AE
Ash Elixir•3w ago
Vonagam

Question about somewhat unusual join relationship

Imagine we have User, Opinion and Event. Opinion is a join resource between User and Event. The tricky part that I want to support is for Opinion to have nullable user_id, where an opinion without a user is a default opinion. And when we query opinions/events for a user we want to include default opinions for events which the user has not specified/created their own opinion. Sounds like something for sort based on user_id and distinct based on event_id. And this combo does work if we query things for a single user but if we load things for multiple users there appears a problem - read action is used to read all users opinions together. Because of that it results in removal of a default opinion if any one of requested users has its own opinion (distinct gets applied to whole query not per user). Which means that if one user has its own opinion about an event then other users, who do not have their own opinion, lose their join link (default one). I'm interested if it is possible to make this work without using manual relationship.
Solution:
I think many to many relationships essentially only support join resources that are distinct on the two columns in question currently, we should probably be showing an error about it TBH
Jump to solution
3 Replies
Solution
ZachDaniel
ZachDaniel•3w ago
I think many to many relationships essentially only support join resources that are distinct on the two columns in question currently, we should probably be showing an error about it TBH
ZachDaniel
ZachDaniel•3w ago
I believe you could do something like: 
has_many :events, Event do
  no_attributes? true
  filter expr(parent(opinions.event_id) == id))
end
has_many :events, Event do
  no_attributes? true
  filter expr(parent(opinions.event_id) == id))
end
Vonagam
VonagamOP•3w ago
has_many :events like that will work, yes, but only for events, but I also wanted to get user opinions (there are attributes there) - for load: [users: [opinions: :event]] to work. For record, the main problem here is distinct as it does not take into account __lateral_join_source__ and I think for all other query operations like filter, select, load, sort and so on it does not affect the query if the read happens for everyone at once or for each user, but for distinct specifically it does. So, as I understand, other than through manual relationship it cannot be done. So will mark it as done.

Did you find this page helpful?