[Dbix-class] relationship with additional constraints?

[Andrew Beverley]

On Thu, 17 May 2018 11:12:45 +0200 dodds at united-domains.de wrote:
> 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?

This should answer your question:

https://blog.afoolishmanifesto.com/posts/dbix-class-parameterized-relationships/

Interestingly the post has been updated since I last looked at it
saying that approach no longer works. I'm not sure why, as it still
seems to work for me. Frew - can you elaborate?

>  - 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 other method you might want to consider is using a correlated
sub-query. It's a little cleaner from a DBIx::Class viewpoint, but I
think you'd need to pull out each review column separately, so would
depend what you want to retrieve. Another excellent blog from Frew:

https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-resultset-correlaterelationship/

It allows you to do something like this (untested):

  '+columns' => {
     sue_review => $self->schema->resultset('Review')
       ->correlate('movies')
       ->search({ person => 'sue' })
       ->get_column('review_text')
       ->as_query,
  },

Andy