Hi,
I have a problem where Ecto preloads are adding an unwanted and unspecified ORDER BY to the query. Hope I’m sharing enough info for someone to tell what’s going wrong, but here we go:
def playlist_with_productions(playlist_id, organisation_id, limit) do
productions = AccessibleContent.get_accessible_productions_for_playlist(organisation_id, playlist_id, limit)
query = from playlist in Playlist,
where: playlist.id == ^playlist_id,
preload: [productions: ^productions]
IO.puts "START WITH PRELOAD"
playlist = Repo.one(query)
IO.puts "END WITH PRELOAD"
IO.puts "START WITHOUT PRELOAD"
productions = Repo.all(productions)
playlist = Map.put(playlist, :productions, productions)
IO.puts "END WITHOUT PRELOAD"
playlist
end
This generates a bunch of SQL, but the version WITHOUT preloads, or I guess perhaps you could call it a manual preload, is correct. The one WITH preloads is not, it returns a bunch of productions, but the ordering is slighty off.
Here’s the Queryable using for (manual) preloading
def get_accessible_productions_for_playlist(organisation_id, playlist_id, limit) do
query =
from production in Production,
join: pp in assoc(production, :playlist_productions),
join: show in assoc(production, :show),
join: channel in assoc(show, :channel),
join: read_access_organisation in assoc(channel, :read_access_organisations),
where: pp.playlist_id == ^playlist_id,
where: production.publication_status == "published",
where: read_access_organisation.id == ^organisation_id,
order_by: [asc: pp.position],
preload: ^preload_production()
query
|> limit(^limit)
end
The order_by: [asc: pp.position], does not seems to get respected as the version with preloading inserts different SQL. I’ll paste that below.
START WITH PRELOAD
[debug] QUERY OK source="playlists" db=0.8ms SELECT p0."id", p0."organisation_id", p0."name", p0."display_title", p0."layout_style", p0."summary", p0."logo_imgix_src", p0."position", p0."created_at", p0."updated_at" FROM "playlists" AS p0 WHERE (p0."id" = $1) [953] [debug] QUERY OK source="playlist_productions" db=1.1ms decode=0.1ms SELECT p0."id", p0."playlist_id", p0."production_id", p0."position", p0."created_at", p0."updated_at", p0."playlist_id" FROM "playlist_productions" AS p0 WHERE (p0."playlist_id" = $1) ORDER BY p0."playlist_id" [953] [debug] QUERY OK source="productions" db=4.3ms decode=0.1ms queue=0.1ms SELECT p0."id", p0."show_id", p0."genre_id", p0."title", p0."description", p0."display_logo", p0."video_time", p0."adaptive_dash_video_url", p0."adaptive_hls_video_url", p0."ads_enabled", p0."clickout_url", p0."clickout_url_time_offset", p0."age_rating", p0."duration_category", p0."ooyala_content_id", p0."live_broadcast", p0."publication_status", p0."published_at", p0."screenshot_imgix_focalpoint", p0."screenshot_imgix_src", p0."origin", p0."player_skin", p0."slug", p0."vas_content_id", p0."vas_content_status", p0."created_at", p0."updated_at", p0."id" FROM "productions" AS p0 INNER JOIN "playlist_productions" AS p1 ON p1."production_id" = p0."id" INNER JOIN "shows" AS s2 ON s2."id" = p0."show_id" INNER JOIN "channels" AS c3 ON c3."id" = s2."channel_id" INNER JOIN "channel_permissions" AS c5 ON c5."channel_id" = c3."id" INNER JOIN "organisations" AS o4 ON o4."id" = c5."organisation_id" WHERE (p1."playlist_id" = $1) AND (p0."publication_status" = 'published') AND (o4."id" = $2) AND (p0."id" = ANY($3)) ORDER BY p1."position" LIMIT $4 [953, 2, [27756, 35351, 37719, 25266, 24608, 28503, 24563, 24590, 31399, 29114, 25428, 25430, 35114, 24571, 28210, 46256, 47274, 35022, 39692, 28311, 42131, 27263, 25928, 24583, 42484, 28870, 45110, 27491, 45796, 38839, 46262, 26933, 32287, 28820, 32222, 43204, 32757, 33073, 30397, 26610, 36015, 31795, 30894, 33534, 47701, 46276, 39145, ...], 6] [debug] QUERY OK source="genres" db=0.3ms SELECT g0."id", g0."key", g0."position", g0."id" FROM "genres" AS g0 WHERE (g0."id" = ANY($1)) [[3, 6]] [debug] QUERY OK source="progressive_video_urls" db=0.4ms decode=0.1ms SELECT p0."id", p0."production_id", p0."label", p0."url", p0."created_at", p0."updated_at", p0."production_id" FROM "progressive_video_urls" AS p0 WHERE (p0."production_id" = ANY($1)) ORDER BY p0."production_id" [[44383, 44706, 45110, 46670, 42131, 47274]] [debug] QUERY OK source="shows" db=0.5ms SELECT s0."id", s0."channel_id", s0."genre_id", s0."title", s0."publication_status", s0."published_at", s0."summary", s0."ads_forbidden", s0."header_imgix_focalpoint", s0."header_imgix_src", s0."logo_imgix_src", s0."picture_imgix_focalpoint", s0."picture_imgix_src", s0."slug", s0."position", s0."published_productions_count", s0."created_at", s0."updated_at", s0."id" FROM "shows" AS s0 WHERE (s0."id" = $1) [1108] [debug] QUERY OK source="channels" db=0.4ms SELECT c0."id", c0."organisation_id", c0."genre_id", c0."highlighted_production_id", c0."name", c0."subtitle", c0."summary_longer", c0."header_imgix_focalpoint", c0."summary", c0."header_imgix_src", c0."logo_imgix_src", c0."logo_with_transparency_imgix_src", c0."publication_status", c0."published_at", c0."ads_forbidden", c0."slug", c0."published_shows_count", c0."created_at", c0."updated_at", c0."id" FROM "channels" AS c0 WHERE (c0."id" = $1) [449] [debug] QUERY OK source="genres" db=0.3ms SELECT g0."id", g0."key", g0."position", g0."id" FROM "genres" AS g0 WHERE (g0."id" = $1) [6] [debug] QUERY OK source="organisations" db=0.3ms SELECT o0."id", o0."name", o0."vas_brand_label", o0."brand_name", o0."id" FROM "organisations" AS o0 WHERE (o0."id" = $1) [8] [debug] QUERY OK source="genres" db=1.0ms SELECT g0."id", g0."key", g0."position", g0."id" FROM "genres" AS g0 WHERE (g0."id" = $1) [3]
END WITH PRELOAD
START WITHOUT PRELOAD
[debug] QUERY OK source=“productions” db=7.6ms
SELECT p0.“id”, p0.“show_id”, p0.“genre_id”, p0.“title”, p0.“description”, p0.“display_logo”, p0.“video_time”, p0.“adaptive_dash_video_url”, p0.“adaptive_hls_video_url”, p0.“ads_enabled”, p0.“clickout_url”, p0.“clickout_url_time_offset”, p0.“age_rating”, p0.“duration_category”, p0.“ooyala_content_id”, p0.“live_broadcast”, p0.“publication_status”, p0.“published_at”, p0.“screenshot_imgix_focalpoint”, p0.“screenshot_imgix_src”, p0.“origin”, p0.“player_skin”, p0.“slug”, p0.“vas_content_id”, p0.“vas_content_status”, p0.“created_at”, p0.“updated_at” FROM “productions” AS p0 INNER JOIN “playlist_productions” AS p1 ON p1.“production_id” = p0.“id” INNER JOIN “shows” AS s2 ON s2.“id” = p0.“show_id” INNER JOIN “channels” AS c3 ON c3.“id” = s2.“channel_id” INNER JOIN “channel_permissions” AS c5 ON c5.“channel_id” = c3.“id” INNER JOIN “organisations” AS o4 ON o4.“id” = c5.“organisation_id” WHERE (p1.“playlist_id” = $1) AND (p0.“publication_status” = ‘published’) AND (o4.“id” = $2) ORDER BY p1.“position” LIMIT $3 [953, 2, 6]
[debug] QUERY OK source=“shows” db=0.3ms
SELECT s0.“id”, s0.“channel_id”, s0.“genre_id”, s0.“title”, s0.“publication_status”, s0.“published_at”, s0.“summary”, s0.“ads_forbidden”, s0.“header_imgix_focalpoint”, s0.“header_imgix_src”, s0.“logo_imgix_src”, s0.“picture_imgix_focalpoint”, s0.“picture_imgix_src”, s0.“slug”, s0.“position”, s0.“published_productions_count”, s0.“created_at”, s0.“updated_at”, s0.“id” FROM “shows” AS s0 WHERE (s0.“id” = $1) [1108]
[debug] QUERY OK source=“genres” db=0.5ms
SELECT g0.“id”, g0.“key”, g0.“position”, g0.“id” FROM “genres” AS g0 WHERE (g0.“id” = $1) [3]
[debug] QUERY OK source=“genres” db=1.3ms
SELECT g0.“id”, g0.“key”, g0.“position”, g0.“id” FROM “genres” AS g0 WHERE (g0.“id” = ANY($1)) [[3, 6]]
[debug] QUERY OK source=“progressive_video_urls” db=1.4ms
SELECT p0.“id”, p0.“production_id”, p0.“label”, p0.“url”, p0.“created_at”, p0.“updated_at”, p0.“production_id” FROM “progressive_video_urls” AS p0 WHERE (p0.“production_id” = ANY($1)) ORDER BY p0.“production_id” [[44383, 44706, 45110, 46670, 42131, 47274]]
[debug] QUERY OK source=“channels” db=1.9ms
SELECT c0.“id”, c0.“organisation_id”, c0.“genre_id”, c0.“highlighted_production_id”, c0.“name”, c0.“subtitle”, c0.“summary_longer”, c0.“header_imgix_focalpoint”, c0.“summary”, c0.“header_imgix_src”, c0.“logo_imgix_src”, c0.“logo_with_transparency_imgix_src”, c0.“publication_status”, c0.“published_at”, c0.“ads_forbidden”, c0.“slug”, c0.“published_shows_count”, c0.“created_at”, c0.“updated_at”, c0.“id” FROM “channels” AS c0 WHERE (c0.“id” = $1) [449]
[debug] QUERY OK source=“genres” db=0.2ms queue=0.1ms
SELECT g0.“id”, g0.“key”, g0.“position”, g0.“id” FROM “genres” AS g0 WHERE (g0.“id” = $1) [6]
[debug] QUERY OK source=“organisations” db=0.8ms
SELECT o0.“id”, o0.“name”, o0.“vas_brand_label”, o0.“brand_name”, o0.“id” FROM “organisations” AS o0 WHERE (o0.“id” = $1) [8]
END WITHOUT PRELOAD
I really hope someone can tell me why the preload version adds this unwanted ORDER BY:
SELECT p0."id", p0."playlist_id", p0."production_id", p0."position", p0."created_at", p0."updated_at", p0."playlist_id" FROM "playlist_productions" AS p0 WHERE (p0."playlist_id" = $1) ORDER BY p0."playlist_id" [953]
