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

[Bill Moseley]

Peter, we discussed multiple join conditions in 2009 (
http://dbix-class.35028.n2.nabble.com/Left-join-with-an-extra-condition-td3=
880896.html
).

Is that still the case that I need to use a virtual view?



On Mon, Jun 3, 2013 at 10:06 AM, Bill Moseley <moseley at hank.org> wrote:

>
>
> On Mon, Jun 3, 2013 at 5:04 AM, Peter Rabbitson <rabbit+dbic at rabbit.us>wr=
ote:
>
>> 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.songwrit=
er
> =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




-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130606/131=
9da7c/attachment.htm