How to find records that have duplicate data using Active Record
Translating @TuteC into ActiveRecord:
sql = 'SELECT id, COUNT(id) as quantity FROM types GROUP BY name HAVING quantity > 1'#=>Type.select("id, count(id) as quantity") .group(:name) .having("quantity > 1")
Here's how I solved it with the AREL helpers, and no custom SQL:
Person.select("COUNT(last_name) as total, last_name") .group(:last_name) .having("COUNT(last_name) > 1") .order(:last_name) .map{|p| {p.last_name => p.total} }
Really, it's just a nicer way to write the SQL. This finds all records that have duplicate last_name values, and tells you how many and what the last names are in a nice hash.
I was beating my head against this problem with a 2016 stack (Rails 4.2, Ruby 2.2), and got what I wanted with this:
> Model.select([:thing]).group(:thing).having("count(thing) > 1").all.size => {"name1"=>5, "name2"=>4, "name3"=>3, "name4"=>2, "name5"=>2}