CODEWITHSUNDEEP
ruby-on-railsactiverecord
Simon
Simon

Reputation: 751

Find all records whose association has a nil association in turn

Given the models below:

class Score < ApplicationRecord
  belongs_to :composition
end

class Composition < ApplicationRecord
  has_many :scores
  has_one :invoice, dependent: :destroy
end

class Invoice < ApplicationRecord
  belongs_to :composition
end

what's the best way to find the scores, whose composition has a nil invoice?

I tried:

Score.joins(:composition).where(composition: {invoice: nil})

csn = Composition.includes(:invoice).where(invoices:{id:nil})
Score.where(csn.include? composition)
Score.where(csn.map(&:id).include? composition_id)

Score.where(Composition.left_outer_joins(:invoice).where(invoices:{id:nil}).includes? composition)

all with errors. Any ideas?

EDIT: here are the corresponding tables as per schema.rb:

  create_table "compositions", force: :cascade do |t|
    ...
  end

  create_table "invoices", force: :cascade do |t|
    t.integer  "composition_id"
    ...
    t.index ["composition_id"], name: "index_invoices_on_composition_id", using: :btree
  end

  create_table "scores", force: :cascade do |t|
    t.integer  "composition_id",                           null: false
    ...
  end

Upvotes: 3

Views: 125

Answers (3)

Ganesh
Ganesh

Reputation: 2004

Please try following query,

Score.joins(:composition).where('compositions.id NOT IN (?)', Invoice.pluck(:composition_id))

If this not work then let me know the columns present in compositions and scores tables

Upvotes: 0

Tai
Tai

Reputation: 1254

Please try following query:

Score.joins(:composition).includes(composition: : invoice).where(invoices: { id: nil })

Upvotes: 1

user9295875
user9295875

Reputation:

Try following

Score.joins(:composition).where('compositions.invoice_id IS NULL')

above should work, Have a good luck!!!

Upvotes: 0

Related Questions