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 _______________________________________________ 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/