Ordering by preloaded associations seems to work but does not

Questions / Help
,
1

I have a track, associated to track_engineers, which then is tied to a contributor:

# \d track_engineers
                                      Table "public.track_engineers"
      Column       |  Type  |                                  Modifiers
-------------------+--------+-----------------------------------------------------------------------------
 track_engineer_id | bigint | not null default nextval('track_engineers_track_engineer_id_seq'::regclass)
 track_id          | bigint | not null
 contributor_id    | bigint | not null

In my Track model, I have:

has_many :track_engineers, Nin.TrackEngineer, foreign_key: :track_id, references: :track_id
has_many :engineers, through: [:track_engineers, :contributor]

This works great:

track = Track |> where(track_id: 2 ) |> preload(:engineers) |> Repo.one

Excellent. However, when there are two engineers, the ordering can be inconsistent. For what I’ve read, I can do something like this:

    query = from at in AlbumTrack,
    join: a in Album, on: [album_id: at.album_id],
    where: a.active == true,
    where: at.album_id == ^id,
    order_by: {:asc, :track_number},
    preload: [track: [engineers: ^from(c in Contributor, order_by: c.contributor),
                      producers: ^from(c in Contributor, order_by: c.contributor),
                      mixers: ^from(c in Contributor, order_by: c.contributor)]]

In the logs, I do see:

[debug] QUERY OK source="contributors" db=0.3ms idle=59.2ms
SELECT c0."contributor_id", c0."contributor", c0."contributor_id" FROM "contributors" AS c0 WHERE (c0."contributor_id" = ANY($1)) ORDER BY c0."contributor" [[1, 86]]
[debug] QUERY OK source="contributors" db=0.8ms idle=59.6ms
SELECT c0."contributor_id", c0."contributor", c0."contributor_id" FROM "contributors" AS c0 WHERE (c0."contributor_id" = $1) ORDER BY c0."contributor" [23]
[debug] QUERY OK source="contributors" db=0.4ms idle=7.4ms
SELECT c0."contributor_id", c0."contributor", c0."contributor_id" FROM "contributors" AS c0 WHERE (c0."contributor_id" = ANY($1)) ORDER BY c0."contributor" [[131, 137]]

However, the order is inconsistent when I try:

<%= if Enum.any?(album_track.track.engineers) do %>
   <p>Engineer(s): <%= Enum.map(album_track.track.engineers, fn(x) ->
                                       x.contributor 
                                      end)
                                      |> Enum.join(", ") |> raw %>

(ie. sometimes C. Richardson will show up prior to J. McGrath, but other times, J. McGrath will show up first. When I do an IO.inspect, it reflects the inconsistent ordering:

    track: %App.Track{
      __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
      bpm: 96,
      duration: "6:41",
      engineers: [
        %App.Contributor{
          __meta__: #Ecto.Schema.Metadata<:loaded, "contributors">,
          contributor: "J. McGrath",
          contributor_id: 137
        },
        %Nin.Contributor{
          __meta__: #Ecto.Schema.Metadata<:loaded, "contributors">,
          contributor: "C. Richardson",
          contributor_id: 131
        }
      ],

I have read the documentations and read this forum, but just do not know what I am doing wrong, and if what I want to do is even possible.

Thank you

1 Like