Andrey Moroz Andrey Moroz - 7 months ago 37
SQL Question

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?

Answer

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