[Dbix-class] JOIN question

[Stephan Szabo]

On Wed, 29 Nov 2006, Matt S Trout wrote:

> Bernhard Graf wrote:
> > Matt S Trout wrote:
> >
> >> Bernhard Graf wrote:
> >>> Matt S Trout wrote:
> >>>>> SELECT me.title
> >>>>> FROM email me
> >>>>> LEFT JOIN chunk chunks ON chunks.email=me.id
> >>>>> LEFT JOIN chunk chunks_2 ON chunks_2.email=me.id AND
> >>>>> chunks.id!=chunks_2.id
> >>>> why not just join => [ qw/chunks chunks/ ] and add { 'chunks.id'
> >>>> => \"!= chunks_2.id" } to the where?
> >>> Thanks for the answer, but this must be in the JOIN ... (ON ...)
> >>> part to work correctly.
> >> Why?
> >
> > Err, because that's what left-joins are for, I think: Get a row with
> > values NULL'ed where else with a normal join wouldn't get anything.
> >
> > If you have a mysql db at hand you could simply c&p the example from the
> > first mail into the test db and try it yourself. I would be very
> > interested to see a way how to retrieve for example all text-only
> > emails without "chunks.id!=chunks_2.id" being in the LEFT-JOIN-ON
> > condition.
>
> { -or => [ { 'chunks.id => undef' }, { 'chunks_2.id' => undef }, { 'chunks.id'
> => \'!= chunks2.id' } ] }
>
> i.e.
>
> WHERE (chunks.id IS NULL OR chunks_2.id IS NULL OR chunks.id != chunks2.id)
>
> It's not amazingly elegant, which is why the JOIN ON syntax is generally
> preferred, but by and large you can move stuff in and ON clause into the
> WHERE clause or vice versa just fine - a LEFT JOIN is pretty much just a
> JOIN against all values of that table *plus* a row consisting entirely
> of NULLs.

Are you sure? My interpretation of the spec seems to say that you get a
JOIN plus null extended rows of the rows on the left with no matches on
the right.

If we have an email row with id 1 and a chunk row with id 10 and email 1
and no other rows in either table, my understanding is that
> >>>>> FROM email me
> >>>>> LEFT JOIN chunk chunks ON chunks.email=me.id
> >>>>> LEFT JOIN chunk chunks_2 ON chunks_2.email=me.id
gives you a result like
 (me.id=1, chunks.email=1, chunks.id = 10, chunks_2.email=1,
chunks_2.id=10)
and then a WHERE clause like your proposed would filter the row out, while
the intended behavior is that you'd get a row like (me.id=1,
chunks.email=1, chunks.id = 10, chunks_2.email=null, chunks_2.email=null).