How to select records with zero associations from two nested has_many?

Some days ago I asked the following question: How to select records with zero associations from two has many?

I failed to adapt the answer that solved my previous question to this similar situation:

class Report < ApplicationRecord
  belongs_to :departure, class_name: 'Stop'
  belongs_to :arrival, class_name: 'Stop'
end

class Stop < ApplicationRecord
  has_many :departure_reports, foreign_key: 'departure_id', class_name: 'Report'
  has_many :arrival_reports, foreign_key: 'arrival_id', class_name: 'Report'
  belongs_to :journey
end

class Journey < ApplicationRecord
  has_many :stops
end

These are the corresponding migrations:

class CreateReports < ActiveRecord::Migration[5.2]
  def change
    create_table :reports do |t|
      t.references :departure, foreign_key: { to_table: :stops }
      t.references :arrival, foreign_key: { to_table: :stops }

      t.timestamps
    end
  end
end

class CreateStops < ActiveRecord::Migration[5.2]
  def change
    create_table :stops do |t|
      t.references :journey, foreign_key: true

      t.timestamps
    end
  end
end

class CreateJourneys < ActiveRecord::Migration[5.2]
  def change
    create_table :journeys do |t|
      t.timestamps
    end
  end
end

Here is a more visual description:

How to select journeys whose stops don’t have any associated report as departure or arrival ?

What I’m looking for would be equivalent to this:

Journey.all.select do |journey|
  journey.stops.all? do |stop|
    stop.departure_reports.count + stop.arrival_reports.count == 0
  end
end

Edit

Trying the solution proposed by Sebastian Palma:

Journey
  .left_joins(:stops)
  .joins('LEFT OUTER JOIN reports ON stops.id IN (reports.departure_id, reports.arrival_id)')
  .where(reports: { departure_id: nil, arrival_id: nil })

I get the following sql query:

SELECT "journeys".* FROM "journeys"
LEFT OUTER JOIN reports ON stops.id IN (reports.departure_id, reports.arrival_id)
LEFT OUTER JOIN "stops" ON "stops"."journey_id" = "journeys"."id"
WHERE "reports"."departure_id" IS NULL AND "reports"."arrival_id" IS NULL
LIMIT $1 [["LIMIT", 11]]

With this error:

Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "stops")
LINE 1: ...eys".* FROM "journeys" LEFT OUTER JOIN reports ON stops.id I...
                                                             ^

Answer

Have following model side changes,

class Journey < ApplicationRecord
  has_many :stops
  has_many :departure_reports, through: :stops
  has_many :arrival_reports, through: :stops
end

And get journeys having both

Journey.where.not(id: Journey.joins(:departure_reports, :arrival_reports).distinct.ids)

Good part of above is, only single query is fired even inner attributes are passed by calling another query.

Advertisement

Answer