I have two models, User and Note. Note has a user_id field which points to User. I did not create associations when I create the Phoenix app. I would like to add associations now: One user can have many notes, but a note can have only one user (the author). How do I add the association this late date?
2 Likes
Perhaps you could modify your posts table. (Assuming your posts table has a field user_id just as an integer).
So you may want to try creating a new migration with something like this:
alter table(:notes) do
modify :user_id, references(:users)
end
and in your schemas:
schema "posts" do
...
belongs_to :author, User, foreign_key: :user_id
end
schema "users" do
...
has_many :posts, Post
end
1 Like
Thank you dgamidov and sysashi!
1 Like
Hello again,
I tried this migration:
First Try
defmodule LookupPhoenix.Repo.Migrations.AddAssociation do
use Ecto.Migration
def change do
alter table(:notes) do
modify :user_id, references(:users)
end
end
end
But got this error:
06:07:36.061 [info] alter table notes
** (Postgrex.Error) ERROR 23503 (foreign_key_violation): insert or update on table "notes" violates foreign key constraint "notes_user_id_fkey"
table: notes
constraint: notes_user_id_fkey
Key (user_id)=(20) is not present in table "users".
Table notes has an id and a user_id, where the latter points to id of table ‘users’
Second try
If I modify the schema, eg., add has_many :notes, LookupPhoenix.Note to User and add belongs_to :user, LookupPhoenix.User to Note, then I get a compilation error:
== Compilation error on file web/models/note.ex ==
** (ArgumentError) field/association :user_id is already set on schema
1 Like
Can you post your current schemas of notes and users please.
And it seems like your user_id column contains non existing user id (20)
1 Like
Try this
defmodule MyApp.Repo.Migrations.CreateJoin do
use Ecto.Migration
def change do
create table :joins do
add :note_id, references(:notes, on_delete: :delete_all, type: :uuid), null: false
add :user_id, references(:users, on_delete: :delete_all, type: :uuid), null: false
end
create index :joins, [:user_id, :note_id], unique: true
end
end
defmodule MyApp.User do
@primary_key {:id, Ecto.UUID, autogenerate: true}
schema "users" do
has_many :joined_notes, MyApp.Join
has_many :joined, through: [: joined_notes, :note]
end
end
defmodule MyApp.Note do
@primary_key {:id, Ecto.UUID, autogenerate: true}
schema "notes" do
has_many :notes_joined, MyApp.Join
has_many :joiners, through: [: notes_joined, :user]
end
end
defmodule MyApp.Join do
@foreign_key_type Ecto.UUID
schema "joins" do
belongs_to :note, Note
belongs_to :user, User
end
@required_params ~w(note_id user_id)a
def changeset(struct, params \\ %{}) do
struct
|> cast(params, @required_params)
|> validate_required(@required_params)
|> unique_constraint(:user_id, name: :joins_user_id_notes_id_index, message: "Some cool message")
end
end
1 Like
syashsi, Below are the schemas. I’ve been running the app without associations (and so without
belongs_to .. and has_many ... The user_id field of a Note is always the id of a User –
enforced in code, but I should be using associations.
DmytroNasyrov, I am reluctant to use your proposed solution at this time because I am using integer ID’s – will have to think this one through. Thankyou!
Notes
schema "notes" do
use Timex.Ecto.Timestamps
field :title, :string
field :content, :string
field :user_id, :integer
field :viewed_at, :utc_datetime
field :edited_at, :utc_datetime
belongs_to :user, LookupPhoenix.User
timestamps()
end
Users
schema "users" do
field :name, :string
field :username, :string
field :email, :string
field :password, :string
field :password_hash, :string
field :registration_code, :string
field :tags, {:array, :string }
field :read_only, :boolean
field :admin, :boolean
field :number_of_searches, :integer
field :search_filter, :string
has_many :notes, LookupPhoenix.Note
timestamps()
end
1 Like
uuid is just as an option. It has no influence on many_to_many association at all. I copy-pasted this snippets right from my code and decide to leave uuid in case someone will look for the same solution but with uuid. It will work with int ids too.
1 Like
DmytroNasyrov – thanks so much for this. I have been considering using UUID’s in this experiment but haven’t decided yet whether it is the right thing to do.
sysashi – It turned out that having some bad Note records was the problem. I deleted them, ran the migrations without has_many .. and belongs_to.
Addendum I enabled the lines has_many .. and belongs_to :user, LookupPhoenix.User and commented out the line field :user_id, :integer. Now everything is working
1 Like
Thankyou sysashi and DmytroNasyrov – all working now!!
2 Likes
Summary of how to add an association after the fact. Thanks to all and special thanks to @sysashi.
In my case I had two models, User and Note – see schemas listed below. I wanted a note to be associated to one user and a user to have many notes. Notes and Users were already manually linked via the user_id field in Note.
Step 1: Make sure that there are no Note records with user_id pointing to nonexistent users
Step 2: Run the migration below.
Step 3. Enable the lines
belongs_to :user, LookupPhoenix.User` # in Note
and
has_many :notes, LookupPhoenix.Note # in User
Then comment out the line
field :user_id, :integer # in Note
Migration
def change do
alter table(:notes) do
modify :user_id, references(:users)
end
end
Note Schema
schema "notes" do
use Timex.Ecto.Timestamps
field :title, :string
field :content, :string
field :user_id, :integer
# irrelevant fields
# Will add this after migration: # belongs_to :user, LookupPhoenix.User
timestamps()
end
User Schema
schema "users" do
field :name, :string
field :username, :string
# irrelevant fields
# will add this after migration: # has_many :notes, LookupPhoenix.Note
timestamps()
end
4 Likes
Glad you got it sorted - might be worth adding step 3 to the Schema code blocks in your post in case it confuses someone (or adding the final code separately below).
2 Likes
