[Dbix-class] relationship custom join condition using relation in foreign rel

[Peter Mottram]

Partly answering my own question here though I'm still interested if
this can be achieved using a custom join condition...

On 29/07/14 15:50, Peter Mottram wrote:
> I'm using a Message class which has various m2m rels + link tables to
> store things such as ProductReview and OrderComment.
>
> I'd like to create an accessor in User which returns only the Message
> rows that are ProductReviews as opposed to OrderComments/whatever. The
> SQL I'm hoping to build is along these lines:
>
> SELECT messages.*
>   FROM users me
>     JOIN messages messages ON me.users_id = messages.author
>     JOIN product_reviews product_reviews ON messages.messages_id =
> product_reviews.messages_id
>   WHERE me.users_id = ?
>
> I was hoping to use a custom join condition on a has_many relationship
> but am getting nowhere. Maybe I should just add a simple sub to User
> instead that uses the existing messages rel + some extra join
> conditions? Suggestions welcome - simplified classes follow...
>
> TIA
> PeteM
>
> ## User class
>
> package User;
>
> add_columns( qw/users_id username/ );
>
> has_many( messages => "Message", { 'foreign.author' => 'self.users_id' } );
>
> has_many( reviews => "Message", sub { ** what goes here? ** });
Simple sub instead of rel:

sub reviews {
    my $self = shift;
    return $self->messages->search({},
        { join =>'product_review' }
    );
}

Also add add_to_reviews sub (obvious so not shown here).
>
> ## Message class used for many things including product reviews
>
> package Message;
>
> add_columns( qw/messages_id author/ );
>
> might_have( product_review => 'ProductReview', 'messages_id' );

Change that rel to prevent left join:

might_have( product_review => 'ProductReview', 'messages_id', { join_type => '' } );

> belongs_to( author => 'User', { 'foreign.users_id' => 'self.author' }, {
> join_type => 'left' } );
>
> ## ProductReview class isa (kind of) Message
>
> package ProductReview; # link table Product - Message
>
> add_columns( qw/messages_id sku/ );
>
> belongs_to( message => 'Message', "messages_id" );
> belongs_to( product => 'Product', "sku" );
>
> ## Product class (only shown for clarity)
>
> package Product;
>
> add_columns( qw/sku name/ );
>
> has_many( product_reviews => "ProductReview", "sku" );
>
> many_to_many("reviews", "product_reviews", "message");
>