[Dbix-class] order_by question

[Peter Rabbitson]

africroissance wrote:
> 
> 
> On Wed, Jan 19, 2011 at 9:26 PM, Rob Kinyon <rob.kinyon at gmail.com 
> <mailto:rob.kinyon at 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 
> 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,
});

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.

[1] http://search.cpan.org/~abraxxa/DBIx-Class-0.08127/lib/DBIx/Class/Manual/Cookbook.pod