http://www.perlmonks.org?node_id=552944


in reply to DBIx::Class and many-to-many searching

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

=cut
--Brent Dax
There is no sig.

Replies are listed 'Best First'.
Re^2: DBIx::Class and many-to-many searching
by zby (Vicar) on Jun 02, 2006 at 08:32 UTC
    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.