Ecto EXISTS clauses with stuff in ’em

I have always loved the EXISTS() clause in PostgreSQL. It’s a super-convenient way of finding records that have associated records in another table without actually joining that table into your main query. Great for when you’re only interested as to whether those records exist and not what’s in them. If you are joining to the many side of your relationship, it also easily skirts display of duplicate records.

With ActiveRecord in Rails, it was always relatively easy to construct an EXISTS() clause, as ActiveRecord isn’t particularly picky about string interpolation. Elixir’s Ecto is – if you try to use the fragment() method to construct a where clause, it’s going to yell at you if it has any string interpolation in it.

Thus, if you want to construct your own IN() clause from a list, you can’t do this:


where: fragment(
"SELECT 1 FROM table_x WHERE table_x.y_id = table_y.id AND myfield in (#{Enum.join(my_awesome_id_list, ",")})"
)

You must instead do this:


where: fragment(
"SELECT 1 FROM table_x WHERE table_x.y_id = table_y.id AND myfield"
) in ^my_awesome_id_list

That’s all well and good – but I wanted to have a query that was wrapped in an EXISTS() clause. So how can I do that when Ecto is tacking on this IN() clause after my fragment?
I discovered that fragment methods are nestable:


where: fragment(
"EXISTS(?)",
fragment(
"SELECT 1 FROM table_x WHERE table_x.y_id = table_y.id AND myfield"
) in ^my_awesome_id_list
)

I’m glad that capability is there, because EXISTS() clauses are quite convenient and performant. I wish Ecto was a bit less picky, but I suppose it’s forcing me to make better choices in my code.

Posted in Uncategorized Tagged with: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*