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");
>
_______________________________________________
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
