CODEWITHSUNDEEP
activerecordruby-on-rails-4
zkwsk
zkwsk

Reputation: 2136

Rails find all Customers that have exactly 1 Order

I have a data model where a Customer has many Orders. I now need to extract all of the customers that have only placed 1 order and I am scratching my head trying to figure out what to do.

Upvotes: 1

Views: 567

Answers (3)

Saul
Saul

Reputation: 931

To avoid ambiguous reference to the ID field, this would be used:

Customer.joins(:orders).group("customers.id").having("count(orders.id) = 1")

which would generate the following SQL:

SELECT "customers".* FROM "customers" INNER JOIN "orders" ON "orders"."customer_id" =
"customers"."id" GROUP BY customers.id HAVING count(orders.id) = 1

Upvotes: 1

Kirti Thorat
Kirti Thorat

Reputation: 53048

Use this

Customer.joins(:orders).group(:id).having("count(orders.id) = 1")

this will create a query like:

SELECT "customers".* FROM "customers" INNER JOIN "orders" ON "orders"."customer_id" = "customers"."id" GROUP BY id HAVING count(orders.id) = 1

You would get all of the customers that have placed exactly 1 order.

Upvotes: 3

Zini
Zini

Reputation: 914

Consider Customer and Order are ActiveRecord classes and consider you have a code line belongs_to :customer in your Order class definition. And also consider that Order table have a foreign_key or index column named customer_id do as below to get this Customer objects.

Customer.joins(:orders).where("count(orders.id) = 1")

Upvotes: 0

Related Questions