CODEWITHSUNDEEP
sqlrubypostgresqlactiverecordruby-on-rails-5
matQ
matQ

Reputation: 617

Rails 5 select duplicate by two columns or attributes?

I have a model User and my database (postgres) was migrated with repeated values. I want to select duplicates rows by two columns: group_code and birthdate: Eg.

id | group_code | birthdate
2  | 345        |  1980-05-05
3  | 261        |  1994-03-06
4  | 876        |  1997-09-16
5  | 345        |  1980-05-05

result expected (using active record): User with id 2 and 5

Upvotes: 0

Views: 408

Answers (3)

Thomas
Thomas

Reputation: 2952

From what it seems is that you need only one column. Looks like someone might have created two of the same record. You can solve this with below code but also check this out here to avoid possible future duplicates in the database when you create an entry with rails

group = Model.where(group_code: 345).first
parameters = group.attributes

Model.where(group_code: 345).destroy_all

new_group = Model.find_or_create_by(group_code: 345)
new_group.update_attributes(parameters)
new_group.save

Disclaimer: I myself am new to rails and there is probably a much better way to do this but what this is doing is: Finding all the objects that match a criteria then saving those attributes...then deleting all of the objects in the where relation and recreating them. You can even append to the .where with your date if you would like. Model is your model name. Also I've worked with Rails 3 and 6. I know it's update_attributes for Rails 3 but newer version might deprecate...try update for alter versions.

Upvotes: 0

zealous
zealous

Reputation: 7503

You can do using count as well. here is the Demo

select
  id,
  group_code,
  birthdate
from
(
  select
    *,
    count(*) over (partition by group_code, birthdate) as cnt
  from myTable
) val
where cnt > 1

Output:

id  group_code  birthdate
--------------------------
2     345       1980-05-05
5     345       1980-05-05

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use exists:

select t.*
from table t
where exists (select 1 
              from table t1 
              where t1.group_code = t.group_code and 
                    t1.birthdate = t.birthdate and 
                    t1.id <> t.id
             );

Upvotes: 1

Related Questions