[Dbix-class] Many2MAny "Related" query

[Zbigniew Lukasiak]

On 3/15/07, Guillermo Roditi <groditi at gmail.com> wrote:
> i guess you would either inner self join for every tag to only select items
> that have all those tags or you could do an IN(@tags) and then group by
> product id using a count() and then in the having clause count = scalar
> @tags
>
> SELECT id, name, COUNT(*) as cnt FROM products  INNER JOIN tags
> ON(products.id = tags.product_id) WHERE tags.tag IN( @tags ) GROUP BY
> products.id  HAVING cnt = scalar @tags

I would like to know how efficient that query is - my intuition is
that it would have to scan all the groups to count their members so
this would mean a full scan on the joined table which can be pretty
big.

>
> or
>
> SELECT id, name, COUNT(*) as cnt FROM products
> INNER JOIN tags as tags1 ON(products.id = tags.product_id)
> INNER JOIN tags as tags2 ON(products.id = tags.product_id)
> WHERE tags1.tag ="computer" AND tags2.tag ="widget"
>

I have translated that into DBIC at
http://perlalchemy.blogspot.com/2006/10/tags-and-search-and-dbixclass.html
(using bookmarks instead of products):

I had to build hash with search parameters with the proper key names
(tag, tag_2, tag_3 ...) and values from the @tags array :

my $suffix = '';
my $i = 1;
for my $tag (@tags){
$sqlparams{'tag' . $suffix . '.tag'} = $tag;
$suffix = '_' . ++$i;
}

And then the search:

my $it = $schema->resultset('Bookmark')->search(
\%sqlparams, {
join => [ ('tag') x scalar(@tags), 'usr' ],
order_by => \@order,
page => $page,
rows => $maxrows,
},
);

>From what I understood Chris needs to get not the products, but rather
the other tags related to them - so eventually another additional join
to the tags table is inevitable.

--
Zbyszek

>
> On 3/15/07, Christopher H. Laco <claco at chrislaco.com> wrote:
> >
> > Zbigniew Lukasiak wrote:
> > > First - do you want to have all the tags of all the products that have
> > > *all* of the selected tags or you want to have all the tags of all the
> > > products that have *any* of the selected tags?
> > >
> > > From the pseudocode it seems that you want the latter, but I am still
> > > not sure.
> > >
> > > --
> > > Zbyszek
> >
> >
> > All. Given:
> >
> >    http://.../tags/foo/bar/
> >
> > I want all tags assigned to the products that have foo AND bar assigned
> > to them. So yeah, "in" was a misnomer. :-/
> >
> > -=Chris
> >
> >
> > _______________________________________________
> > 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/
> >
> >
>
>
> _______________________________________________
> 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/
>


-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/