[Dbix-class] Many2MAny "Related" query

Christopher H. Laco claco at chrislaco.com
Thu Mar 15 17:11:12 GMT 2007


Guillermo Roditi wrote:
> i guess you would either inner self join for every tag to only select ite=
ms
> 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"
> =


And just I mention it, doing the SQL isn't my problem... it's now to
manke DBIC/SQL::Abstract happy that is the part that's screwing me up....


As for the SQL above, that's Products w/Tags... I want related Tags of
Tags (og products)...

In fact, I already have a $products->get_by_tags(@tags) that does
exactly that... an aliased join for each tag, then distinct product.*

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: OpenPGP digital signature
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070315/1f=
7ea1e6/signature.pgp


More information about the Dbix-class mailing list