CODEWITHSUNDEEP
ruby-on-railsjoinrails-activerecord
Kalsan
Kalsan

Reputation: 1039

Rails 6.1: Create a scope containing .first or .last through a has_many relation

In Rails 6.1, assuming two models:

I want to run the query:

Give me all PeriodicJobs which last (highest ID) execution has state succeed.

A potential solution would be raw SQL with a subquery, as pointed out in this other Stackoverflow question: Ransack searching for instances with specific value in last of has_many associations

However, this seems overly complicated code for such a simple question in English. Given the power of Rails, I'd have expected to see something like:

PeriodicJob.joins(:executions).where(cool_trick_im_yet_unaware_of_to_get_last_ordered_by_id_execution: { state: 'succeeded' }

Does such a thing exist in Rails and how would it be applied to this example?

Upvotes: 0

Views: 254

Answers (2)

Int'l Man Of Coding Mystery
Int'l Man Of Coding Mystery

Reputation: 1722

If you are using regular numeric ids, you can add a condition to the relationship. This will set a custom relationship on the model that will return the latest execution that is succeeded. THe has one macro will limit the 'collection' to one.

has_one :current_succeeded_execution, -> { where(state: "succeeded").reorder(id: :desc) }, class_name: 'Execution'

If you're using uuid, I would have set a default scope on those mdoels to order by created at asc to ensure the first one is always the oldest, ie the first one created. So then you could reorder to desc to get latest one

has_one :current_succeeded_execution, -> { where(state: "succeeded").reorder(created_at: :desc) }, class_name: 'Execution'

Upvotes: 0

max
max

Reputation: 102001

One way of optimizing this for reading would be to setup a separate foreign key column and association as a "shortcut" to the latest execution:

class AddLatestExecutionToProducts < ActiveRecord::Migration[6.0]
  def change
    add_reference :latest_execution, :execution
  end
end

class PeriodicJob < ApplicationRecord
  has_many :executions, 
    after_add: :set_latest_execution
  belongs_to :latest_execution, 
    optional: true,
    class_name: 'Execution'

  private

  def set_latest_execution(execution)
    update_attribute(:latest_execution_id, execution.id)
  end
end

This lets you do PeriodicJob.eager_load(:latest_execution) and avoid both a N+1 query and loading all the records off the executions table. This is especially important if you have a lot of executions per peroidic job.

The cost is that it requires an extra write query every time an execution is created.

If you want to limit this to just the latest success/failure you could add two columns:

class AddLatestExecutionToProducts < ActiveRecord::Migration[6.0]
  def change
    add_reference :latest_successful_execution, :execution
    add_reference :latest_failed_execution, :execution
  end
end

class Execution ​< ApplicationRecord
  enum state: {
    ​succeeded: 'succeeded',
    ​failed:    'failed'
 ​ }
end

class PeriodicJob < ApplicationRecord
  has_many :executions, 
    after_add: :set_latest_execution
  belongs_to :latest_successful_execution, 
    optional: true,
    class_name: 'Execution'
  belongs_to :latest_failed_execution, 
    optional: true,
    class_name: 'Execution'

  private

  def set_latest_execution(execution)
    if execution.succeeded?
      update_attribute(:latest_successful_execution_id, execution.id)
    else
      update_attribute(:latest_failed_execution_id, execution.id)
    end
  end
end

Upvotes: 1

Related Questions