Ecto Simple Foreign Key

Question

In my Phoenix app, I have a Contact table and an Address table. The contact schema is:

schema "contacts" do
    field :name, :string
    field :number, :string
    has_one :address, App.Address, on_delete: :nothing

    timestamps()
end

The address schema is

schema "addresses" do
    field :name, :string
    field :lat, :decimal
    field :lng, :decimal

    timestamps()
end

I just want to have address_id in contacts be selected via a dropdown of addresses filled from the database. However, phoenix is returning

`App.Address.contact_id` in `where` does not exist in the schema in query:

from a in App.Address,
  where: a.contact_id == ^9435,
  select: {a.contact_id, a}

So it is trying to find the contact_id field in address, however I want address to belong to a contact or many contacts, but I don't want the contact_id field in the address table. How is this to be accomplished with ecto associations?

I am aware I can do this with an intermediary table that has an entry for each contact_id and address_id paired up, but I would rather avoid doing so because I think that adds a level of unnecessary complexity and abstraction.

EDIT: Thank you for the answer, I realized I just needed to think of it in the reverse just a few minutes after posting. When thinking of it as an address has many contacts and a contact has one address the relationships make sense. I was just stuck thinking that address belongs to a contact when that is not necessarily true.

Answer 1

If a Contact can have only one Address and an Address can be in multiple Contacts and there's an address_id field in the contacts table, you need a belongs_to relationship in Contact and a has_many in Contact.

schema "contacts" do
  belongs_to :address, App.Address
  ...
end

schema "addresses" do
  has_many :contact, App.Contact
  ...
end

Now you can get the address of a Contact like this:

contact = Repo.get(Contact, 1234) |> Repo.preload([:address])
IO.inspect contact.address.name

Answer 2

That's a normal one-to-many association. You should use belongs_to instead of has_one. That's where the foreign key is defined. You can read more about the difference between them here

Your contact migration should have an association line, such as

add :address_id, references(:addresses, on_delete: :nothing)

Then your schema should have the relations

schema "contacts" do
  belongs_to :address, App.Address
end

schema "addresses" do
  has_many :contact, App.Contact
end

If you're still getting the error after those adjustments, its probably another error in your controller/query and we would need more code examples to help you