Rails 5中Left Outer Join结合WHERE筛选user_tasks失败问题

Hey there! Let's figure out why your query isn't filtering the user_tasks correctly.

The issue here has two key parts:

1. Ambiguous column reference: When you use where(status: 'approved'), Rails might not know if you're targeting a status column on the users table or the user_tasks table. You need to explicitly specify which table the status field belongs to.
2. Left join behavior quirk: A left_joins keeps all records from the users table, even if they have no matching user_tasks. But adding a where condition on user_tasks.status turns it into an implicit inner join—filtering out any users who don't have at least one approved task (since their user_tasks.status would be NULL, which doesn't match 'approved').

Let's fix this based on your actual needs:

Scenario 1: Count only approved tasks for all users (including those with 0 approved tasks) #

If you need to keep every user in the result set but only tally their approved tasks, use a conditional count instead of a where clause:

def self.leaders
  self.left_joins(:user_tasks)
      .group(:id)
      .order("COUNT(CASE WHEN user_tasks.status = 'approved' THEN 1 END) DESC")
end

This way, users with no approved tasks will show a count of 0 and still appear in the results.

Scenario 2: Only include users who have at least one approved task #

If you don't care about users with no approved tasks, you can either explicitly target the user_tasks table in your where clause or switch to an inner join (more intentional for this use case):

Option 1 (Explicit table in where):

def self.leaders
  self.left_joins(:user_tasks)
      .where(user_tasks: { status: 'approved' })
      .group(:id)
      .order("COUNT(user_tasks.id) DESC")
end

Note: This behaves the same as an inner join, as it filters out users with no matching approved tasks.

Option 2 (Inner join, clearer intent):

def self.leaders
  self.joins(:user_tasks)
      .where(user_tasks: { status: 'approved' })
      .group(:id)
      .order("COUNT(user_tasks.id) DESC")
end

Bonus: Clean up with a reusable scope #

To make this code more maintainable, add a scope to your UserTask model:

# app/models/user_task.rb
class UserTask < ApplicationRecord
  belongs_to :user
  scope :approved, -> { where(status: 'approved') }
end

Then use merge in your User model's method to keep things DRY:

def self.leaders
  self.joins(:user_tasks)
      .merge(UserTask.approved)
      .group(:id)
      .order("COUNT(user_tasks.id) DESC")
end

内容的提问来源于stack exchange，提问作者waynard