ruby-on-rails - 修复 Rails 中的 n+1 查询

Question

我有图表：https ://app.diagrams.net/#LVenjob 在我的 Rails 应用程序中想要创建最喜欢的工作功能：

控制器：

#favorite_jobs_controller.rb
class FavoriteJobsController < ApplicationController
  before_action :logged_in_user
  before_action :load_job, only: %i[create destroy]

  def create
    #code
  end

  def destroy
    #code
  end

  private

  def load_job
    #load the job
  end
end

#jobs_controller.rb
class JobsController < ApplicationController
  before_action :history, only: :show

  def index
    if job_params.present?
      search
    else
      @jobs = Job.sort_by_date(page: params[:page], per_page: Job::JOB_PER_PAGE)
    end
  end

  def show
    @job = Job.find_by(id: params[:id])
    render partial: 'shared/job_not_found' if @job.nil?
  end
end

模型

#job.rb
LATEST_JOBS_LIMIT = 5
  JOB_PER_PAGE = 20

  has_and_belongs_to_many :industries
  has_and_belongs_to_many :cities
  belongs_to :company
  has_many :apply_jobs, dependent: :destroy
  has_many :favorite_jobs, dependent: :destroy
  has_many :history_job, dependent: :destroy

  def self.sort_by_date(page: 1, per_page: 1)
    includes(:favorite_jobs, :cities, :cities_jobs, :company).order(created_at: :desc).page(page).per(per_page).references(:cities)
  end

#user.rb
class User < ApplicationRecord
  has_many :apply_jobs, dependent: :destroy
  has_many :favorite_jobs, dependent: :destroy
  has_many :history_jobs, dependent: :destroy


  def favorite?(job)
    favorite_jobs.exists?(job_id: job.id)
  end
end

#favorite_job.rb
class FavoriteJob < ApplicationRecord
  belongs_to :job
  belongs_to :user
end

鉴于：

#index.html.slim
#jobs index
.container
  .container
  - @jobs.each do |job|
       - if user_signed_in? && current_user.favorite?(job)
          = render 'shared/unfavorite', job_id: job.id
       - else
          = render 'shared/favorite', job_id: job.id

如果用户没有收藏此工作或未登录，这将呈现“应用”按钮

导轨控制台结果

↳ app/views/jobs/index.html.slim:10
  User Load (1.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 ORDER BY `users`.`id` ASC LIMIT 1
  ↳ app/views/jobs/index.html.slim:20
  FavoriteJob Exists? (0.9ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 244 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 107)
  FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 240 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
  FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 237 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
  FavoriteJob Exists? (0.9ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 234 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.4ms | Allocations: 108)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 233 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 229 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 228 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 227 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
  FavoriteJob Exists? (0.9ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 224 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.6ms | Allocations: 116)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 220 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
  FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 212 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 210 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.5ms | Allocations: 116)
  FavoriteJob Exists? (1.1ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 208 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 207 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
  FavoriteJob Exists? (1.2ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 206 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
  FavoriteJob Exists? (1.1ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 201 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.4ms | Allocations: 108)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 200 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.4ms | Allocations: 108)
  FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 199 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
  FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 197 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
  Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
  FavoriteJob Exists? (1.2ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 195 LIMIT 1
  ↳ app/models/user.rb:30:in `favorite?'
.......  

我尝试了 include(:favorite_jobs) 之类的，但它不起作用请指导我修复工作索引中的 n+1 个查询

标签： ruby-on-railsruby

Answer 1

An alternative way of solving the problem is by selecting a subquery:

class Job < ApplicationRecord
  has_many :favorite_jobs

  def self.with_favorite_status(user)
    select(
      "jobs.*",
      FavoriteJob.select(1)
                 .where(user_id: user.id)
                 .where(FavoriteJob.arel_table[:job_id].eq(arel_table[:id]))
                 .arel
                 .exists
                 .as("favorited")
    )
  end

  def favorited?
    favorited
  end
end

This selects favorited along with the other columns from the table:

SELECT 
  jobs.*, 
  EXISTS (
    SELECT 1 FROM "favorite_jobs" WHERE "favorite_jobs"."user_id" = ? AND "favorite_jobs"."job_id" = "jobs"."id"
  ) AS favorited 
FROM "jobs"

You would then build your other scopes off this one:

class JobsController < ApplicationController
  before_action :history, only: :show

  def index
    @jobs = if user_signed_in?
       Job.with_favorite_status(current_user)
    else
       Job.all
    end

    # ...
  end

  def show
    @job = Job.find(params[:id])
    render partial: 'shared/job_not_found' if @job.nil?
  end
end

On Postgres you can optimize this further by using a lateral join instead of a subquery.

The main advantage of this approach is that it does not load all the rows associatied rows off the favorite_jobs table which will slow your app down or even crash it if jobs have a lot of favorites.