[Dbix-class] relationship with additional constraints?

[Stuart Dodds]

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