[Dbix-class] Finding missing rows -- JOIN with multiple conditions

[Bill Moseley]

On Mon, Jun 3, 2013 at 5:04 AM, Peter Rabbitson <rabbit+dbic at rabbit.us>wrot=
e:

> On Sat, Jun 01, 2013 at 04:22:17PM -0700, Bill Moseley wrote:
> > After stumbling around for a while I realize I need some help with
> building
> > a query with DBIC.
>
> That was a rather long email. It isn't immediately clear *which* part
> you are still having a problem with. Can you rephrase the question...?
>

Did I see that more complex join conditions are possible now with DBIC w/o
a virtual view?


This is one of those "find where joined row do not exist" problems that is
often solved with a correlated sub-query.  But, I think I can solve (and
much faster) with just a join.   But I need to have extra join condition
(in *bold* below).


This is essentially the query I'm after where I join with the "track" table
but only if the track.songwriter is associated with the musician (because
there's other musicians that might have track rows).

SELECT
         musician_band.musician, musician.band, album.id as album

    FROM
         musician_band
         LEFT JOIN band ON  musician.band =3D band.id
         LEFT JOIN album ON album.band =3D band.id
         LEFT JOIN track ON track.album =3D album.id* AND  track.songwriter=
 =3D
musician_band.musician*
         LEFT JOIN track_detail ON track_detail.id =3D track.id

    WHERE
         band in ( @bands )
         AND (
             track.id IS NULL  -- NULL thus means there's no track written
by the musician on that album.
             OR track_detail.is_on_album IS FALSE
         )


So, with the normal join it would be something like this:


schema->resultset( 'MusicianBand' )->search(
    {
        'me.band' =3D> { -in =3D> \@band_ids },
        -or =3D> [
            'tracks.id' =3D> undef,
            'track_detail.is_on_album' =3D> 0,
        ],
    },
    {
        select =3D> [qw/ me.musician me.band albums.id /],
        as     =3D> [qw/ musician band album / ],
        join   =3D> {
            bands =3D> {
                albums =3D> {
                    tracks =3D> 'track_detail',
                },
            },
        },
    },
);



-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130603/e3d=
7e03f/attachment.htm