How to make a joint fetch?-CodePudding

I am building a Rails 5.2 app. In this app I got three objects:

User

Id
Firstname
Lastname

Conversation

Id
Entity_type
Entity_subtype

Assignment

Id
User_id
Assignable_id (ID of Conversation)
Assignable_type (Class name of Conversation)

One or more Users are connected to a Conversation through Assignments. I am trying to create a query that checks if two Users are having a direct message Conversation or I need to create a new one automatically.

I have this code and it "works" (I get no error) but it returns a Conversation even though only one of the Users are involved in a Conversation. But I need it to only return a Conversation if Both Users share the Same Conversation, ie they are talking to each other.

Conversation.where(entity_type: "conversation", entity_subtype: "direct").joins(:assignments).where(:assignments => {:user_id => ["cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb", "32117e53-9b2f-49c6-8cc8-3a9eb9003a2e"] })

CodePudding user response：

One way to do this is by using GROUP and using HAVING to set a condition on the group:

class Conversion < ActiveRecord::Base
  def self.between(*users)
    joins(:assignments)
      .group(:id)
      .where(assignments: { user_id: users })
      .having(Assignment.arel_table[:id].count.eq(users.length))
  end
end

The advantage is that this approach can be used with any number of users.

If you change out .eq to .gteq you can get conversions that include the two users but isn't a private conversation. On Postgres you can use Assignment.arel_table[Arel.star] as an optimization.

CodePudding user response：

Join assignments twice and in resulting rows, pick a row with assignment user ids match.

user_ids = ["cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb", "32117e53-9b2f-49c6-8cc8-3a9eb9003a2e"]

Conversation
  .where(entity_type: "conversation", entity_subtype: "direct")
  .joins("LEFT JOIN assignments as1 ON assignments.assignable_id = conversations.id AND assignments.assignable_type = 'Conversation'")
  .joins("LEFT JOIN assignments as2 ON assignments.assignable_id = conversations.id AND assignments.assignable_type = 'Conversation'")
  .where('as1.user_id = as2.user_id')
  .where('as1.user_id = ? AND as2.user_id = ?', user_ids[0], user_ids[1])

Will give you list of convos, both are involved