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 = 'A';
my $tag2 = 'B';
$c->model('MyApp:Tag')->single({ tag_id => $tag1 })->products->search({ tags
=> { tag_id => $tag2 }});
OR
$c->model('MyApp:Tag')->single({ tag_id => $tag1
})->products->search_related('product_tags', { tag_id => $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 => $tag1
})->search_related('product_tags', { tag_id => $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 = $tag1 AND tag_id = $tag2 so that 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 = 'A';
my $tag2 = 'B';
my $tag_rs = $c->model('ReaneyDesignDB::Tag')->single({ tag_id => $tag });
my @products = $tag_rs->products;
$c->stash->{products} = [grep { ($_->search_related('product_tags', { tag_id
=> $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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
