Ecto Join based on custom key field name

Question

I'm trying to get join to work using Ecto. Ecto seems to default to 'id' as the key field for join condition. Tried to change it to list_id and user_id using the foreign key settings in the schema, but it doesn't seem to work. What am I missing?

query =
          from(i in SgListItems,
            join: l in assoc(i, :sg_lists),
            join: u in assoc(i, :sg_users),
           select: [l.list_id, i.item_id]
          )

is translated into

SELECT s1."list_id", s0."item_id" FROM "sg_list_items" AS s0 INNER JOIN "sg_lists" AS s1 ON **s1."id"** = s0."list_id" INNER JOIN "sg_users" AS s2 ON **s2."id"** = s0."author_id" ) []

The schema for sg_lists for instance is as follows:

 @primary_key {:list_id, :binary_id, autogenerate: true}
  schema "sg_lists" do
    field(:list_title, :string, null: false)
    field(:list_description, :map, null: false)

    has_many(:sg_list_items, MyApp.SgListItems, foreign_key: :list_id)
    timestamps()
  end

Schema for sg_list_items below.

 @primary_key {:item_id, :binary_id, autogenerate: true}
  schema "sg_list_items" do
    belongs_to(:sg_lists, MyApp.SgLists, foreign_key: :list_id, type: :binary_id)
    field(:item_hierarchy, :map, null: false)
    field(:item_title, :string, null: false)
    field(:item_description, :map, null: false)
    belongs_to(:sg_users, MyApp.SgUsers, foreign_key: :author_id, type: :binary_id)
    timestamps()
  end

Answer 1

You can explicitly set the field to join on, by using the :on option of join.

Try something like:

query =
  from(i in SgListItems,
    join: l in assoc(i, :sg_lists), on: [id: i.list_id],
    join: u in assoc(i, :sg_users), on: [id: i.user_id],
    select: [l.list_id, i.item_id]
  )

On this line: [id: i.list_id], id refers to :sg_lists's id, and on the next line, it refers to :sg_users's one.

If you want to use different fields from :sg_list or :sg_users, you should put those instead of :id, and replace i.list_id for the correct field from SgListItems

This should work.

You can even add multiple conditions to the :on option, something like:

on: [id: i.user_id, other_field: i.other_field] would work as well. But I think what you need is just like in the snippet above.