CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4
bmichotte
bmichotte

Reputation: 610

Ruby complex query elegant solution

in my rails 4 project, I have the following tables enter image description here

In this SO question, I searched for a SQL query to fetch the projects with the actual project status id = XX. By actual, I mean the one with the max(created_at).

I got an answer for my query which is

select p.* from projects p
     left join projects_status_projects psp on (psp.project_id = p.id)
     where created_at = (select max(created_at) from projects_status_projects 
           where project_id = p.id)    
     and project_status_id = XX

My models are defined

class Project < ActiveRecord::Base
   has_many :projects_status_projects
   has_many :projects_statuses, :through => :projects_status_projects
end

class ProjectStatus < ActiveRecord::Base
   has_many :projects_status_projects
   has_many :projects, :through => :projects_status_projects
end

class ProjectsStatusType < ActiveRecord::Base
   belongs_to :project
   belongs_to :project_status
end

In my Project model, I have the following method

def self.with_status(status)
   joins(:projects_status_projects)
       .where('projects_status_projects.created_at = (select max(created_at) from 
                    projects_status_projects where project_id = p.id)')
       .where('projects_status_projects.project_status_id = ?', status)
end

While the query is correct, the received results are well filtered, I find this solution terrible and not elegant at all.

Is there any way to get the same result with scopes ?

Thanks for your help.

Upvotes: 3

Views: 157

Answers (3)

T_Dnzt
T_Dnzt

Reputation: 244

What do you think of

scope :with_status, -> (status) { 
   ProjectsStatusType.where(:project_status_id, status).order(:created_at).last.project 
}

EDIT based on comments :

As sockmonk said, scopes should be chainable. Here is a cleaner way to do it, which also fix the problem if no project is found. 

# ProjectStatusType model
scope :with_ordered_status, -> (status) { 
   where(:project_status_id, status).order(:created_at)
}

# Project model
def self.find_by_status(status)
  project_statuses = ProjectsStatusType.with_ordered_status(status)
  project_statuses.any? ? project_statuses.last.project : nil
end

Upvotes: 1

sockmonk
sockmonk

Reputation: 4255

scope :with_status, ->(status = "default_status") {
  joins(:projects_status_projects)
  .where('projects_status_projects.project_status_id = ?', status)
  .order("projects_status_projects.created_at DESC")    
}

When you call it, you'll want to tack a '.first' to the end of it; can't include the .first in the scope itself, as that would make it unchainable.

Upvotes: 0

Jakub Kuchar
Jakub Kuchar

Reputation: 1665

how about?

scope :with_status, ->(status = "default_status") {
    joins(:projects_status_projects).
    where('projects_status_projects.project_status_id = ?', status).
    order("projects_status_projects.created_at DESC").first
  }

Upvotes: 0

Related Questions