Preload association count/if there are associations records

Phoenix Forum Questions / Help
,
1

:wave:

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:

%Thing{
  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:

2

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.

3 Likes
3

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.

7 Likes
4

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

1 Like
5

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 :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 https://github.com/elixir-ecto/ecto/issues/1479 and the discussions about how we can even have Repo.exists? https://github.com/elixir-ecto/ecto/issues/2454

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

6

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
1 Like
7

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

1 Like
8

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

9

fragment for time being I guess!