Search code examples
elixirphoenix-frameworkecto

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

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?

Solution

  • 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.