Ecto: order_by query does not work in preload for has_many assocations

[milch]

I have a schema like this:

schema "foos" do
  has_many :bars, Bar
end
schema "bars" do
  belongs_to :foo, Foo
  has_many :baz, Baz
end
schema "baz" do
  belongs_to :bars, Bar
  field :sort_seq, :integer
end

(I left out the Module definitions for conciseness). Now I perform the following two queries:

foo = (from f in Foo, 
         preload: :bars, 
         select: f) 
      |> Repo.one
# ... Some code in-between
query = from b in Baz, order_by: b.sort_seq
bars = foo.bars 
       |> Repo.preload(baz: query)

If I inspect the bars variable after the last query, a lot of my Baz objects will be missing. It seems that the algorithm that does the preloading for multiple objects expects them to be sorted by the foreign key, which doesn't hold if you set order_by like I did. If I change query to query = from b in Baz, order_by: [b.bar_id, b.sort_seq] it works.

Here are some logs:

iex(1)> (from b in Baz, order_by: b.sort_seq) |> Repo.all
[debug] SELECT b0."id", b0."sort_seq", b0."bar_id" FROM "baz" AS b0 ORDER BY b0."sort_seq" [] OK query=1.4ms
[%Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
  bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
  bar_id: 2, id: 7, sort_seq: 0},
 %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
  bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
  bar_id: 1, id: 2, sort_seq: 2},
 %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
  bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
  bar_id: 2, id: 5, sort_seq: 4},
 %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
  bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
  bar_id: 2, id: 4, sort_seq: 5},
 %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
  bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
  bar_id: 1, id: 1, sort_seq: 6},
 %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
  bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
  bar_id: 1, id: 3, sort_seq: 34},
 %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
  bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
  bar_id: 2, id: 6, sort_seq: 77}]

iex(2)> bars = foo.bars |> Repo.preload(baz: (from b in Baz, order_by: [b.sort_seq]))
[debug] SELECT b0."id", b0."sort_seq", b0."bar_id" FROM "baz" AS b0 WHERE (b0."bar_id" IN ($1,$2)) ORDER BY b0."sort_seq", b0."bar_id" [1, 2] OK query=1.8ms
[%Bar{__meta__: #Ecto.Schema.Metadata<:loaded>,
  baz: [%Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
    bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
    bar_id: 1, id: 1, sort_seq: 6},
   %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
    bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
    bar_id: 1, id: 3, sort_seq: 34}],
  foo: #Ecto.Association.NotLoaded<association :foo is not loaded>,
  foo_id: 1, id: 1},
 %Bar{__meta__: #Ecto.Schema.Metadata<:loaded>,
  baz: [%Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
    bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
    bar_id: 2, id: 6, sort_seq: 77.0}],
  foo: #Ecto.Association.NotLoaded<association :foo is not loaded>,
  foo_id: 1, id: 2}]

iex(3)> bars = foo.bars |> Repo.preload(baz: (from b in Baz, order_by: [b.bar_id, b.sort_seq]))
    [debug] SELECT b0."id", b0."sort_seq", b0."bar_id" FROM "baz" AS b0 WHERE (b0."bar_id" IN ($1,$2)) ORDER BY b0."bar_id", b0."sort_seq", b0."bar_id" [1, 2] OK query=2.0ms
    [%Bar{__meta__: #Ecto.Schema.Metadata<:loaded>,
    baz: [%Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
        bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
        bar_id: 1, id: 2, sort_seq: 2,
        },
    %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
        bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
        bar_id: 1, id: 1, sort_seq: 6,
        },
    %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
        bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
        bar_id: 1, id: 3, sort_seq: 34,
        }],
    foo: #Ecto.Association.NotLoaded<association :foo is not loaded>,
    foo_id: 1, id: 1, inserted_at: #Ecto.DateTime<2015-09-01T16:04:55Z>},
    %Bar{__meta__: #Ecto.Schema.Metadata<:loaded>,
        baz: [%Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
        bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
        bar_id: 2, id: 7, sort_seq: 0,
        },
        %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
        bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
        bar_id: 2, id: 5, sort_seq: 4,
        },
        %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
        bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
        bar_id: 2, id: 4, sort_seq: 5,
        },
        %Baz{__meta__: #Ecto.Schema.Metadata<:loaded>,
        bar: #Ecto.Association.NotLoaded<association :bar is not loaded>,
        bar_id: 2, id: 6, sort_seq: 77}],
        foo: #Ecto.Association.NotLoaded<association :foo is not loaded>,
        foo_id: 1, id: 2}]

It seems that preload would need to prepend its own order_by clause, rather than appending it.

[milch]

Note that the documentation for Ecto.Repo.preload/2 even mentions these kinds of queries:

Repo.preload posts, comments: from(c in Comment, order_by: c.published_at)

[josevalim]

Thanks for the excellent and detailed bug report.