Hi Peter, Thanks for helping.
On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson <[email protected]<rabbit%[email protected]> > wrote: > 2) You can take a resultset from your original source, and *temporarily* > (i.e. > just for the life of this particular resultset object) substitute the > 'from' > attribute with a scalarref of the SQL you want to execute. The 'from' > attribute represents everything between the FROM and WHERE keywords. There > used to be documentation of this attribute, but it was mostly factually > incorrect and thus was undocumented. However you might see how the > scalarref > part worked here (the rest is now mostly irrelevant, as the format has > changed, > A LOT): http://dev.catalyst.perl.org/svnweb/bast/revision/?rev=7711 > This seems to be the best solution for this specific problem. I have the typical "music" database, with an extra table that tracks "gigs" and the venue where those gigs are: select * from artist; id | name | label ----+--------------+------- 1 | artist one | 1 2 | artist two | 1 3 | artist three | 2 4 | artist four | 2 select * from gig order by artist; id | artist | venue ----+--------+--------- 1 | 1 | outside 2 | 1 | outside 3 | 1 | outside 4 | 1 | inside 5 | 2 | outside 6 | 2 | outside 7 | 2 | inside 8 | 2 | inside 9 | 3 | studio And I want a count of how many "outside" gigs each artist plays: SELECT a.id, a.name, count(g.id) as gig_count FROM artist a LEFT JOIN gig g on g.artist = a.id AND g.venue = 'outside' GROUP BY 1,2 ORDER BY a.id id | name | gig_count ----+--------------+----------- 1 | artist one | 3 2 | artist two | 2 3 | artist three | 0 4 | artist four | 0 So, the custom ResultSet::Artist method is: sub outside_gigs { my $rs = shift; return $rs->search( undef, { 'select' => [ qw/ me.id me.name /, { count => 'gigs.id', -as => 'gig_count', }, ], from => \q{ artist me LEFT JOIN gig gigs ON me.id = gigs.artist AND gigs.venue = 'outside' }, as => [qw/ id name gig_count / ], group_by => '1,2', }, ); } What's good about this is the custom method is abstracted out into the Artist's resultset class. That is, the implementation of outside_gigs() is hidden. Plus, can make use of the ORM's features to limit rows and fetch a given page: my @artists = $schema->resultset('Artist')->search( undef, { rows => 2, page => 2, order_by => 'id', }, )->outside_gigs->all; With the limitation that can't use a "join" or "prefetch". Plus, not sure that will support a more complex query (e.g. where there's bind parameters in a CASE in the SELECT list). -- Bill Moseley [email protected]
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
