0

I have a rails app which has users who post recipes.People can post comments to the recipes with a rating.I want to sort the list of users(index action) by the average of the ratings received by the recipes posted by them.
I am already sorting the list of recpes by using this: 

@courses = @recipes.joins(:comments).group('recipes.id').order('AVG(comments.rating)').reverse_order

How can I do something similar(or not) for users?
I have tried to do this using joins, group etc by reading the rails guides and apidock but still can't figure this out.

Swapnil Devesh
  • 224
  • 8
  • 19

1 Answers1

0

try this, not sure if it's completely right. Hope it's helpful to you.

User.joins(recipes: [:comments]).group('users.id').order('AVG(comments.rating) DESC')

update

my Models are User, Quiz, Question. The part confusing me a lot is your definition of User. Are the users who posted courses, and the users who comment in the same table or separate table? The relationship I mocked is this, which is I think is almost exactly same as your models:

  1. User has_many quizzes, and user has_many questions.
  2. Quiz has_many questions, and belongs_to user.
  3. Questions belongs_to a user, and belongs_to a quizz.

in this case, if I do @users = User.joins(quizzes: [:questions]).group('users.id').order('AVG(questions.easy_count) DESC'), the result I get is, the result @users is a list of user who owns quizzes ordered by the easy_count(in your case rating) questions belongs to the corresponding quiz.

explanation of the code

User.joins(quizzes: [:questions]) simple gives you all rows of users who has quizzes(in your case should be courses) which has questions(in your case should be comment). consider the below example:

  1. user1 has quiz1
  2. quiz1 has question1 and question2

the result you get from User.joins(quizzes: [:questions]) will return you two duplicated rows of user1(because user1 is related to two questions through quiz1)

then group('users.id') is going to group the result from User.joins(quizzes: [:questions]) by user.id(you want to get user list)

so so far, what you get is very simple, it's a list of users who has quizzes and those quizzes need to have at least one question, I'm pretty sure it is the owner user of the quiz, not the owner user of the questions.

then at the end you sort this list by average easy_count of questions(in your case, rating of comments)

Zhiliang Xing
  • 1,057
  • 1
  • 8
  • 19
  • Doing this groups the comments by the user posting the comment. A comment belongs_to user and course(here user is the person posting the comment). Course has_many comments, User has_many courses.hop this helps – Swapnil Devesh Apr 02 '16 at 20:50
  • can you provide me your schema table? so that i can easily set it up exactly same as you, if you have a seed.rb, it would be wonderful – Zhiliang Xing Apr 02 '16 at 20:58
  • Since you didn't provide me a table, I just mocked a similar model relationships. see the update in my answer – Zhiliang Xing Apr 02 '16 at 21:27
  • the sql generated is `SELECT "users".* FROM "users" INNER JOIN "quizzes" ON "quizzes"."user_id" = "users"."id" INNER JOIN "questions" ON "questions"."quiz_id" = "quizzes"."id" GROUP BY "users"."id"` which also proves that its the user posting quiz(course in your case), not the user posting questions(comment in your case) – Zhiliang Xing Apr 02 '16 at 21:46
  • if you want to validate the result you can run the following code in you console: `sql = %q(SELECT "users".* FROM "users" INNER JOIN "courses" ON "courses"."user_id" = "users"."id" INNER JOIN "comment" ON "comment"."course_id" = "courses"."id" GROUP BY "users"."id" ORDER BY AVG(comments.rating) DESC)`, `ActiveRecord::Base.connection.execute(sql)` – Zhiliang Xing Apr 02 '16 at 21:47
  • thanks for that amazing explanation!the model reationships you created are exactly same as mine. – Swapnil Devesh Apr 02 '16 at 22:07
  • while trying this, i encountered an error `SQLite3::SQLException: ambiguous column name: id: SELECT COUNT(*) AS count_all, id AS id FROM "users" INNER JOIN "courses" ON "courses"."user_id" = "users"."id" INNER JOIN "comments" ON "comments"."course_id" = "courses"."id" GROUP BY "users"."id" ORDER BY AVG(comments.rating) DESC` when i did .empty? on the result of that statement.Same error is there when i try to do count.@ZhiliangTakutoXing – Swapnil Devesh Apr 02 '16 at 22:17
  • what are you trying? can you specify the code here? the sql error messages are very clear, it's saying it doesn't know which table is * and id from, because you are using inner join – Zhiliang Xing Apr 02 '16 at 22:59
  • i guess what you are trying to do is this: `SELECT COUNT(*) AS count_all, users.id AS id FROM users INNER JOIN courses ON courses.user_id = users.id INNER JOIN comments ON comments.course_id = courses.id GROUP BY users.id ORDER BY AVG(comments.rating) DESC` – Zhiliang Xing Apr 02 '16 at 23:05
  • I am trying `@users = User.joins(courses: [:comments]).group(:id).order('AVG(comments.rating) DESC')` and then '@users.empty?'.@ZhiliangTakutoXing – Swapnil Devesh Apr 03 '16 at 06:56
  • Ok, basically what happened for `.empty' is it somehow got considered as part of sql operations and got translated to sql query. so for `.empty`, you can simply do `@users = User.joins(courses: [:comments]).group(:id).order('AVG(comments.rating) DESC')`, and then call `@users.empty?`, if it's now working, call ` User.joins(courses: [:comments]).group(:id).order('AVG(comments.rating) DESC').class` to see if the result is a class of `ActiveRecord::Relation`. – Zhiliang Xing Apr 03 '16 at 19:11
  • and for `.count`, you are simply calling the count method defined [here](http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-count), it's not the count you thought about. instead you can call `.size`. hope it helps – Zhiliang Xing Apr 03 '16 at 19:13
  • look at this post for more information. http://stackoverflow.com/a/18997294/4036030 – Zhiliang Xing Apr 03 '16 at 19:19
  • The way you suggested to try i.e call @users.empty?, that's exactly what i am trying to do and is not working...It is an activerecord relation...so basically what i grasp from that post is can i use size in place of count anywhere and results will be same?btw `@users.size` and `@users.count` both give errors...@ZhiliangTakutoXing – Swapnil Devesh Apr 03 '16 at 19:57
  • Can u show me the error message from calling .size? – Zhiliang Xing Apr 04 '16 at 00:20
  • `ActiveRecord::StatementInvalid: SQLite3::SQLException: ambiguous column name: id: SELECT COUNT(*) AS count_all, id AS id FROM "users" INNER JOIN "courses" ON "courses"."user_id" = "users"."id" INNER JOIN "comments" ON "comments"."course_id" = "courses"."id" GROUP BY "users"."id" ORDER BY AVG(comments.rating) DESC`@ZhiliangTakutoXing – Swapnil Devesh Apr 04 '16 at 01:09
  • how about `User.joins(courses: [:comments]).group('users.id').order('AVG(comments.rating) DESC').count` – Zhiliang Xing Apr 04 '16 at 19:33
  • the above code is going to return you something like this `{3=>6, 2=>7, 1=>3}`, because it counts based on the group. if you simply just want to count the size of the result, use `User.joins(courses: [:comments]).group('users.id').order('AVG(comments.rating) DESC').length` – Zhiliang Xing Apr 04 '16 at 19:35
  • if you think it helped you please accept my answer as solution thanks! – Zhiliang Xing Apr 05 '16 at 16:37
  • "count" and "length" works but the data it returns seems to be incorrect...also when i tried this in the app, i got a "will_paginate" error `undefined method 'total_pages' for #`...this happens in line `<%= will_paginate @users %>`....thanks for help btw... – Swapnil Devesh Apr 05 '16 at 16:47
  • sorry...but the data is correct!!!thanks...only issue now is the "total_pages" error....any idea about that...the result is an ActiveRecord Reation so idk why its not working... – Swapnil Devesh Apr 05 '16 at 17:20
  • I tried to install pagination, but I can catch the error you have, can you give me the exactly code you used in your controller, and the stack trace? – Zhiliang Xing Apr 05 '16 at 17:36