CODEWITHSUNDEEP
sqlruby-on-rails
Julien Dargelos
Julien Dargelos

Reputation: 395

How to select records with zero associations from two has_many?

I have the following models:

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'
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.timestamps
    end
  end
end

How to select stops that don't have any associated report as departure or arrival ?

Upvotes: 1

Views: 83

Answers (2)

PGill
PGill

Reputation: 3521

You can do

class Stop
  scope :without_reports, -> {
    where(_not_exists(Report.where("stops.id IN (departure_id, arrival_id)")))
  }

  def self._not_exists(scope)
    "NOT EXISTS(#{scope.to_sql})"
  end
end

replace NOT EXISTS with EXISTS to get stops with reports

> Stop.without_reports
SELECT "stops".* FROM "stops" WHERE (NOT EXISTS(SELECT "reports".* FROM "reports" WHERE (stops.id IN (departure_id, arrival_id))))

Upvotes: 0

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33420

You can try using joins to define your own left outer join by checking the rows in the stops table that match by their id with the departure_id and/or arrival_id in the reports table.

After that, a where clause can be used to filter those reports where the departure_id or arrival_id are NULL:

Stop
  .joins('left outer join reports on stops.id IN (reports.departure_id, reports.arrival_id)')
  .where('reports.departure_id IS NULL OR reports.arrival_id IS NULL')

Upvotes: 2

Related Questions