[Dbix-class] Multiple self-selects against a table

[Michael Henson]

Hello,

I am working with code that dynamically generates a query like:

SELECT DISTINCT aa.product_id AS id
FROM product_variant_options aa,
     product_variant_option ab,
     product_variant_options ac
WHERE
 (aa.option_set_id = ? AND aa.option_id = ? AND aa.variant_of = ?)
 AND (ab.option_set_id = ? AND ab.option_id = ? AND ab.variant_of = ?)
 AND (ac.option_set_id = ? AND ac.option_id = ? AND ac.variant_of = ?)
 AND aa.product_id = ab.product_id
 AND aa.product_id = ac.product_id

The dynamic part is that the number of (option_set_id, option_id) pairs
we need to use as select criteria is unknown - at least one pair, but
limited only by the highest number of options actually associated with a
product in the db.

Is there a clean DBIx-ish way of doing this, or is building the query
string manually the only way at the moment?

Thanks,
Michael Henson
mhenson at silverthorneinc.com