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:
x
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...
^
Advertisement
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.