Peter, we discussed multiple join conditions in 2009 ( http://dbix-class.35028.n2.nabble.com/Left-join-with-an-extra-condition-td3880896.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 <mose...@hank.org> wrote: > > > On Mon, Jun 3, 2013 at 5:04 AM, Peter Rabbitson <rabbit+d...@rabbit.us>wrote: > >> 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 = band.id > LEFT JOIN album ON album.band = band.id > LEFT JOIN track ON track.album = album.id* AND track.songwriter > = musician_band.musician* > LEFT JOIN track_detail ON track_detail.id = 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' => { -in => \@band_ids }, > -or => [ > 'tracks.id' => undef, > 'track_detail.is_on_album' => 0, > ], > }, > { > select => [qw/ me.musician me.band albums.id /], > as => [qw/ musician band album / ], > join => { > bands => { > albums => { > tracks => 'track_detail', > }, > }, > }, > }, > ); > > > > -- > Bill Moseley > mose...@hank.org -- Bill Moseley mose...@hank.org
_______________________________________________ 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