On 3/15/07, Guillermo Roditi <[EMAIL PROTECTED]> 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 => [EMAIL PROTECTED],
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 <[EMAIL PROTECTED]> 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/[email protected]/
>
>
_______________________________________________
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/[email protected]/
--
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
_______________________________________________
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/[email protected]/
