Pau Riosa
Posted on April 6, 2022
Intro
In this tutorial, I want to share with you how to remove duplicate rows in your schema using Ecto.
Preparation
- In your schema migration, let say you have this schema setup.
CREATE TABLE(:student_teacher, primary_key: false) do
add :id, :uuid, primary_key: true
add :student_id, references(:students, type: :uuid)
add :teacher_id, references(:teachers, type: :uuid)
end
- And then let's insert a couple of duplicate record. (Supposed you are using Ex.Machina for your data mock ups )
student = insert(:student)
teacher = insert(:teacher)
insert_list(100, :student_teacher, student: student, teacher: teacher)
Check for Duplicate Rows using IEx
Run iex -S mix in your console and check for duplicate rows.
iex) query = SELECT COUNT(*), student_id, teacher_id FROM
student_teacher group by student_id, teacher_id having count(*) > 1;
iex) Ecto.Adapter.SQL.query!(Repo, query)
iex) %Postgrex.Result{
columns: ["count", "student_id", "teacher_id"],
command: :select
num_rows: 1,
rows: [
[
100,
<<student_id>>,
<<teacher_id>>
],
]
}
Run Query to delete Duplicate Rows
Repo.transaction(
fn ->
query = """
DELETE FROM student_teacher s1
USING student_teacher s2
where s1.id < s2.id
AND s1.student_id = s2.student_id
AND s1.teacher_id = s2.teacher_id
"""
Ecto.Adapters.SQL.query!(Repo, query)
end,
timeout: :infinity
)
Happy Coding!
💖 💪 🙅 🚩
Pau Riosa
Posted on April 6, 2022
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.