[Dbix-class] Left join with an extra condition

[Bill Moseley]

Hi Peter,

Thanks for helping.

On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson
<rabbit+dbic at rabbit.us<rabbit%2Bdbic at rabbit.us>
> 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=3D7711
>

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 =3D a.id AND g.venue =3D '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 =3D shift;

    return $rs->search( undef,
        {
            'select' =3D> [
                qw/ me.id me.name /,
                {
                    count =3D> 'gigs.id',
                    -as   =3D> 'gig_count',
                },
            ],
            from =3D> \q{
                artist me LEFT JOIN gig gigs
                    ON me.id =3D gigs.artist
                    AND gigs.venue =3D 'outside'
            },

            as =3D> [qw/ id name gig_count / ],
            group_by =3D> '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 =3D $schema->resultset('Artist')->search(
    undef,
    {
        rows =3D> 2,
        page =3D> 2,
        order_by =3D> '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
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20091025/c4e=
4fba8/attachment.htm