How do i get instance of a model with highest value from an operation?-CodePudding

I have three models, directors, movies, and ratings. These are their relations: A director has_many movies, a movie belongs_to a director, a movie has_many ratings and a rating belongs_to a movie.

My schema.rb:

ActiveRecord::Schema.define(version: 2022_01_20_101906) do

  create_table "directors", force: :cascade do |t|
    t.string "first_name"
    t.date "date_of_birth"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "movies", force: :cascade do |t|
    t.string "name"
    t.date "release_date"
    t.string "description"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.integer "director_id"
    t.index ["director_id"], name: "index_movies_on_director_id"
  end

  create_table "ratings", force: :cascade do |t|
    t.integer "value"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.integer "movie_id"
    t.index ["movie_id"], name: "index_ratings_on_movie_id"
  end

  create_table "users", force: :cascade do |t|
    t.string "email", default: "", null: false
    t.string "encrypted_password", default: "", null: false
    t.string "reset_password_token"
    t.datetime "reset_password_sent_at", precision: 6
    t.datetime "remember_created_at", precision: 6
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
  end

  add_foreign_key "movies", "directors"
  add_foreign_key "ratings", "movies"
end

I'm currently trying to display a director's best-rated movie to display on a _director.html.erb patrial, so I tried this code to get the highest-rated movie value:

<% array = [] %>
<% rat_val = 0 %>
<% result = 0 %>
<% director.movies.each do |movie| %>
   <% if movie.ratings.count > 0 %>
      <% director.movies.each do |movie| %>
         <% rat_val = number_with_precision(movie.ratings.average(:value), precision: 2) %>
         <% array << rat_val %>
         <% result = array.max %>
      <% end %>
        <%= result %>
      <% break %>
   <% end %>
<% end %>

But I'm struggling with what to do next to use this value to get the director's best-rated movie name to display.

CodePudding user response：

One way to obtain the best-rated movie from each director would be selecting the average rating values for each director movie, ordering by that calculation and limiting the rows returned to 1.

This would be very complex to achieve in ActiveRecord. Having the whole select statement to get the movie_name as a string, so here's just the SQL version:

SELECT
  d.*,
  (
    SELECT t.name
    FROM (
      SELECT name, (SELECT AVG(value) FROM ratings WHERE movie_id = movies.id) avgval
      FROM movies
      WHERE director_id = d.id
      ORDER BY avgval DESC
      LIMIT 1
    ) t
  ) movie_name
FROM directors d;

With this you don't need to manually iterate and counting raitings, you have every director column accessible plus the movie_name.

CodePudding user response：

I think the following would be clean, and sticks with using ActiveRecord:

director.movies.order(rating: :desc).first