ActiveRecord & PostgreSQL ordering error

Question

I have two models in my Rails app: Product and CardItem

Product has field price

CardItem has field quantity and belongs to Product.

I want to select total price of all card items depending on product.price * quantity

class CardItem < ActiveRecord::Base
  belongs_to :product

  scope :total_price, -> {
    result = joins(:product).select('SUM(products.price * card_items.quantity) as total_price')

    result.empty? ? 0 : result.first.total_price
  }
end

This code perfectly works in sqlite, but with postgresql it throws error:

PG::GroupingError: ERROR:  column "card_items.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...t_id" WHERE "card_items"."user_id" = $1  ORDER BY "card_item...
                                                             ^
: SELECT  SUM(products.price * card_items.quantity) as total_price FROM "card_items" INNER JOIN "products" ON "products"."id" = "card_items"."product_id" WHERE "card_items"."user_id" = $1  ORDER BY "card_items"."id" ASC LIMIT 1
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "card_items.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...t_id" WHERE "card_items"."user_id" = $1  ORDER BY "card_item...
                                                             ^
: SELECT  SUM(products.price * card_items.quantity) as total_price FROM "card_items" INNER JOIN "products" ON "products"."id" = "card_items"."product_id" WHERE "card_items"."user_id" = $1  ORDER BY "card_items"."id" ASC LIMIT 1

help my fix this please

p.s. I use Rails 4.0.0

OK, I fix this by explicit append order('') after select, but may be there are another solutions? Is this a bug in ActiveRecord? — atomAltera
– atomAltera, Commented Dec 21, 2013 at 5:52

Richard Huxton · Accepted Answer · 2013-12-21 16:12:53Z

1

PostgreSQL is strict on its aggregate queries. SQLite isn't.

That doesn't mean code is working perfectly with SQLite, it just means you weren't informed about a possible source of error.

In this case, I'm not even sure what it means to sort by a field you aren't listing in the SELECT columns. How do you know which card.id you are getting a total for?

Since you hadn't specified a sort order I'm guessing it's an ActiveRecord default thing perhaps triggered by your use of first. It's trying to give a default order so that the "first" result has some consistent meaning.

However, your query doesn't make sense to me unless you can only have one card_item for each user_id. The filter is on user_id which if a user can have more than one card_item associated with it means you could get multiple rows returned. That doesn't mesh with the idea of having total_price associated with a card_item rather than a user. Unless I'm missing something.

answered Dec 21, 2013 at 16:12

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

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

3 Comments

atomAltera Over a year ago

OK, 1) Can't I sort by not selected columns? 2) For each card_items I have only one product. This means, I won't have duplications card_item.id 3) I thought SUM function returns only one row. Correct me if I'm wrong

Richard Huxton Over a year ago

1. You can, but what does it mean if you sort by not selected columns? What does each row relate to? 2. You're filtering by user-id - who gives a damn how many products relate to a card-id, we haven't got that far yet. 3. You get one row for each grouping valu . 4.Get a book on SQL and/or relational theory.

Richard Huxton Over a year ago

Check out books by C.J.Date for relational theory and perhaps Joe Celko for some SQL background.

Collectives™ on Stack Overflow

ActiveRecord & PostgreSQL ordering error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related