[Dbix-class] order_by question

[africroissance]

On Thu, Jan 20, 2011 at 1:12 PM, Peter Rabbitson
<rabbit+dbic at rabbit.us<rabbit%2Bdbic at rabbit.us>
> wrote:

> 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<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 =3D $schema->resultset('Cd')->search(
>>    {},
>>    {
>>      order_by =3D> \'tracks.count DESC',
>>      join     =3D> [qw/ tracks /],
>>    }
>>  );
>>  while (my $cd =3D $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 "SELE=
CT
>> me.cdid, me.artist, me.title FROM cd me LEFT JOIN track tracks ON
>> tracks.cd <http://tracks.cd> =3D 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 DES=
C'
> 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', wh=
at
> would you do? :)
>
> Hence your necessary query is:
>
> search({}, {
>  order_by =3D> { -desc =3D> \'count(tracks.id)' },
>  join =3D> 'tracks',
>  distinct =3D> 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 =3D> { -desc =3D> \'count(tracks.trackid <http://tracks.id/>)' },
 join =3D> 'tracks',
 distinct =3D> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110120/96c=
893db/attachment.htm