BrentDax has asked for the wisdom of the Perl Monks concerning the following question:

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.


--Brent Dax
There is no sig.

Replies are listed 'Best First'.
Re: DBIx::Class and many-to-many searching
by zby (Vicar) on May 30, 2006 at 12:25 UTC
    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 = articlelinks.article AND = $with[0] AND = articlelinks_1.article AND = $with[1] A +ND ...
    To produce this with DBIC you need something like:
    my %params; $params{''} = $with[0]; for my $i ( 2 .. (scalar @with) - 1 ){ $params{"articlelinks_$"} = $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.

Re: DBIx::Class and many-to-many searching
by castaway (Parson) on May 30, 2006 at 09:36 UTC
    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.



    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

      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 }, }, );

      - tye        

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

      --Brent Dax
      There is no sig.

Re: DBIx::Class and many-to-many searching
by BrentDax (Hermit) on May 31, 2006 at 22:09 UTC

    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);

    --Brent Dax
    There is no sig.

      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.