State dependencies between two or model - design / retrieval technique

project.rb

def self.filter_by_job_status(job_status)
  projects = where(nil)

  if job_status == :completed
    match_all = <<~EOD
      EXISTS ( SELECT 1 FROM jobs WHERE jobs.project_id = projects.id ) AND
      NOT EXISTS ( SELECT 1 from jobs WHERE jobs.project_id = projects.id AND jobs.status NOT IN (?) )
    EOD
    return projects.where([match_all, job_status])
  end

  projects.where(['EXISTS ( SELECT 1 FROM jobs WHERE jobs.project_id = projects.id AND jobs.status = ? )', job_status])
end

Pure Ruby variant:

def self.filter_projects_by_job_status(job_status)
  projects = where(nil).order('id DESC')
  return projects if job_status.blank?

  projects = projects.includes(:jobs)
  projects.select(&:"job_#{job_status}?")
end

def job_completed?
  return false if jobs.empty?
  jobs.index { |job| job.status == 'cancelled' || job.status == 'created' }.nil?
end

def job_cancelled?
  !jobs.index { |job| job.status == 'cancelled' }.nil?
end

def job_created?
  !jobs.index { |job| job.status == 'created' }.nil?
end

This problem happens a lot for example in Spree commerce order state and shipment state depend on each other.
When an order is first created, the order sets status to shipments. when the shipment state changes to ship, the order should be aware of all shipment states are shipped then the order state should be shipped.

Spree also stores shipment_state as a calculated field to avoid joining with the shipments table.