[Dbix-class] JOIN question

Sun Nov 26 22:31:32 GMT 2006

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
...
- Only text:
... WHERE chunks.mimetype='text/plain' AND chunks_2.mimetype IS NULL;

- Only html:
... WHERE chunks.mimetype='text/html' AND chunks_2.mimetype IS NULL;

- Multipart:
... WHERE chunks.mimetype='text/plain' AND chunks_2.mimetype='text/html';

Now here's starts the problem:
How do I get DBIx::Class to put this "chunks.id!=chunks_2.id" into the 
left join of my search statement?

I've already arrived at
http://search.cpan.org/~bricas/DBIx-Class-0.07003/lib/DBIx/Class/ResultSet.pm#from ,
read the warning of course, then played around a little with it, finally
ended with this "from" attribute:

  $attribute->{from} = [
    {me => 'email', -join_type => 'left'},
    [
      {chunks_2 => 'chunk'},
      {'chunks_2.email' => 'me.id', 'chunks.id' => {'!=', 'chunks_2.id'}}
    ]
  ];

Besides this is really ugly, it fails, because
  'chunks.id' => {'!=', 'chunks_2.id'}
is translated into:
  chunks.id = HASH(0x38c3460)

I would be very happy if anybody has the time to check this out and has
some advice for this problem.

Have a nice week.
-- 
Bernhard Graf