Re: DBIx::Class and many-to-many searching

[zby]

Update: Added code to generate the params.

Hmm - that might be pretty hard (I assume that you want an article that is linked to all of the tags in @with instead of the castaway's interpretation). Here are some loose thoughts.

Lets take a bit simplified case where the @with contains the articlelinks ids and not think about the @without part at all. Than you need a query like:

SELECT * FROM 
article, articlelinks, articlelinks articlelinks_2, articlelinks artic
+lelinks_3 ...
WHERE
article.id = articlelinks.article AND articlelinks.id = $with[0] AND
article.id = articlelinks_1.article AND articlelinks_1.id = $with[1] A
+ND
...

To produce this with DBIC you need something like:

my %params;
$params{'articlelinks.id'} = $with[0];
for my $i ( 2 .. (scalar @with) - 1 ){
    $params{"articlelinks_$i.id"} = $with[$i - 1];
}
my @articles = $schema->resultset('articles')->search(
    \%params,
    {
        join => [ 'articlelinks' x scalar( @with ) ]
    }
);

The @without part is a bit simpler, thanks to De Morgan lows. But how to compose those two parts? In raw SQL I would use 'EXCEPT' but in DBIC? And how to go from the list of 'articlelinks' ids to tags?

More questions than answers here - but I've heard mst was looking for hard cases so perhaps some day he shall simplify that? I'm interested - as I use a similar structure in my bookmarking app.