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

Question

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.

Answer 1

I managed to get it to work via this perhaps over-complicated loop:

  <% ratings_all = [] %>
  <% ratings_array = [] %>
  <% rating_avg = [] %>
  <% movies_rating = [] %>
  <% best_rating = 0 %>

  <% director.movies.each do |movie| %>

    <% if movie.ratings.count > 0 %>

      <% director.movies.each do |movie| %>

        <% ratings_all = movie.ratings.to_a %>
        <% ratings_array = ratings_all.map { |v| v[:value] } %>
        <% ratings_avg = (ratings_array.sum.to_f / ratings_array.count).round(2).to_f %>
        <% movies_rating << ratings_avg %>
        <% best_rating = movies_rating.max %>

      <% end %>

        <% if movie.ratings.average(:value).round(2) == best_rating %>
         <%= link_to movie.name, movie_path(movie) %>
         (<%= best_rating %>/5.0)

       <% end %>

    <% end %>

  <% end %>

but now it displays the name of the best rated movie on a directors and its rating, which is what I aimed to achieve.

Answer 2

A better performing alternative to a subquery is to use a lateral join:

SELECT "directors".*, 
       "best_rated"."average_rating" AS rating 
FROM "directors" 
JOIN LATERAL (
  SELECT AVG("ratings"."value") AS average_rating 
  FROM "movies" 
  INNER JOIN "ratings" ON "ratings"."movie_id" = "movies"."id" 
  WHERE "movies"."director_id" = "directors"."id" 
  GROUP BY "movies"."id" 
  ORDER BY "average_rating" DESC LIMIT 1
) best_rated ON true

This is somewhat like a foreach loop that selects a single row off the movies table which you can reference in the select clause. Writing the query with Arel/ActiveRecord is actually almost longer then the SQL string but is somewhat more portable.

class Director < ApplicationRecord
  has_many :movies
  has_many :ratings, through: :movies

  # Select directors and the average ranking of their top ranking movie
  def self.with_top_rated_movie
    # this is the subquery for the lateral join
    lateral = Movie
                .select(
                  Rating.arel_table[:value].average.as('average_rating')
                  # You can add any additional columns you want from the movie here
                )
                .joins(:ratings)
                .group(:id)
                .order(average_rating: :desc)
                # WHERE movies.director_id = directors.id  This is our lateral reference
                .where(Movie.arel_table[:director_id].eq(arel_table[:id]))
                .limit(1)
    # This is the alias used by the lateral join
    best_rated = Movie.arel_table.alias('best_rated')
    select(
      arel_table[Arel.star],
      best_rated[:average_rating].as('rating')
      # You can add any additional columns you want from the movie here
    ).joins(
      "JOIN LATERAL (#{lateral.to_sql}) best_rated ON true"
    )
  end           
end

Answer 3

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

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

Answer 4

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.