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