CODEWITHSUNDEEP
ruby-on-rails
Zephyr4434
Zephyr4434

Reputation: 796

How to sort through an associated attribute on two levels at once?

This is a simple ruby question I believe. In my app, I have Product model that has_many Reviews. Each Review has an attribute of an "overall" rating which is an integer.

What I want to do is display the top ten Products based on the average of their overall ratings. I've already gotten this to work, BUT, I also want to sort Products that have the SAME overall rating by a secondary aggregate attribute, which would be how MANY reviews that Product has. Right now, if I have 3 products with the same average overall rating, they seem to be displayed in random order.

So far my code is:

Controller

@best = Product.has_reviews.get_best_products(10)

Product Model

scope :has_reviews, joins{reviews.outer}.where{reviews.id != nil}

def self.get_best_products(number)
  sorted = self.uniq
  sorted = sorted.sort { |x, y| y.reviews.average("overall").to_f <=> x.reviews.average("overall").to_f }
  sorted.first(number)
end

I've tried this for my model code:

def self.get_best_products(number)
  sorted = self.uniq.sort! { |x, y| x.reviews.count.to_f <=> y.reviews.count.to_f }
  sorted = sorted.sort { |x, y| y.reviews.average("overall").to_f <=> x.reviews.average("overall").to_f }
  sorted.first(number)
end

...but it does not do what I want it to do. I am just iterating through the @best array using each in my view.

---UPDATE

OK now I am trying this:

Controller:

@best = Product.get_best_products(6)

Model:

def self.get_best_products(number)
  self.joins{reviews}.order{'AVG(reviews.overall), COUNT(reviews)'}.limit(number)
end

But I am getting this error:

PGError: ERROR:  column "products.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  "products".* FROM "products" INNER JOIN "reviews" ...

I am using the Squeel gem btw to avoid having direct SQL code in the model.

----UPDATE 2

Now I added the 'group' part to my method but I am still getting an error:

def self.get_best_products(number)
  self.joins{reviews}.group('product.id').order{'AVG(reviews.overall), COUNT(reviews)'}.limit(number)
end

I get this error:

PGError: ERROR:  missing FROM-clause entry for table "product"
LINE 1: ...eviews"."product_id" = "products"."id" GROUP BY product.i...

Upvotes: 2

Views: 235

Answers (2)

rmagnum2002
rmagnum2002

Reputation: 11421

If I got it right here is my idea of how I would do it:

As products has many reviews and reviews has an overall attribute I would add a reviews_counter column to the products table that will increment with each added review, this way you'll be able to gain a little more db performance as you don't have to count all the products reviews to get the most reviewed one.

Now you'll get the products ordered by reviews_counter:

@best_products = Products.order("reviews_counter desc")

and next you'll get the reviews for each product ordered by overall:

<% for prod in @best_products %>
  <%= prod.reviews.order("overall desc") %> # can do all this or more in helper
<% end %>

also ordering this way, if you have 3 reviews with the same overall you can one more order() statement and sort it by name or id or whatever you like so they don't display in random order.

This is just my idea of how I would do it, I worked recently on an app that required something similar and we just added a counter_field to our model, it's not illegal to do so :)

p.s. it's not very clear for me how many records you would want to display for each so you'll just need to add .limit(5) for exemple to get only the first 5 reviews of a product.

Upvotes: 0

Arjan
Arjan

Reputation: 6274

product.rb

scope :best_products, (lambda do |number|
  joins(:reviews).order('AVG(reviews.overall), COUNT(reviews)').limit(number)
)

products_controller.rb

Product.best_products(10)

This makes sure everything happens in the database, so you won't get records you don't need.

Upvotes: 0

Related Questions