DBIx::Class and many-to-many searching

[BrentDax]

I'm writing a Catalyst app using DBIx::Class accessing MySQL for my model. Two of my tables have a many-to-many relationship, and I'm having trouble constructing the right query for the problem at hand.

Okay, so I have the classic many-to-many relationship: an articles table, a tags table, and a links table with article and tag IDs. I'm using DBIx::Class to access these tables, and I have the relationships set up the way you would expect:

schema::articles->has_many(taglinks => 'schema::links', 'article');
schema::links->belongs_to(article => 'schema::articles');
schema::links->belongs_to(tag => 'schema::tags');
schema::tags->has_many(articlelinks => 'links', 'tag');
schema::articles->many_to_many(tags => taglinks => 'tag');
schema::tags->many_to_many(articles => articlelinks => 'article');

Now, at one point in my code I have two arrays with rows from the tags table. I want to find the articles with all of the tag rows in @with and none of the tag rows in @without. The articles can have tags that aren't in either array. (Oh, and to complicate things further, I may want to exclude articles which don't have certain values in columns of the articles table itself, but I'll burn that bridge when I get to it.) How do I do this?

My searching turned up Many-to-many relationships in databases : SOLVED, but I'm not sure how to apply this to DBIx::Class, or if it's even the right approach for this module. Any help would be appreciated.

Thanks,

[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.

[castaway]

Hi, If you already have an array of tag row objects, then you want to do something like this:

@with = map { $_->id } @with;
@without = map { $_->id } @without;

my @articles = $schema->resultset('articles')->search({}, 
 { join => { 'articlelinks' => 'tag' },
   tag.tag => { '-not_in' => \@without, '-in' => \@with } });

This is basically an SQL::Abstract construct, look there for details.

(Untested!)

C.

Update: Fixed code.. maybe

PS: This will return all articles with any of the tags in @with, there's a discussion going on in #dbix-class as to whether you wanted any, or all of them..

Please give an example of the SQL you'd like to produce, for further help

[tye]

tag.tag is likely not what you want:

$_= $_->id() for @with, @without;
my @articles= $schema->r­esultset('­articles')­->search(
    {­}, 
    {   join => { 'articleli­nks' => 'tag' },
        'tag.tag' => { '-not_in' => \@without, '-in' => \@with },
    },
);

[BrentDax]

For the record, I'm looking for articles with all of the tags in @with and none of the tags in @without.

[BrentDax]

I've got a partial solution—it does what I want (I think), but takes several queries to do it. It's based on the node I linked above. Here's what I have:

    my $attrs   = {
        order_by => "$order DESC",
        page => $page,
        rows => $rows,
        columns  => [ qw(title title_norm description created modified
+ status 
                         rating owner.nickname owner.nick_norm ) ],
        join => [ qw(owner) ],
    };
    
    my $articles = $c->model('DBIC::Articles');
    
    # Build the query.
    if($owner) {
        $articles = $articles->search({ owner => $owner });
    }
    if(@with) {
        $articles = $articles->search_related('taglinks', 
            {
                tag => { -in => [ @with ] }
            },
            {
                select   => [ 'article', { count => '*' } ],
                group_by => [ 'article' ],
                having   => [ 'COUNT( * )' => scalar(@with) ]
            }
        )->search_related('article');
    }
    if(@without) {
        my $excluded = $articles->search_related('taglinks',
            {
                tag => { -in => [ @without ] },
            },
            {
                columns  => [ 'article' ],
                group_by => [ 'article' ]
            }
        );
        if($excluded) {
            my @excluded_ids = map { $_->id } $excluded->all;
            $articles = $articles->search(
                { id => { -not_in => \@excluded_ids } }
            );
        }
    }
    $articles = $articles->search(undef, $attrs);

[zby]

I would like to hear what a DB expert would say about this. My intuition is that my query, even if it might use quite a bit of joins, it still does everything using indexes - while your 'HAVING' clause needs to count things and than filter and for big data set shall be inefficient. But I am no expert here.