ruby on rails - Prevent N+1 queries with custom select in association - Stack Overflow

admin管理员组

文章数量:1289893

I include an association with additional attribute projects_count. Even with the includes method, I still got N+1 queries for categories. I guess it's because of the dynamic argument in categories_with_projects_count but I don't know how to achieve it without dynamic argument

class User < ApplicationRecord
  has_many :categories_with_projects_count, ->(user) { with_projects_count(user.id) }, through: :user_profile, source: :categories
end

class UserProfile < ApplicationRecord
  has_and_belongs_to_many :categories
end

class Category < ApplicationRecord
  scope :with_projects_count, lambda { |user_id|
    select('categories.*, COALESCE(won_projects_stats.count, 0) AS projects_count')
      .joins(<<~SQL.squish)
        LEFT JOIN (
          SELECT cat.parent_id AS parent_category_id, cat.id AS category_id, COUNT(*) AS count
          FROM projects
          INNER JOIN categories_projects ON (categories_projects.project_id = projects.id)
          INNER JOIN categories cat ON (categories_projects.category_id = cat.id)
          INNER JOIN offers ON (projects.id = offers.project_id)
          WHERE offers.state = 'accepted' AND offers.user_id = #{user_id}
          GROUP BY 1, 2
        ) AS won_projects_stats ON (
          categories.id = won_projects_stats.parent_category_id
          OR categories.id = won_projects_stats.category_id
        )
      SQL
  }
end

@users = User.includes(categories_with_projects_count: [:users_seo_tagging_page, :parent, { logo_attachment: :blob }])

@user.each do |u|
  u.categories_with_projects_count.each do |c|
    ...
  end
end

本文标签： ruby on railsPrevent N1 queries with custom select in associationStack Overflow