[Dbix-class] Filtering with Many-to-Many relationship

[Shawn Marincas]

Alright, it seemed to me that what I wanted to do was straightforward, but
it doesn't seem like I can find any resources on how to do it... so maybe
I'm just going about it the wrong way.

I have a table Products and a table Tags with a many_to_many relationship
setup, the purpose of which would be to easily generate a resultset of
products with the given tag(s).  However, I'm running in to problems when I
want to generate a resultset of product records filtered with multiple
tags.  I was thinking that one of the following would work:

my $tag1 =3D 'A';
my $tag2 =3D 'B';

$c->model('MyApp:Tag')->single({ tag_id =3D> $tag1 })->products->search({ t=
ags
=3D> { tag_id =3D> $tag2 }});

OR

$c->model('MyApp:Tag')->single({ tag_id =3D> $tag1
})->products->search_related('product_tags', { tag_id  =3D> $tag2 });

But apparently the many_to_many relationship accessor only returns a list of
row_objects rather than a resultset itself, so I can't perform resultset
searches on that.  So I tried the following to chain together two searches:

$c->model('MyApp:Product')->search_related('product_tags',{ tag_id =3D> $ta=
g1
})->search_related('product_tags', { tag_id =3D> $tag2 });

I tried the same chaining using search and joins to link to the
ProductTagsLink table, they combined the searches so that it was joining on
the related table searching for tag_id =3D $tag1 AND tag_id =3D $tag2 so th=
at it
wasn't returning anything.  I was looking in to creating a subquery for
this, but my attempts have failed and the documentation mentions that
subqueries are experimental.  I'm thinking if I had an accessor to access
the many_to_many resultset as an actual resultset rather than a list of
row_objects I could probably get it working, but I didn't see any
documentation on actually doing that.  I managed to get the actual concept
working using this code here, but would prefer a straight SQL/DBIX solution
since this doesn't seem very efficient:

my $tag1 =3D 'A';
my $tag2 =3D 'B';
my $tag_rs =3D $c->model('ReaneyDesignDB::Tag')->single({ tag_id =3D> $tag =
});

my @products =3D $tag_rs->products;

$c->stash->{products} =3D [grep { ($_->search_related('product_tags', { tag=
_id
=3D> $tag2 })->single) } @products];

Anyone have experience trying to accomplish what I'm doing here?  Let me
know if I'm missing something here.  Thanks.

- Shawn Marincas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090918/e51=
436cf/attachment.htm