Ecto with IN subquery

Questions / Help
1

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 p.id IN(SELECT post_id FROM post_tags WHERE post_tags.tag = ?)

Schema is something like this:

post
- id :integer
- title :string

post_tags
- post_id :integer
- tag :string
2

Hey! I think this is doable with a join:

from p in Post,
  join: t in PostTag, on: p.id == 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
3

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

4

Oops!

I don’t think WHERE IN itself is actually supported in Ecto, because it’s basically always re-written as a join anyway. https://hexdocs.pm/ecto/Ecto.Query.html#subquery/2 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?

5

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

If you attempt to write it as where: p.id 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:

https://hexdocs.pm/ecto/Ecto.Query.html#subquery/2-examples

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

6

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: p.id == t.post_id))

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

7

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.

8

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

3 Likes
9

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 = ?)", p.id, ^tag)
    )
    |> Repo.all()
  end

  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: p.id in subquery(tagged_posts)
    )
    |> Repo.all()
  end
1 Like