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

[Peter Rabbitson]

On 07/29/2014 03:50 PM, 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...
>

A condition within DBIC invariable represents a single JOIN. Using it to 
represent a many-to-many is conceptually impossible, as it makes a lot 
of foundation assumptions fall apart.

Your next message detailing the custom method is the correct way of 
doing this. In fact if you look in the accessor generator for m2m, you 
will realize it is doing exactly the same for you: 
https://metacpan.org/source/RIBASUSHI/DBIx-Class-0.082700_05/lib/DBIx/Class/Relationship/ManyToMany.pm#L63

Let me know if this answered your question, or there is stuff that I missed.

Cheers