[Dbix-class] JOIN question

[Matt S Trout]

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.

This is why I hate it when people tell me something "must be" different to my 
suggestions without justification - if you don't explain *why* you think that, 
I can't tell you the bit you're missing :)