Ecto with IN subquery

Is this kind of query possible with “pure” Ecto? I know I can rewrite this as a join but that is not what I want too know :slight_smile:

SELECT * FROM posts p WHERE IN(SELECT post_id FROM post_tags WHERE post_tags.tag = ?)

Schema is something like this:

- id :integer
- title :string

- post_id :integer
- tag :string

Hey! I think this is doable with a join:

from p in Post,
  join: t in PostTag, on: == t.post_id,
  where: t.tag == ^tag

If you have an association in the Post schema then you could do join: t in assoc(p, :post_tags),

1 Like

Thanks @benwilson512 I know I can do that :slight_smile:


I don’t think WHERE IN itself is actually supported in Ecto, because it’s basically always re-written as a join anyway. is how you do queries inside of queries, and it explicitly only supports that in from and join.

Do you have a particular concrete query in mind?

From a quick perusal of the Ecto docs it looks like it is not currently possible:

If you attempt to write it as where: in ^subquery(foo), Ecto won’t accept such query. However, the subquery above can be written as a JOIN, which is supported by Ecto. The final Ecto query will look like this:

Although if you really wanted a WHERE IN query you could use the escape hatch Ecto.Query.API.fragment/1 to implement it:

No. Not directly. subqueries are supported using JOIN syntax. Something like:

  tags_subquery = from(p in PostTags, where: p.tag == ^tag_value, select: p.post_id)
  Repo.all(from(p in Posts, join: t in subquery(tags_subquery), on: == t.post_id))

other than that I believe you are left with fragments. good luck.

Thanks for all your time, help and suggestions. I guess I will have to use joins instead then, even though I sometimes prefer writing in the subquery form.
Ectos fragment API is a no go since I have not found a good way (yet) of combining that approach with my usage of query prefixes.

Can now use subquery with where - see Ecto.Query — Ecto v3.9.4


I think both my wisdom and Ecto has improved in 5 years :sweat_smile: It can be done with both fragment and subquery as you pointed out.

 def find_tagged_posts_with_fragment(tag) do
    Ecto.Query.from(p in Post,
      where: fragment("? = (select post_id from post_tags where post_tags.tag = ?)",, ^tag)
    |> Repo.all()

  def find_tagged_posts_with_subquery(tag) do
    tagged_posts = Ecto.Query.from(t in PostTag, select: t.post_id, where: t.tag == ^tag)

    Ecto.Query.from(p in Post,
      where: in subquery(tagged_posts)
    |> Repo.all()
1 Like