CODEWITHSUNDEEP
ruby-on-railsrubypg
Hetal Khunti
Hetal Khunti

Reputation: 807

PG query for select and group in rails 4

I am learning join tables for associations and fire select and group query together. Suppose I have database like:

Orders:

#has_many :items
id , title , description

Items:

#belongs_to :order
#belongs_to :product
id , order_id , name , product_id , cost

Now I want to list all order's title with total number of order_id from Item table. For eg:

Order_title_1 => 5 (count of order_id )

I tried this query but gives error: I know it is not correct

Item.joins(:orders).select("orders.id, orders.title, SUM(items.order_id) as total").group("order_id")

Upvotes: 0

Views: 385

Answers (1)

Arup Rakshit
Arup Rakshit

Reputation: 118261

As you are in postgresql, columns present in the SELECT clause must be present in the group by clause, except the one you used inside the aggregate function. But in your case, it didn't so. Try to write the code as :

Item.joins(:order)
    .select("orders.id, orders.title, SUM(items.order_id) as total")
    .group("orders.id, orders.title")

Upvotes: 1

Related Questions