CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordhaving
Andrey Moroz
Andrey Moroz

Reputation: 293

When I do having PG::UndefinedColumn: ERROR

I cannot understand why. Help me please I have Customer table, that has many orders. So Customer(id), Order(id, customer_id). I want to get all customers, who has less than 100 orders. So I do sql query to postgres(I use Rails and ActiveRecord), but query is 

SELECT customers.*, COUNT(*) AS c_c FROM "customers" LEFT OUTER JOIN orders ON orders.customer_id = customers.id GROUP BY "customers"."id" HAVING c_c < 100 OR orders.id IS NULL

I have an error

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "c_c" does not exist

Why? How can I do that?

Upvotes: 2

Views: 1522

Answers (2)

Anthony E
Anthony E

Reputation: 11235

The query itself doesn't look valid. You can't reference aliased columns from the HAVING clause. Also your COUNT(*) is ambiguous. You can fix by replacing c_c with COUNT(orders.id) in HAVING, and add COUNT(orders.id) in the SELECT:

SELECT customers.*, COUNT(orders.id) AS c_c FROM "customers" LEFT OUTER JOIN orders ON orders.customer_id = customers.id GROUP BY "customers"."id" HAVING COUNT(orders.id) < 100 OR orders.id IS NULL

EDIT

For an ActiveRecord query try this:

Customer.joins("LEFT JOIN orders ON orders.customer_id = customers.id").group("customers.id").having("COUNT(orders.id) < 100")

Make sure you have a has_many :orders defined in Customer

Upvotes: 2

Deepak Mahakale
Deepak Mahakale

Reputation: 23671

You can't use c_c in having

SELECT customers.*, (
  SELECT COUNT(*)
  FROM orders
  WHERE customers.id = orders.customer_id
) AS c_c 
FROM "customers" 
LEFT OUTER JOIN orders 
ON orders.customer_id = customers.id 
GROUP BY "customers"."id" 
HAVING (SELECT COUNT(*)
  FROM orders
  WHERE customers.id = orders.customer_id
) < 100 OR orders.id IS NULL

Upvotes: 1

Related Questions