Aggregate subquery in where condition

Questions / Help
,
1

Hello :wave:

Having issues with Ecto’s query DSL. Is there any sane way to express the following:

SELECT * FROM foos WHERE id <= (SELECT min(foo_id) FROM bars);

I’d wish I could just write:

from(f in Foo)
|> where([f], f.id <= subquery(from(b in Bar, select: min(b.foo_id)))

But apparently this is not a thing. Any ideas?

The query is a little bit more complex in reality, though not much. Unfortunately it has dynamic prefixes in front of foos and bars, so fragment is not really an option.

Thank you
malte

2 
from f in Foo, 
  join: b in subquery(from(b in bar, select: %{min: min(b.foo_id)})), 
  on: true, 
  where: f.id <= b.min
1 Like
3

@LostKobrakai

Thanks, this works for a more readable solution (also if I put the condition in the :on clause), unfortunately in my exact case it produces a worse query plan, so I had to do something different.

Thanks for the help though!

4

Could you post an example of your actual solution for others who may face the same worsening?

5

@03juan I’m afraid that won’t be of much help. As said, the actual query is a tiny bit more complicated, in particular I realized that I also need to handle the empty bars table case, iow. the MIN() query can return null. Consulted the query planner for various solutions, and none of them really worked out (in terms of only requiring a bitmap index scan on the index on foos.id).

In the end I resorted to using ALL(), as in

from(f in Foo, where: f.id <= all(from(b in Bar, select: bar.foo_id)))

Works, but according to the query planner performs a sequential scan on Foo :person_shrugging:

6

FWIW, if someone wants to give it another try, the query is this one

1 Like