Ruby on Rails中SQL查询自定义字段user_average_rating使用异常问题

Hey there! Let's break down why you're running into issues accessing the user_average_rating field in your Rails app, and fix it step by step.

The Root Problem #

Rails ActiveRecord models only automatically recognize columns that exist in your database table. Since user_average_rating is a custom computed alias from your SQL query, Rails doesn't know about this attribute by default—so calling it directly throws an error.

Solutions to Fix This #

1. Define the Custom Attribute in Your User Model

First, you need to tell Rails that this computed field exists, and what type it should be. Add this to your User model:

class User < ApplicationRecord
  # Declare the custom attribute with its data type (float works for your rounded values)
  attribute :user_average_rating, :float
end

This lets Rails automatically handle type conversion (so you get a numeric value instead of a raw string from the SQL result) and gives you a proper getter method for the field.

2. Run Your Query the Rails Way (Optional but Recommended)

Instead of writing raw SQL, you can rebuild the query using Rails' query interface for better maintainability and safety. Here's how:

users_with_ratings = User.left_joins(video_chats: :ratings)
  .select(
    "users.id",
    "ROUND(COALESCE(AVG(CAST(ratings.score AS FLOAT)), 0)::numeric, 1) AS user_average_rating"
  )
  .group("users.id")

If you want to reuse this query, wrap it in a model scope:

class User < ApplicationRecord
  has_many :video_chats
  has_many :ratings, through: :video_chats

  attribute :user_average_rating, :float

  scope :with_average_rating, -> {
    left_joins(video_chats: :ratings)
      .select(
        "users.*", # Include all user columns if needed
        "ROUND(COALESCE(AVG(CAST(ratings.score AS FLOAT)), 0)::numeric, 1) AS user_average_rating"
      )
      .group("users.id")
  }
end

Then use it like this:

User.with_average_rating.each do |user|
  puts "User #{user.id} has an average rating of #{user.user_average_rating}"
end

3. If You Prefer Raw SQL (Using find_by_sql)

If you stick with your original raw SQL query, the attribute declaration in the model is still required to access the field properly. Example:

sql_query = <<-SQL
  SELECT u.id, ROUND(COALESCE(AVG(CAST(r.score AS FLOAT)),0)::numeric, 1) AS user_average_rating 
  FROM users u 
  LEFT JOIN video_chats v ON v.user_id = u.id 
  LEFT JOIN ratings r ON r.video_chat_id = v.id 
  GROUP BY u.id;
SQL

users = User.find_by_sql(sql_query)
users.first.user_average_rating # Now works correctly thanks to the attribute declaration

Quick Checks to Avoid Mistakes #

• Double-check that the alias in your SQL (user_average_rating) matches exactly what you're calling in Rails—typos are a common culprit!
• If you're using pluck instead of fetching model instances, you won't need the attribute declaration (since pluck returns raw values directly), but you'll lose access to other user attributes.

内容的提问来源于stack exchange，提问作者Mateusz Urbański