Re: set_sql weird cache issues?

[prev] [thread] [next] [Date index for 2005/07/01]

On Fri, 2005-07-01 at 16:46 +0100, J Brookes wrote:
> package MyApp::M::DBI;
> use strict;
> use base 'Class::DBI';
> __PACKAGE__->columns(All   => qw/pid cid product customer notes addr1/);
> __PACKAGE__->set_db('esp', 'dbi:mysql:esp', 'user', 'pass');
> __PACKAGE__->set_sql("big_join", 
>                      qq{select c.id 'cid', p.id 'pid',
> 		     c.name 'customer', c.addr1, 
> 		     p.name 'product', p.notes 'notes' 
> 		     from 
> 		     purchase_order_line po, product p, 
> 		     customer c where po.product = p.id 
> 		     and po.customer = c.id order by customer}
> );

Class::DBI isn't really meant to work this way.  The above tells it that
you have a table with those columns, and that the primary key is "pid",
which is why you're getting incorrect results.  If you fix the primary
key you may get something usable, but you may hit other problems by
trying to use Class::DBI with something that is not a table.  You can
try Tatsuhiko's Class::DBI::View, but I think it may be out of date.

Class::DBI isn't very useful for reporting.  It can work, if what you
want to end up with is a list of rows from one table.  Otherwise, it
will be kludgey.  In this case, I might put that SQL query into a report
class that inherits from Ima::DBI but not Class::DBI and then access the
statement handle directly with sql_big_join and just loop through it
normally.  Class::DBI is not giving you any benefit by providing object
accessors around these (immutable) rows.

- Perrin