Ecto: Pre-loading Associations; Query Compositions

Hello All,

I am having trouble with querying and pre-loading the associated data. I can manually build the query that pulls in the data without using schema associations but it’s not to my liking.

query = 
  from m in "movements", 
  join: c in "categories",
  on: m.category_id == c.id,
  select: %Movement{id: m.id, name: m.name, category: c.name}

Repo.all(query)

Am I misunderstanding something here? I think query expressions/composition are fantastic and help me clean my code.

The relationship between the Movement and Category is a One-to-One/Has-One Relationship. I don’t see the need for the Category schema/table to require the movement_id.

The Movement can have only one category and the Category can be belong to many movements.

Error

* (Postgrex.Error) 
   ERROR 42703 (undefined_column) column c1.movement_id does not exist

    query: 
      SELECT m0."id", m0."name" 
      FROM "movements" AS m0 
      INNER JOIN "categories" AS c1 
      ON (c1."movement_id" = m0."id") AND (m0."category_id" = c1."id") 
      WHERE (m0."category_id" = c1."id")

    (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:913: 
        Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:828: 
        Ecto.Adapters.SQL.execute/6
    (ecto 3.10.1) lib/ecto/repo/queryable.ex:229: 
        Ecto.Repo.Queryable.execute/4
    (ecto 3.10.1) lib/ecto/repo/queryable.ex:19: 
        Ecto.Repo.Queryable.all/3

Migration

    create table(:categories) do
      add :name, :string, null: false
    end
    create unique_index(:categories, [:name])

    create table(:movements) do
      add :name, :string, null: false
      add :category_id, references(:categories, on_delete: :nothing), null: false
    end

Schemas

defmodule Movement do
  use Ecto.Schema

  schema "movements" do
    field :name, :string
    has_one :category, Category
  end
end

defmodule Category do
  use Ecto.Schema

  schema "categories" do
    field :name, :string
    belongs_to :movement, Movement
  end
end

Query Builder

defmodule Movement.Query do  
  import Ecto.Query, warn: false

  def base, do: Movement

  def with_categories do
    base()
    |> join_categories()
  end

  def join_categories(query \\ base()) do
    join(query, :left, [m], c in assoc(m, :category), on: m.category_id == c.id)
  end

  def preload_categories(query) do
    preload(query, [m, c], :category)
  end

  def filter_by_category(query \\ base(), category) do
    where(query, [m, c], c.name == ^category)
  end

Context Query

  def list_movements_with_categories do
    Movement.Query.with_categories
    |> Movement.Query.preload_categories()
    |> Repo.all()
  end

The associations are incorrectly defined in your schemas. Movement belongs to category, and Category has many movements.

1 Like

Ah, I see. impostor syndrome sets back in

Thanks a bunch.

Oh, it’s just a small mistake, we all do it. Your code looks great otherwise.

When using the function form of join, where, etc, I prefer to use named bindings. Also, when using assoc one a join call, the :on condition between the two tables is added for you.

3 Likes