在 Rails 4 中将 SQL 查询转换并优化为活动记录查询

Question

如何在 rails 中的活动记录查询中重写此 SQL 查询

sqlQuery = "SELECT real_estate_agent_assignment_statuses.assignment_status, 
COUNT(developer_referrals.id) AS rea_count FROM 
real_estate_agent_assignment_statuses LEFT OUTER JOIN developer_referrals ON 
developer_referrals.real_estate_agent_assignment_status_id = 
real_estate_agent_assignment_statuses.id AND developer_referrals.project_id =
 1 AND developer_referrals.developer_staff_id IN (266) WHERE 
real_estate_agent_assignment_statuses.assignment_status IN ('Pending 
Eligibility Check', 'Completed Eligibility Check') AND 
real_estate_agent_assignment_statuses.deleted_at IS NULL GROUP BY 
real_estate_agent_assignment_statuses.assignment_status, 
real_estate_agent_assignment_statuses.rank ORDER BY 
real_estate_agent_assignment_statuses.rank ASC"

Answer 1

我的SQL生锈了，但我认为这会让你入门。

sql_query1 = RealEstateAgentAssignmentStatus.joins(:developer_referrals)
                                            .where(:assignment_status => ['Pending Eligibility Check', 'Completed Eligibility Check'])
                                            .group(:assignment_status)
                                            .order(:rank)
                                            .all

http://apidock.com/rails/ActiveRecord/QueryMethods

Answer 2

通过设置正确的范围和关系，您几乎可以做任何事情。

第一步是定义关系和范围。 我只是猜测你的关系是什么，但即使完全不同，下面的代码也应该大致显示它是如何工作的：

class DeveloperReferral < ActiveRecord::Base
  belongs_to :project
  belongs_to :staff_member
  scope :with_project, ->(project) {
    # merging allows you to define conditions on the join
    joins(:project).merge( Project.where(id: project) )
  }
  scope :with_staff_member, ->(staff_member) {
    # merging allows you to define conditions on the join
    joins(:staff_member).merge( StaffMember.where(id: staff_member) )
  }
end
class RealEstateAgentAssignmentStatus < ActiveRecord::Base
  has_many :developer_referrals
  scope :with_status, ->(status) { where(status: status) }
  scope :not_deleted, -> { where(deleted_at: nil) }
  scope :with_project, ->(project) {
    # merging allows you to define conditions on the join
    joins(:developer_referrals).merge(
        DeveloperReferral.with_project(project)
      )
  }
  scope :with_staff_member, ->(staff_member) {
    # merging allows you to define conditions on the join
    joins(:developer_referrals).merge(
        DeveloperReferral.with_staff_member(staff_member)
      )
  }
end

然后，可以使用范围构建查询。

project = Project.find(1)
staff_members = project.staff_members
statuses =
  RealEstateAgentAssignmentStatus
    .with_project(project)
    .with_staff_member(staff_members)
    .with_status([
        'Pending Eligibility Check',
        'Completed Eligibility Check',
      ])
    .not_deleted
    .order(
        # we use `arel_table` so that SQL uses the namespaced column name
        RealEstateAgentAssignmentStatus.arel_table(:rank),
      )

然后你可以做你的组/计数：

status_counts =
  statuses
    .group(
        # we use `arel_table` so that SQL uses the namespaced column name
        RealEstateAgentAssignmentStatus.arel_table(:assignment_status),
        RealEstateAgentAssignmentStatus.arel_table(:rank),
      )
    .count(:id)