Struggling to make a query for an aggregate count where a subquery evaluates to a specific value only

Questions / Help
1

hey! fairly new to Ecto and wanting to make a query where: count of all the models where the model’s count of children == 0 AND one of the model’s attributes are a specific value. I’ve written it out in SQL and am using a subquery, but i’m just struggling to convert it to Ecto. Here is the SQL

SELECT * FROM companies c WHERE
(SELECT COUNT(*) FROM connections AS conn WHERE conn.company_id=c.id)= 0;
2

Hey!
I’d write this query as follows:

from companies in "companies",
  left_join: connections in "connections",
  on: connections.company_id == companies.id,
  where: is_nil(connections.id)
3 Likes
3

Oh this is great, thank you so much it worked out perfectly! I’ll keep this in mind as other portions of this page will use similar logic :slight_smile:

4

Was going to say this works perfectly, but let’s say I wanted to only find companies with a SPECIFIC count of children, I should then use sub-queries at that point right? @fuelen for example, companies only with 1 connection

5

Do not use COUNT if you are checking for existence, especially when it is PostgreSQL as it can get quite slow. What you want is existential queries:

SELECT *
FROM companies c
WHERE NOT EXISTS (SELECT 1 FROM connections AS conn WHERE conn.company_id = c.id)

New Ecto versions support such queries via:

from c in "companies",
  as: :company,
  where: not exists(from conn in "connections", where: parent_as(:company).id == conn.company_id)
3 Likes
6

Yes, you can use count + subquery as in your initial approach. But I’m not sure if it has a good performance.
Another approach is to fetch company ids using

select company_id from connections group by company_id having count(*) = 1;

and then fetch companies by these ids.

1 Like
7

It’s worth mentioning, that such subqueries have limitations in current Ecto implementation. When I played with exists + parent_as I couldn’t use dynamic query expressions.

1 Like
8

@fuelen @hauleth thank you both! I think i spent more time than I wanted to trying to optimize this, and did the opposite by really wanting to use a subquery :frowning: Thank you both for the explanations!

1 Like