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?