ActiveRecord: Loading records with all child association eager loaded, but select the ones that any of the children hits the condition

Question

I have models like the following.

class Project < ActiveRecord::Base
  # id         :integer  
  # name       :string

  has_many :assignments
end

class Assignment < ActiveRecord::Base
  # id         :integer  
  # finished   :boolean
  # project_id :integer  

  belongs_to :project
end

I want to load the projects that have any unfinished assignments, but with all assignments eager loaded. Hopefully, in a single SQL query.

Suppose I have record like the following

- project_1
  - assignment [finished: false]
  - assignment [finished: false]
  - assignment [finished: false]

- project_2
  - assignment [finished: true]
  - assignment [finished: true]
  - assignment [finished: true]

- project_3
  - assignment [finished: true]
  - assignment [finished: false]
  - assignment [finished: true]

Then the records I want is like the following.

- project_1
  - assignment [finished: false]
  - assignment [finished: false]
  - assignment [finished: false]

- project_3
  - assignment [finished: true]
  - assignment [finished: false]
  - assignment [finished: true]

Here're the questions

1. Can I achieve this using only ActiveRecord's query, arel, or squeel gem's expression?

2. If not, what kinda SQL query work for this?

I've tried the following ruby code, but it filters out the finished assignments.

Project.eager_load(:assignments).where('assignments.finished = ?', false)

# which results in
# 
#- project_1
#  - assignment [finished: false]
#  - assignment [finished: false]
#  - assignment [finished: false]
#
#- project_3
#  - assignment [finished: false]

Answer 1

Hey you can achieve this using ActiveRecord's query as

@unfinished = Assignment.where(:finished => false).pluck(:project_id)

@projects = Project.includes(:assignments).where(:id => @unfinished)

Active Record with sql in single query as,

 Project.includes(:assignments).where("projects.id in (select project_id from assignments where assignments.finished = ?)", false)