How to query object associations?-CodePudding

I am building a Rails 5.2 app. In this app I got three main objects, Conversation, User and Assignment.

Conversation

Has_many :assignments

- Id
- Entity_type
- Entity_subtype

User

Has_many :assignments

- Id
- Name

Assignment

This is a polymorphic association.

- Id
- User_id
- Assignable_id
- Assignable_type

<Assignment id: "5475728b-a0c0-4959-a8a7-17af9a5e224b", account_id: "243c9502-c3ab-4362-907b-ca630c2caf04", user_id: "cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb", assignable_type: "Conversation", assignable_id: "f753e48c-6a67-4410-9748-8454e5904405", role: "owner">

I want to create a query where I find (check) if two individual Users are connected to the same Conversation through an Assignment.

I tried this and it returns conversations but how can I check both user IDs?:

Conversation.joins(:assignments).where(:assignments => {:user_id => "cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb"})

But how can I check the to User IDs are connected to the same Conversation?

CodePudding user response：

Have you tried this?

Conversation.joins(:assignments)
.where(:assignments => {:user_id => ["cdd3c6be-ac78-46f2-a7ae-7f2299b6fedb", "c974419c-1bbd-4537-9d4f-d8df262ace82"]})
.group("assignable_id", "conversations.id")
.having("count(1) > ?", 1)

What it should do is to first fetch all the assignments for given user_ids, then group it by assignable_id and filter those that have more than one assignments.

Note: As user_id I added just some random UUID - I suppose you want to search for the specific Users in your code.