Re: set_sql weird cache issues?

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

On Tue, Jun 28, 2005 at 08:54:13PM +0100, marshyon@xxxxx.xxx wrote:
> 
> 
> in a parent class 'DBI.pm', I am setting sql:
> 
> __PACKAGE__->set_sql("big_join", "select c.name 'customer', p.name 'product', p.notes 'notes' from purchase_order po, product p, customer c where po.product = p.id and po.customer = c.id order by customer");
> 
> in my script I am accessing values of the above
> 
> my @things = MyApp::M::DBI::list->search_big_join();

I wouldn't bother doing a set_sql for something this simple. Install
Class::DBI::Sweet and subclass that instead of Class::DBI - you should then
be able to do (assuming you have classes for the three tables and
::purchase_order has_a ::customer and ::product)

my @things = MyApp::M::DBI::purchase_order->search({ },
               { prefetch => [ 'customer', 'product' ],
                 order_by => 'customer.name' });

and your @things will come back with pre-populated customer and product
objects because Sweet will do the join for you ala ActiveRecord's :include,
so you can just do

$thing->customer->name

etc. etc. without it going back to the database. 

Note: your nearest CPAN mirror may only have 0.02. I managed to break the
tests (mkdir t/var to fix them). 0.03 has my screw-up fixed by the wonderful
andyg and is percolating through CPAN at the moment.

        -- 
             Matt S Trout           Website: http://www.shadowcatsystems.co.uk
  Technical Director        E-mail:  mst (at) shadowcatsystems.co.uk
Shadowcat Systems Ltd.