Preload association count/if there are associations records


Relatively common setup:

I have a Thing which has_many SubThing. In the index view (showing many Things) I want to display a link or a text if there are any “sub things”. Otherwise not.

Of course I could just Repo.preload(:sub_things) but I don’t need them here so I wanna be light on the db/memory etc.

I know that I can write some query that returns me its own data structure telling me which Thing record has how many SubThings (or id any at all) and I could use that second data structure to display/not display them. I don’t have that query but I’m confident that I can write it with relative ease :smiley:

I think this is a relatively common problem, so the question is: “Is there a better/standard way to do this?” (my googling came up empty)

If I could wish for something then my Thing struct would have an additional attribute like:

  sub_things_exist?: true/false
  # or / and
  sub_things_count: 5

And I could get there with Repo.preload(:sub_things_exist?) or something similar.

Just to be clear I don’t want to introduce a counter cache (yet) it’s fine if it’s still queried from the database dynamically but ideally in one big query (this is on an index page with a lot of elements).

Thanks and cheers as always! :green_heart:

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 ==, 
  select_merge: %{sub_thing_count: count(}

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),
  select_merge: %{sub_thing_count: count(}

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 :dancer:

1 Like

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),
  select_merge: %{sub_thing_count: count(}

(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 :sweat_smile:

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 and the discussions about how we can even have Repo.exists?

I tried changing the select_merge to true, is_nil( 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 :grin:

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

SELECT, sub_thing.available IS NOT NULL AS sub_thing_available 
FROM thing AS thing
	SELECT true AS available 
	FROM sub_thing
	WHERE thing_id =
) AS sub_thing ON true
1 Like

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

1 Like

“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.