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: https://hexdocs.pm/ecto/Ecto.Query.API.html#fragment/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.