[Dbix-class] JOIN question

Mon Nov 27 03:17:27 GMT 2006

Bernhard Graf wrote:
> Hi,
> 
> I'm unable to define a certain join condition with DBIC.
> Maybe someone can have a look and help me out, perhaps also Matt,
> because it seems there is a small bug with the "from"-attribute
> (see at the end).
> 
> In reality the whole beast is much more complex, but it try to reduce as
> much as possible to the relevant parts:
> 
> I have two tables that can handle multipart emails:
> 1. `email´ which contains header data
> 2. `chunk´ which contains body data
> 
> "chunk" has a foreign key to `email´ - or `chunk´ belongs_to `email´ in
> DBIC nomenclature.
> 
> Here are the tables (MySQL):
> 
> CREATE TABLE `email` (
>   `id` int(10) unsigned NOT NULL auto_increment,
>   `title` varchar(255) default NULL,
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> CREATE TABLE `chunk` (
>   `id` int(10) unsigned NOT NULL auto_increment,
>   `email` int(10) unsigned default NULL,
>   `data` mediumtext,
>   `mimetype` varchar(63) NOT NULL default 'text/plain',
>   PRIMARY KEY (`id`),
>   KEY `email` (`email`),
>   FOREIGN KEY (`email`) REFERENCES `email` (`id`) ON DELETE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> Now define three emails, first plain text, second only html and third
> a multipart with a text and an email chunk.
> 
> INSERT INTO email VALUES (1, 'plain text');
> INSERT INTO email VALUES (2, 'plain html');
> INSERT INTO email VALUES (3, 'text+html');
> INSERT INTO chunk VALUES (1,1,'foo','text/plain');
> INSERT INTO chunk VALUES (2,2,'<html><body>foo</body></html>','text/html');
> INSERT INTO chunk VALUES (3,3,'bar','text/plain');
> INSERT INTO chunk VALUES (4,3,'<html><body>bar</body></html>','text/html');
> 
> 
> The challenge is to find those emails with a format that is either
> - only text
> - only html
> - multipart text+html
> 
> To do this in one query table `email´ must be joined twice with table
> `chunk´ to express statements as "email has text chunk but no html chunk"
> or "email has text chunk and html chunk".
> 
> Here is the SQL code that does the jobs:
> 
> 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?