Ruby on Rails / Active Record sorta self-referencial query?-CodePudding

I have a model called Stack that is a list of tasks to be done by a background worker. Up until now it was a single background worker doing the work but as time has gone on it has gotten a little more bloated, a little slower, and the Stack is... well stacking up.

For purposes of this question assume this is the model.

class Stack < ApplicationRecord
  enum state: { queued: 0, processing: 1, third_state: 2, fourth_state: 3 }
  validates :state, :object_name, :object_id, presence: true
end

So Stack has a state, a object_name and a object_id that it will be working on.

In our database we have the following Stack entries;

{ id: 1, state: :processing, object_name: "Car", object_id: 1 }
{ id: 2, state: :queued, object_name: "Car", object_id: 1 }
{ id: 6, state: :queued, object_name: "Car", object_id: 2 }
{ id: 7, state: :queued, object_name: "Truck", object_id: 2 }

I want to be able to get id:6 above as the next "queued" because in theory, another worker is already working on id:1 and we don't want it to pull id:2 because the subject of the task is the same object_name and object_id as id:1 which is currently being processed.

So, how do I get the next thing in the stack that is queued, but not if its object is being processed somewhere else in the stack?

I feel like I should be able to do something like;

Stack.with_state(:queued).where.not("object_id = ? and object_name = ?", 
Stack.without_state(:queued).pluck(:object_id, :object_name).uniq ).to_sql

But that wouldn't work because the "object_id = ? and object_name = ?" expects two arguments where as the query I put in gives an array of arrays with two elements.

Thoughts?

CodePudding user response：

If I understand correctly you want to find:

The first stack (ordered by id) in a "queued" state for which no other stacks, with the same object_name and object_id, not in a "queued" state exist.

If so the following should work for you:

stack_tbl = Arel::Table.new(Stack.table_name,as: 's2')

Stack
  .where(state: :queued)
  .where.not(
    stack_tbl
      .project(stack_tbl[Arel.star])
      .where(
        stack_tbl[:object_id].eq(Stack.arel_tbl[:object_id])
        .and(stack_tbl[:object_name].eq(Stack.arel_table[:object_name]))
        .and(stack_tbl[:state].not_eq(Stack.states[:queued]))
      ).exists
  )
 .order(:id)
 .limit(1)

This should result in the following SQL:

SELECT 
  stacks.*
FROM 
  stacks 
WHERE 
  stacks.state = 0 AND
  NOT EXISTS (
   SELECT 
     s2.* 
   FROM stacks s2 
   WHERE 
     s2.object_id = stacks.object_id 
     AND s2.object_name = stacks.object_name
     AND s2.state <> 0
)
ORDER BY
  stacks.id
LIMIT 1