Preload association count/if there are associations records

Add field :sub_thing_count, :integer, virtual: true to your schema of Thing and instead of Thing use the following for querying:

sub_query = from s in SubThing, select: map(s, [:id, :thing_id])
query = from t in Thing,
  join: s in subquery(sub_query), on: s.thing_id == t.id, 
  group_by: t.id
  select_merge: %{sub_thing_count: count(s.id)}

You could skip the subquery and then join the whole table. What is faster probably depends on the column size of the sub_thing table.

You do not need that subquery, if this is just association then:

query = from t in Thing,
  join: s in assoc(s, :sub_thing),
  group_by: t.id
  select_merge: %{sub_thing_count: count(s.id)}

Will work as expected (DB optimisers are good enough to not fetch whole entries.

Thanks a bunch everyone, haven’t tried it yet but looks like exactly what I need

Hello everyone!

So indeed this works, it needs some minor tweaking though:

query = from t in Thing,
  left_join: s in assoc(t, :sub_thing),
  group_by: t.id,
  select_merge: %{sub_thing_count: count(s.id)}

(assoc from t, left_join so that we also get entries with 0 counts and a little comma mistake)

However, I overestimated my handle on SQL and ecto saying I’d be able to easily make it work in a true/false fashion

I don’t really need it right now but it’d be great to know as for large collections count can become quite expensive. So something like an EXISTS query or something - it seems the difficulty might be relating to Support where exists(subquery) as well as where left in subquery · Issue #1479 · elixir-ecto/ecto · GitHub and the discussions about how we can even have Repo.exists? Existence query in `Repo` · Issue #2454 · elixir-ecto/ecto · GitHub

I tried changing the select_merge to true, is_nil(s.id) but for one reason or another none of them worked (most often because in group_by you can only use aggregate functions).

If someone has a result so that we make just a cheaper query that then in the struct saves has_sub_things true/false I’d be very happy to know how that works

I’m not sure how it would work in ecto, but lateral joins would basically do what you’re thinking of:

SELECT thing.id, sub_thing.available IS NOT NULL AS sub_thing_available 
FROM thing AS thing
LEFT JOIN LATERAL (
	SELECT true AS available 
	FROM sub_thing
	WHERE thing_id = thing.id
	LIMIT 1
) AS sub_thing ON true

count(s.id) > 0 would give you a boolean, if you want to stick to what you have right now.

“Hack” with count could be slow thought. I think that right now the best thing you can do is to use fragments, at least until Elixir will be able to do existential queries.

fragment for time being I guess!