CODEWITHSUNDEEP
ruby-on-railsruby
Robert
Robert

Reputation: 493

How to average a column in a join table?

For each rating_id from items_ratings from an item, get the

  1. name
  2. average of values

My table setup:

item: id url name

rating: id name

items_rating: id item_id rating_id value

I'm started to solve it with:

(Rating.first.id..Rating.last.id).each do |rating_id|
   @item.items_ratings ... 
end

but now I seem stuck as where(rating_id: rating_id) and average(:value) don't work together from here.

input:

@item.items_ratings.where(rating_id: 1)

output:

#<ActiveRecord::AssociationRelation
[#<ItemsRating item_id: 5, rating_id: 1, user_id: 1, value: "3">, #<ItemsRating item_id: 5, rating_id: 1, user_id: 1, value: "3">, 
#<ItemsRating item_id: 5, rating_id: 1, user_id: 1, value: "3">, #<ItemsRating item_id: 5, rating_id: 1, user_id: 1, value: "4">, #<ItemsRating item_id: 5, rating_id: 1, user_id: 1, value: "5">]>

input:

@item.items_ratings.where(rating_id: 1).average(:value)

output:

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  function avg(character varying) does not exist
LINE 1: SELECT AVG("items_ratings"."value") FROM "items_ratings" WHE...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Upvotes: 1

Views: 773

Answers (3)

Uday kumar das
Uday kumar das

Reputation: 1613

Use this code:

@item.items_ratings.where(rating_id: 1).average('value')

Upvotes: 0

Zakhar Antoshchenko
Zakhar Antoshchenko

Reputation: 147

It happened because your 'value' field has a 'string' type. If value will use only as integer, you can change column type to integer or other numeric type, dependency by your database. If you wanna work with string, then if this values will be in integer range, your code can be like: @item.items_ratings.where(rating_id: 1).average('CAST(value AS INT)')

Upvotes: 3

Robert
Robert

Reputation: 493

Argument type for value is a string, change that to integer

Upvotes: 0

Related Questions