Hello! There is an application on rails, in it, in particular, there is a user table (User model) and a feedback table (Reviews model). The Reviews table has a rating field. One-to-many relationship is configured, i.e. The user may have a lot of feedback. You need to configure the query so that in addition to the required fields from the user table, the field containing the average user rating from the Reviews table is also returned:

@search = User.select("id, name, username").joins(** средний рейтинг **) @search[0].rating # возвращено некоторое значение

I note that this field is needed to further render it in json, i.e. callback option

 after_find do |user| user.rating = user.reviews.count == 0 ? 0 : user.reviews.sum(:rating)/(user.reviews.count) end attr_accessor :rating

does not work. Thank you all in advance!

    1 answer 1

    It makes sense to use the existing AVG function in SQL, rather than trying to calculate everything manually. JOIN needs a left to select users without feedback.

     User.select( 'users.id, users.name, users.username, COALESCE(AVG(reviews.rating),0) AS avg_rating' ).joins('LEFT JOIN reviews ON reviews.user_id = users.id') .group(:id)

    PS: any callbacks make the developer’s life a lot harder and they strive to shoot him in the leg all the time, don’t use them for any reason

    UPDATE: Using alias for where will not work by design. Try this:

     User.select( 'users.id, users.name, users.username, COALESCE(AVG(reviews.rating),0) AS avg_rating' ).joins('LEFT JOIN reviews ON reviews.user_id = users.id') .where('COALESCE(AVG(reviews.rating),0) >= ?', params[:min_rating].to_i), .group(:id)

    Or read about WITH and write already the entire query in pure sql :)

    • Thanks for the implementation, they would also suggest how then in this request to filter users by the minimum rating. - Sergey Valerievich
    • If my answer helped you, please accept it. I mean, do you need to sort them by avg_rating ? - Vasilisa
    • Yes. Otherwise I would not bother: I need to add something like where ('avg_rating> =: min_rating', min_rating: params [: min_rating] .to_i), but this piece does not work - Sergey Valerievich
    • @ Sergey Valerievich, updated her answer - Vasilisa
    • In any case, thanks, I went to remember SQL) - Sergey Valerievich