Custom primary key with preload picks wrong key, what am I doing wrong? Or bug?

Hi, I’m running into a strange issue with custom primary key (autogenerated), has_many relation and preload.
I’m setting up 2 tables, one with a custom primary key that’s autogenerated.

Table 1

defmodule Pstspkr.Repo.Migrations.CreateUrls do
  use Ecto.Migration

  def change do
    create table(:urls, primary_key: false) do
      add :short_url, :string, primary_key: true
      add :long_url, :string

Schema 1

defmodule Pstspkr.Url do
  use Ecto.Schema
  import Ecto.Changeset

  alias Pstspkr.Ecto.ShortUrlId

  @primary_key {:short_url, ShortUrlId, [autogenerate: true]}
  @derive {Phoenix.Param, key: :short_url}

  schema "urls" do
    field :long_url, :string, null: false

    has_many :visits, Pstspkr.Visit


  @doc false
  def changeset(url, attrs) do
    |> cast(attrs, [:long_url])
    |> validate_required([:long_url])

Table 2

defmodule Pstspkr.Repo.Migrations.CreateVisits do
  use Ecto.Migration

  def change do
    create table(:visits) do
      add :short_url, references("urls", column: :short_url, type: :string)
      add :ip, :string


Schema 2

defmodule Pstspkr.Visit do
  use Ecto.Schema
  import Ecto.Changeset

  schema "visits" do
    field :ip, :string

    belongs_to :url, Pstspkr.Url, [foreign_key: :short_url, references: :short_url, type: :string] #, define_field: false, type: :string


  @doc false
  def changeset(visit, attrs) do
    |> cast(attrs, [:short_url, :ip)
    |> validate_required([:short_url, :ip])

Inserting in both tables goes fine, and in Postgress it all looks good.
But when I query this
Repo.all(from u in Url, preload: :visits)

I get an error

[debug] QUERY OK source="urls" db=0.4ms
SELECT u0."short_url", u0."long_url", u0."inserted_at", u0."updated_at" FROM "urls" AS u0 []
** (Ecto.QueryError) deps/ecto/lib/ecto/association.ex:621: field `url_short_url` in `where` does not exist in schema Pstspkr.Visit in query:

from v0 in Pstspkr.Visit,
  where: v0.url_short_url in ^["AmlkTnzI", "Q6lRF4CG", "4zZFhO5B", "af9uxDVF", "nseqc29G", "_qvrCdok"],
  order_by: [asc: v0.url_short_url],
  select: {v0.url_short_url, v0}

But that is strange to me, because I specifically specified the foreign key in the Visits schema:
foreign_key: :short_url

I can see what goes wrong here internally, because looking at the docs foreign_key defaults to _id. So without a custom primary key one would do: belongs_to :url, Pstspkr.Url
And then the foreign_key would default to url_id

It looks like if you specify a custom foreign_key, Ecto still concats the two: url + _ + short_url into url_short_url, even though I’ve specified the foreign_key to be short_url.

Am I doing something wrong or is this a bug?

The common convention when associating tables is to use the format: table name + _ + field.

So if your urls table had an id, the reference column in visits would be url_id. The foreign key however is id (not url_id), as its asking for the name of field in the foreign table. Ecto concatenates the two using the common convention.

So, although it looks weird, you should rename the column in visits to url_short_url for this to work.

As a side note, I wouldn’t recommend structuring your database like this. You’re going to find it quite painful as you move forward.

Using the short_url for primary key, means that, if you ever need to change the short_url, its going to break all the tables that link to it. It’s rarely a good idea to use specific data as a primary key.

Personally, I’d just use a standard id column on the urls table and make the short_url column unique. In visits, reference url_id. You can still use short_url for Phoenix.Param.

You’ll have a much more pleasant time in the long run.

1 Like

Thanks a lot for your explanation and help. I think I can move forward now, thanks!