Rails, find duplicate entries in postgres database

Question

I have the following classes:

class Product < ActiveRecord::Base
  has_one :total_purchase
end

class TotalPurchase < ActiveRecord::Base
  belongs_to :product
end

My tables have the following columns (I've left out the majority for clarity):

Product
id | title | end_date | price | discount

TotalPurchase
id | product_id | amount

Numerous products have the same products.title, products.end_date and totalpurchases.amount and I'd like to identify all of those between certain dates. I'm currently using the following sql query to identify them:

Product.find_by_sql("select products.title, products.end_date, total_purchases.amount from products left join total_purchases on products.id = total_purchases.product_id where products.end_date > '2012-08-26 23:00:00' and productss.end_date < '2012-09-02 22:59:59' group by products.end_date, products.title, total_purchases.amount having count(*) > 1")

How would I write the above as a rails query rather than using find_by_sql?

shane · Accepted Answer · 2012-09-07 11:32:58Z

1

Product.select('products.end_date, products.title, total_purchases.amount').
  joins('left join total_purchases on products.id = total_purchases.product_id').
  where('products.end_date > :start_date 
    and products.end_date < :end_date',
    :start_date => '2012-08-26 23:00:00', 
    :end_date => '2012-09-02 22:59:59').
  group('products.end_date, products.title, total_purchases.amount').
  having('count(*) > 1')

edited Sep 7, 2012 at 11:32

shane

992 silver badges11 bronze badges

answered Sep 5, 2012 at 11:08

MrTheWalrus

9,7102 gold badges44 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

shane Over a year ago

Thanks but I get the following error running that query: column "products.id" must appear in the GROUP BY clause or be used in an aggregate function

MrTheWalrus Over a year ago

@shane Answer revised, though note that this assumes that your products both have a unique id, and that that's the same grouping as the one you were doing (i.e. that a given combination of products.title and products.end_date is unique)

shane Over a year ago

Does the query now not just look for products with an id that occurs more than once? since the product id would be unique for all entries it wouldn't be locating the duplicate end_date, title & amounts. Thanks for the syntax though, I'll dig some more

shane Over a year ago

If instead of using products.* only the required end_date, title, amount are used in the select then the group clause doesn't require products.id. Thanks for your help.

Collectives™ on Stack Overflow

Rails, find duplicate entries in postgres database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related