How do I sort my associations when using preload?

Phoenix Forum Questions / Help
1

I want to load associations on an existing struct

entity |> Repo.preload(:components)

executes this query:

SELECT c0."id", c0."type", c0."entity_id", c0."data", c0."inserted_at", c0."updated_at", c0."entity_id" FROM "components" AS c0 WHERE (c0."entity_id" = $1) ORDER BY c0."entity_id" [<<251, 46, 32, 129, 153, 58, 78, 91, 168, 158, 158, 49, 192, 85, 194, 36>>]

I noticed there is a default sort of ORDER BY c0."entity_id" (where does that come from?)

I want to change the sort order, so I tried:

entity |> Repo.preload(components: from(c in Component, order_by: c.type))

However, instead of replacing the order by completely, it only appended my column to the default sort order:
SELECT c0."id", c0."type", c0."entity_id", c0."data", c0."inserted_at", c0."updated_at", c0."entity_id" FROM "components" AS c0 WHERE (c0."entity_id" = $1) ORDER BY c0."entity_id", c0."type" [<<251, 46, 32, 129, 153, 58, 78, 91, 168, 158, 158, 49, 192, 85, 194, 36>>]

This is unintuitive, as I’m still not sorting by the column I specified. How do I get rid of the ORDER BY c0."entity_id" part and replace it with ORDER BY c0.type ?

Here are my schemas:

Entity:


defmodule Thexr.Spaces.Entity do
  use Ecto.Schema
  import Ecto.Changeset
  @derive {Jason.Encoder, only: [:id, :parent_id, :name, :type, :components]}
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "entities" do
    field :name, :string
    field :type, :string
    field :space_id, Ecto.UUID
    field :parent_id, :binary_id
    has_many :components, Thexr.Spaces.Component
    timestamps()
  end

Component

defmodule Thexr.Spaces.Component do
  use Ecto.Schema
  import Ecto.Changeset
  import PolymorphicEmbed, only: [cast_polymorphic_embed: 3]
  @derive {Jason.Encoder, only: [:type, :data]}
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "components" do
    field :type, :string
    belongs_to :entity, Thexr.Spaces.Entity
    field :data, PolymorphicEmbed,
      types: [
        position: Thexr.Components.Vector3,
        rotation: Thexr.Components.Vector3,
        scale: Thexr.Components.Vector3
      ],
      on_type_not_found: :raise,
      on_replace: :update
    timestamps()
  end
1 Like
2

I was facing the same problem today. This seems to work for me:

def get_class!(id) do
    enrollments_query = from e in Enrollment, order_by: [asc: e.processed_at]

    class = Repo.get!(Class, id)
    |> Repo.preload(enrollments: enrollments_query)
    |> Repo.preload(enrollments: [:user])
    class
  end

My associations are Class has_many Users through Enrollments. I needed the table in the middle sorted.

3 Likes
3

I don’t have a middle table, so I’m not sure I follow your solution. I tried breaking out the query to it’s own line but it doesn’t change anything for me:


components_query = from c in Component, order_by: c.type

entity |> Repo.preload(components: components_query)

[debug] QUERY OK source="components" db=5.9ms queue=0.8ms idle=1034.8ms
SELECT c0."id", c0."type", c0."entity_id", c0."data", c0."inserted_at", c0."updated_at", c0."entity_id" FROM "components" AS c0 WHERE (c0."entity_id" = $1) ORDER BY c0."entity_id", c0."type" [<<231, 88, 3, 161, 248, 195, 68, 67, 141, 167, 34, 168, 109, 213, 139, 245>>]

it still wants to order by c0."entity_id", c0."type", which orders by component.entity_id first, and in my case I’m using UUID…

…actually (:man_facepalming: , I just realized that if all the child records are related to the same parent, sorting by the parent_id in each child won’t impact the order of the children since they all have the same parent_id).

So… I guess the generated SQL will still produce the correct results, even though I still don’t understand why it is necessary to include the parent foreign key when sorting associations.

4

I am not sure actually, it might be something that preload does and needs. Afaik you should always have an index on a foreign key and maybe this is where it’s actually used. But I don’t know, someone more experienced needs to say.

5

By using Repo.preload you’re doing one more query. Why not use something like the following?

from(e in Entity, 
left_join: c in assoc(entity, :component),
order_by: c.type,
preload: [component: c])

You might want to read up on the docs on querying and the N+1 problem.

3 Likes
6

Thanks for the optimization suggestion. At the time it was because in my liveview I already had the entity. When the user clicked on the entity, only then would I expand the view to include it’s components.

7

This seems to be a proven answer to ordering preloaded associated records:

After checking, it generates SQL query, which orders by both parent id and children field(s), but this seems harmless

8

If I recall correctly, there’s no N + 1 problem here because preload only makes a single additional query and joins everything in memory, so it’s a 1 + 1 query. In fact, depending on what you want, is better to use preload than join because the latter will compute a cartesian product. Ecto.Query — Ecto v3.8.4.

You can use preload queries or preload functions to change how the associated results are returned. It’s usually the way to go if you need fined-grained control on loading associations.

1 Like
9

Sometimes it can be helpful to apply a default sort order for associations. Since Ecto 3.6, you can do that in your schema via the :preload_order option on has_many and many_to_many associations. Then you can reserve custom preload queries and preload functions for when you need to deviate from your usual ordering.

Providing a default order would look something like:

  schema "entities" do
    # …other fields
    has_many :components, Thexr.Spaces.Component, preload_order: [asc: :inserted_at]
  end
4 Likes