#database #development #develpmoment #elixir
A subtlety of Ecto.Query.preload
is that it makes an extra DB query. To avoid this you need to explicitly do the
join
yourself.
E.g. see below how the first query loads the teams with a separate SELECT
but the second loads the users and teams all
at once.
So, if you do this:
1Repo.all(from a in Activity, preload: :webhook_logs)
The generated SQL will be:
1SELECT
2 a0."id",
3 a0."strava_id",
4 a0."garmin_id",
5 a0."inserted_at",
6 a0."updated_at"
7FROM
8 "activity_activities" AS a0;
9
10SELECT
11 w0."id",
12 w0."object_type",
13 w0."aspect_type",
14 w0."updates",
15 w0."owner_id",
16 w0."subscription_id",
17 w0."event_time",
18 w0."object_id",
19 w0."inserted_at",
20 w0."updated_at",
21 w0."object_id"
22FROM
23 "webhook_logs" AS w0
24WHERE
25 (w0."object_id" = ANY(1, 2, 3 ]))
26ORDER BY
27 w0."object_id";
To avoid the extra query, you can do this instead:
1Repo.all(from a in Activity, left_join: l in assoc(a, :webhook_logs), preload: [webhook_logs: l])
The generated SQL will be:
1SELECT
2 a0."id",
3 a0."strava_id",
4 a0."garmin_id",
5 a0."inserted_at",
6 a0."updated_at",
7 w1."id",
8 w1."object_type",
9 w1."aspect_type",
10 w1."updates",
11 w1."owner_id",
12 w1."subscription_id",
13 w1."event_time",
14 w1."object_id",
15 w1."inserted_at",
16 w1."updated_at"
17FROM
18 "activity_activities" AS a0
19 LEFT OUTER JOIN "webhook_logs" AS w1 ON w1."object_id" = a0."strava_id"
If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.