[Dbix-class] JOIN question

[Bernhard Graf]

Jess Robinson wrote:

> I'm attempting to follow this, and I'm still not convinced.. Can you
> toss out an example with actual data, show what rows your query
> produces, and what Matt's gets? I also think its possible without
> better JOIN conds..

See the example in the first email in this thread.
You can c&p them in a MySQL-db, for example the test-db, that is there by default on first install.

The first email (email.id=1) has only on text part.
Second email (email.id=2) is HTML-only.
Third (email.id=3) is multipart, Text+HTML.

Now my query to find all emails that have only a text part

mysql> SELECT me.id,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
WHERE chunks.mimetype='text/plain'
    AND chunks_2.mimetype IS NULL:
+----+------------+
| id | title      |
+----+------------+
|  1 | plain text |
+----+------------+

Matt proposes
WHERE (chunks.id IS NULL OR chunks_2.id IS NULL OR chunks.id != chunks_2.id)

I suppose to find the text-only email, he would suggest:
mysql> SELECT me.id,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
WHERE (chunks.id IS NULL
       OR chunks_2.id IS NULL
       OR chunks.id != chunks_2.id)
    AND chunks.mimetype='text/plain';
+----+-----------+
| id | title     |
+----+-----------+
|  3 | text+html |
+----+-----------+

This can't work, because "WHERE chunks.id != chunks_2.id" removes all
lines that find emails that are only text or html; both "... IS NULL"
statements are always false.

To give a better understanding you can output all combinations:

mysql> SELECT me.id,me.title,
    chunks.id,chunks.mimetype,
    chunks_2.id,chunks_2.mimetype
FROM email me
LEFT JOIN chunk chunks ON chunks.email=me.id
LEFT JOIN chunk chunks_2 ON chunks_2.email=me.id;
+----+------------+------+------------+------+------------+
| id | title      | id   | mimetype   | id   | mimetype   |
+----+------------+------+------------+------+------------+
|  1 | plain text |    1 | text/plain |    1 | text/plain |
|  2 | plain html |    2 | text/html  |    2 | text/html  |
|  3 | text+html  |    3 | text/plain |    3 | text/plain |
|  3 | text+html  |    3 | text/plain |    4 | text/html  |
|  3 | text+html  |    4 | text/html  |    3 | text/plain |
|  3 | text+html  |    4 | text/html  |    4 | text/html  |
+----+------------+------+------------+------+------------+

See what remains with "WHERE chunks.id != chunks_2.id":

mysql> SELECT me.id,me.title,
    chunks.id,chunks.mimetype,
    chunks_2.id,chunks_2.mimetype
FROM email me
LEFT JOIN chunk chunks ON chunks.email=me.id
LEFT JOIN chunk chunks_2 ON chunks_2.email=me.id
WHERE chunks.id!=chunks_2.id;
+----+-----------+------+------------+------+------------+
| id | title     | id   | mimetype   | id   | mimetype   |
+----+-----------+------+------------+------+------------+
|  3 | text+html |    4 | text/html  |    3 | text/plain |
|  3 | text+html |    3 | text/plain |    4 | text/html  |
+----+-----------+------+------------+------+------------+

Now the same with chunks.id != chunks_2.id in LEFT JOIN:

mysql> SELECT me.id,me.title,
    chunks.id,chunks.mimetype,
    chunks_2.id,chunks_2.mimetype
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;
+----+------------+------+------------+------+------------+
| id | title      | id   | mimetype   | id   | mimetype   |
+----+------------+------+------------+------+------------+
|  1 | plain text |    1 | text/plain | NULL | NULL       |
|  2 | plain html |    2 | text/html  | NULL | NULL       |
|  3 | text+html  |    3 | text/plain |    4 | text/html  |
|  3 | text+html  |    4 | text/html  |    3 | text/plain |
+----+------------+------+------------+------+------------+

Clear now?
-- 
Bernhard Graf