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
_______________________________________________
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/dbix-class@lists.rawmode.org/
