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
