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

Another way to write this is to use GROUP_CONCAT an approx equivalent to array_agg in POSTGRESQL

SELECT Distinct(projects.id)
FROM projects 
INNER JOIN 
(  
	SELECT 
		projects.id AS project_id,
		GROUP_CONCAT( IF (job_state = 'delivered', job_state, NULL) SEPARATOR ',') as completed,
		GROUP_CONCAT( job_state SEPARATOR ',') as all_statuses
		
	FROM projects 
	INNER JOIN jobs ON projects.id = jobs.project_id

	GROUP BY projects.id
) AS completed_projects
ON projects.id = completed_projects.project_id
WHERE 
	completed_projects.completed = completed_projects.all_statuses AND completed_projects.completed IS NOT NULL

Or we can also have the condition inside the subquery by using the HAVING clause

SELECT 
		projects.id AS project_id,
		GROUP_CONCAT( IF (job_state = 'delivered', job_state, NULL) SEPARATOR ',') as completed,
		GROUP_CONCAT( job_state SEPARATOR ',') as all_statuses
		
    FROM projects 
	INNER JOIN jobs ON projects.id = jobs.project_id

	GROUP BY projects.id
    HAVING GROUP_CONCAT( IF (job_state = 'delivered', job_state, NULL) SEPARATOR ',') = GROUP_CONCAT( job_state SEPARATOR ',') 

then the whole query would be like this:

SELECT * FROM projects
WHERE projects.id IN (
SELECT 
	projects.id AS project_id
		
	FROM projects 
	INNER JOIN jobs ON projects.id = jobs.project_id

	GROUP BY projects.id
 HAVING GROUP_CONCAT( IF (job_state = 'delivered', job_state, NULL) SEPARATOR ',') = GROUP_CONCAT( job_state SEPARATOR ',') 
)

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.