On Thu, Jan 20, 2011 at 1:12 PM, Peter Rabbitson <rabbit+d...@rabbit.us<rabbit%2bd...@rabbit.us> > wrote:
> africroissance wrote: > >> >> >> On Wed, Jan 19, 2011 at 9:26 PM, Rob Kinyon <rob.kin...@gmail.com<mailto: >> rob.kin...@gmail.com>> wrote: >> >> What happened? Why do you think that doesn't work? Please provide what >> should have happened, what did happen, and why you're not happy with >> the outcome. >> >> >> >> Thanks Rob, >> >> Working with the example in >> http://search.cpan.org/~abraxxa/DBIx-Class-0.08127/lib/DBIx/Class/Manual/Example.pod<http://search.cpan.org/%7Eabraxxa/DBIx-Class-0.08127/lib/DBIx/Class/Manual/Example.pod>I >> wrote a method: >> >> sub get_cds_ordered_by_number_of_tracks { >> print "CDS ordered by the number of tracks they have\n"; >> my $rs = $schema->resultset('Cd')->search( >> {}, >> { >> order_by => \'tracks.count DESC', >> join => [qw/ tracks /], >> } >> ); >> while (my $cd = $rs->next) { >> print $cd->title . "\n"; >> } >> print "\n"; } >> >> expecting that I would get: >> >> CDS ordered by the number of tracks they have >> Bad >> Thriller >> The Marshall Mathers LP >> >> ie. A list of CDs ordered by the number of tracks they each have (Bad; 3, >> Thriller; 2, The Marshall Mathers LP; 2) >> >> However what I get is this: >> CDS ordered by the number of tracks they have >> DBIx::Class::ResultSet::next(): DBI Exception: DBD::SQLite::db >> prepare_cached failed: no such column: tracks.count [for Statement "SELECT >> me.cdid, me.artist, me.title FROM cd me LEFT JOIN track tracks ON >> tracks.cd <http://tracks.cd> = me.cdid ORDER BY tracks.count DESC"] at >> testdb.pl <http://testdb.pl> line 135 >> >> > I gave you the new documentation because there is no mention of \'foo DESC' > anywhere in it. But anyway what you asked of dbic is: > join CDS to TRACKS, order by the *column* tracks.count, and select the CD > related data. > > x.y is the standard notation for table.column, '.count' is not interpreted > as some magic method (if it did and you *had* a column called 'count', what > would you do? :) > > Hence your necessary query is: > > search({}, { > order_by => { -desc => \'count(tracks.id)' }, > join => 'tracks', > distinct => 1, > }); > > This makes a lot of sense to me and it works like a charm*!! Thanks. * I had to make a very minor change to search({}, { order_by => { -desc => \'count(tracks.trackid <http://tracks.id/>)' }, join => 'tracks', distinct => 1, }); for the CPAN example to work. > distinct gives you the group-over-what-we-select, which makes the count > work > per cd (aggregates obey group_by and split into "zones"). > > Please contribute an example of this as a patch to the cookbook[1] so the > next fellow beginner will not have to crack his skull open. > > Will do that. And thank you again. > [1] > http://search.cpan.org/~abraxxa/DBIx-Class-0.08127/lib/DBIx/Class/Manual/Cookbook.pod<http://search.cpan.org/%7Eabraxxa/DBIx-Class-0.08127/lib/DBIx/Class/Manual/Cookbook.pod> > > > _______________________________________________ > 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/dbix-class@lists.scsys.co.uk >
_______________________________________________ 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/dbix-class@lists.scsys.co.uk
