How to average product review scores using activerecord and POSTGRES

Question

Let's say I have a table with 100 product reviews. Each review has an "id", "product_id", and "score". Now, what I want to do is present the average score for each product... So is there a way to query the DB using Rails to do this? I'm sure there must be, but I can't figure it out. I would like the following to occur:

Toyota Corolla 70%
Toyota Camry 78%
Toyota Avalon 80%

.. obviously based on multiple reviews for each that are averaged, but presented in the above fashion.

UPDATE:

For those interested in the solution

This is in the controller:

@ordered_hash = Review.group('aidmodel_id').average('score')
@keys = @ordered_hash.keys
@reviews = Review.where(:aidmodel_id=>@keys).uniq_by {|x| x.aidmodel_id}

This is in the view:

<% @reviews.each do |review| %>

    <%= review.aidmodel.id %>
    <%= @ordered_hash[review.aidmodel_id] %>

  <% end %>

The @ordered_hash[review.aidmodel_id] line provides the averaged score for the aidmodel with the desired ID.

score 6 · Accepted Answer · answered Mar 17 '12 at 21:43

6

You can mix average and group to do something like

MyClass.group('product_id').average('score')
# => {product_id1 => average1, product_id2 => average2, ... }

answered Mar 17 '12 at 21:43

Baldrick

23,882
6
74
79

Thanks Baldrick. Just one problem for me. Running in Rails console I get the error: ActiveRecord::StatementInvalid: PG::Error: ERROR: column "reviews.created_at" must appear in the GROUP BY clause or be used in an aggregate function. Any idea what might be causing this? – Abram Mar 17 '12 at 21:46
I simply ran the following in console (where aidmodel_id is equivalent to product_id in my case): Review.group('aidmodel_id').average('score') – Abram Mar 17 '12 at 21:48
I don't know why you have this error. I've tested on one of my model, it gave me the following SQL request `Match.group('championship_id').average('date')` => `(30.0ms) SELECT AVG("matches"."date") AS average_date, championship_id AS championship_id FROM "matches" GROUP BY championship_id` – Baldrick Mar 17 '12 at 21:52
Ah, yes, this is the problem. Postgres is a bit more strict... There are a few posts on SO related to this issue with Postgres, but the queries are a bit different than mine... so I still can't figure it out. Thanks anyway, I will upvote your answer, but will wait for one relevant to Postgres for the tick =) – Abram Mar 17 '12 at 22:02
1

@Abram: That should work fine with PostgreSQL, what exactly are you trying? As an aside, there's also `Model.average(:x, :group => :y)`. – mu is too short Mar 17 '12 at 22:11
Mu, I tried this again using your syntax... same error: SELECT AVG("reviews"."score") AS average_score, aidmodel_id AS aidmodel_id FROM "reviews" GROUP BY aidmodel_id ORDER BY reviews.created_at DESC ActiveRecord::StatementInvalid: PG::Error: ERROR: column "reviews.created_at" must appear in the GROUP BY clause or be used in an aggregate function – Abram Mar 17 '12 at 22:32
@Abram The `ORDER_BY` is the cause the problem. Did you add it manually to the request, or maybe you have a scope that adds it... – Baldrick Mar 17 '12 at 22:52
Yes, I forgot I added a default order scope inside of my model. Thanks all good now. – Abram Mar 17 '12 at 23:52

score 2 · Answer 2 · edited May 23 '17 at 10:28

2

You need to change the query as

MyClass.select('product_id').group('product_id').average('score')

have a look at the question.

Update:

Try MyClass.select('DISTINCT(product_id)').group('product_id').average('score')

Look here group by in postgres

edited May 23 '17 at 10:28

Community

1
1

answered Mar 17 '12 at 22:22

asitmoharna

1,484
11
15

Unfortunately I am seeing the same error: SELECT AVG("reviews"."score") AS average_score, aidmodel_id AS aidmodel_id FROM "reviews" GROUP BY aidmodel_id ORDER BY reviews.created_at DESC ActiveRecord::StatementInvalid: PG::Error: ERROR: column "reviews.created_at" must appear in the GROUP BY clause or be used in an aggregate function – Abram Mar 17 '12 at 22:30
Your code does work... so I am upvoting it, but Baldrick has been working with me for a while, and he resolved my problem .. it was a default scope in my Review model. Thanks! – Abram Mar 17 '12 at 23:52

How to average product review scores using activerecord and POSTGRES

2 Answers2

Linked