A subtlety of Ecto.Query.preload

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"

source