Using Ecto, how do I build a query that returns results that appear in two separate associations?

Question

Let's say I have 3 schemas: Tag, Post, User.

There's a many to many relationship between Post and Tag with a join table, and a many to many relationship between Post and User with a join table.

I want to select posts that belong to a given tag and a given user.

user_posts_query = user |> assoc(:posts)
tag_posts_query = tag |> assoc(:posts)

Is there any way I can combine those two query objects and when using Repo.all() get only the overlapping results from each?

Answer 1

I don't have an opportunity to test it but I think something like this should work:

Post
|> join(:inner, [p], u in assoc(p, :users))
|> join(:inner, [p], t in assoc(p, :tags))
|> where([p, u, t], u.id == ^user.id and t.id == ^tag.id)
|> Repo.all()

It may not be exactly what you are looking for but that may work.