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 :)
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
