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;
I’d write this query as follows:
from companies in "companies",
left_join: connections in "connections",
on: connections.company_id == companies.id,
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
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
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:
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",
where: not exists(from conn in "connections", where: parent_as(:company).id == conn.company_id)
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.
It’s worth mentioning, that such subqueries have limitations in current Ecto implementation. When I played with
parent_as I couldn’t use
dynamic query expressions.
@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 Thank you both for the explanations!