CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecordarel
Kingsley Simon
Kingsley Simon

Reputation: 2210

convert and optimize SQL Query to Active Record query in Rails 4

How do i re-write this SQL query in Active Record query in rails 

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"

Upvotes: 1

Views: 107

Answers (3)

Mehnaz Bano
Mehnaz Bano

Reputation: 63

If you have direct association between developer_referrals and RealEstateAgentAssignmentStatus model, use:

RealEstateAgentAssignmentStatus
  .includes(:developer_referrals)
  .where(assignment_status: ['Pending Eligibility Check', 'Completed Eligibility Check'], deleted_at: nil)
  .where(developer_referrals: { project_id: 1, developer_staff_id: [266] } )
  .group([:assignment_status, :real_estate_agent_assignment_statuses.rank])
  .order(:rank)
  .pluck(:assignment_status, 'COUNT(developer_referrals.id)')

Otherwise, use joins to combine rows from two tables, as below:

RealEstateAgentAssignmentStatus
  .joins('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(assignment_status: ['Pending Eligibility Check', 'Completed Eligibility Check'], deleted_at: nil)
  .group(['real_estate_agent_assignment_statuses.assignment_status', 'real_estate_agent_assignment_statuses.rank'])
  .order('real_estate_agent_assignment_statuses.rank')
  .pluck('real_estate_agent_assignment_statuses.assignment_status', 'COUNT(developer_referrals.id)')

Upvotes: 0

br3nt
br3nt

Reputation: 9596

With the right scopes and relationships set up you can do almost anything.

First step is to define the relationships and scopes. I'm only guessing what your relationships are, but even if totally different the below code should roughly show how it all works:

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

Then you can build up your query using the scopes.

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),
      )

And then you can do your group/count:

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)

Upvotes: 0

Okomikeruko
Okomikeruko

Reputation: 1183

My SQL is rusty but I think this will get you started.

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

Upvotes: 0

Related Questions