[Dbix-class] Many2MAny "Related" query

[Guillermo Roditi]

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 =3D scalar
@tags

SELECT id, name, COUNT(*) as cnt FROM products  INNER JOIN tags ON(
products.id =3D tags.product_id) WHERE tags.tag IN( @tags ) GROUP BY
products.id  HAVING cnt =3D scalar @tags

or

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


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. :-/
>
> -=3DChris
>
>
> _______________________________________________
> 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/
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070315/542=
3dd45/attachment.htm