Hello all,
Is it possible to create a relationship which has, along with the join
condition, an additional constraint where the value could somehow be passed
when the search() method is called?
What I would like to achieve (in sql) is this:
(it's a contrived example, but the structure matches my real life project
almost exactly...just with less abstract names)
SELECT movies.name, reviews.text
FROM movies
LEFT JOIN reviews ON movies.id = reviews.movie_id AND reviews.person = ?; 'Sue'
This query would satisfy each of the following cases:
- Get a list of all movies, as well as a review by Sue if it exists
- If a movie does not have a review by Sue then it should still be returned
with reviews.text = NULL.
- If a movie does not have a review by Sue but it has a review by someone else
then it should also be returned with reviews.text = NULL
The closest I can get is the following code, but it only satisfies the first
two cases:
$db->resultset('Movie')->search(
{ 'reviews.person' => [undef, 'Sue'] },
{ prefetch => 'reviews' },
);
which puts the constraint into the WHERE (as normal)...
SELECT movies.name, reviews.text
FROM movies
LEFT JOIN reviews ON movies.id = reviews.movie_id
WHERE reviews.person = NULL
OR reviews.person = ?; 'Sue'
But this will not return any movies which don't have a review by Sue and have a
review by someone else. Which is why the constraint really needs to be in along
with the LEFT JOIN condition.
Any help with this would be greatly appreciated,
Stuart
_______________________________________________
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/dbix-class@lists.scsys.co.uk
