Ecto: How to Get Parent from association IDs

Viewed 53

So I'm creating a SLACK clone. and I hit the wall when I'm trying to show the existing conversation when creating a new conversation with the same participants.

here're my tables

schema "conversations" do
...
  has_many :participants, Participant
...
schema "participants" do
  belongs_to :user, User
  belongs_to :conversation, Conversation
  ...

here's the scenario: John, Peter, and Ron had an old conversation. Now, John tries to create a new conversation involving the three of them.

here's what the backend receives.

%{user_ids: [john_user_id, peter_user_id, ron_user_id]}

the backend should return the existing conversation.

I've tried queries like these:

  def find_conversation_by_participants(%{user_ids: user_ids}) do
    Conversation
    |> join(:left, [c], p in assoc(c, :participants))
    |> where_involved(user_ids)
    |> group_by([c, p], [c.id, p.conversation_id])
    |> distinct(true)
    |> Repo.all()
  do

  def where_involved(query, user_ids) do
    Enum.reduce(user_ids, query, fn user_id, acc ->
      or_where(acc, [_, p], p.user_id == ^user_id)
    end)
  end
  query = from c in Conversation,
    as: :conversation,
    where: exists(
      from(
        p in Participant,
        where: parent_as(:conversation).id == p.conversation_id and p.user_id in ^participants,
        select: 1,
        group_by: p.conversation_id
      )
    )

  result = Repo.all(query)

but I always get all conversations that each of them are involved. Is there a way to find the Common parent from the list of associated ID's?

1 Answers

I would go on the participants table and select where the participant_id is in the list of id's. Then I'll count on the resulting table the conversation id, and filter them with having clause (the condition being having count = length of the user list: in this case 3). You can actually do this with a single query with a subquery.

Related