[Dbix-class] Joining for counts of related items.

[Rob Kinyon]

On Fri, Apr 23, 2010 at 18:09, Bill Moseley <moseley at hank.org> wrote:
> Back to my Music database.  In my Cd class I have these methods to return
> counts of tracks of a specific type.
>
> sub foo_track_type_count {
>     return shift->tracks( { track_type => 1 } )->count;  # any different
> then count_related??
> }
> sub bar_track_type_count {
>    return shift->tracks( { track_type => { -in => [2,3] } } )->count;
> }
>
> Now, it turns out that I always need these counts.
> $cd => $schema->resultset( 'Cd' )->find( $id );  # one database select
> my $foos = $cd->foo_track_count;  # second select
> my $bars = $cd->bar_track_count;  # third.
>
> I know I can build a resultset that includes a total count (as seen in the
> Cookbook), but is there a way to include counts that are limited to those
> specific "track_types"?
> IIRC from past discussions this had to be done with a custom FROM which then
> broke some other functionality.

The question you should always ask yourself is "How would I do this in
raw SQL?". The answer is "With a subquery in my SELECT clause." The
raw SQL would look something like:

SELECT me.id, me.name,
    ( SELECT COUNT(*) FROM tracks WHERE tracks.cd_id = me.id AND
track_type IN ( 1 ) ) AS foo_track_count,
    ( SELECT COUNT(*) FROM tracks WHERE tracks.cd_id = me.id AND
track_type IN ( 2, 3 ) ) AS bar_track_count,
  FROM cds AS me
 WHERE me.id = ?

Given that, it should seem pretty obvious that proper solution should
have correlated subqueries in a +select. The rest is left as an
exercise to the reader.

Rob