Rails scope by absence of has_one polymorphic association

Question

I have records that, instead of being deleted, are invalidated by creating an entry in a polymorphic record_invalidations table.

The setup looks like this:

class RecordInvalidation < ActiveRecord::Base
  belongs_to :archivable, polymorphic: true
end


class Record < ActiveRecord::Base
  has_one :invalidation, class_name: "RecordInvalidation", as: :archivable

  def self.default_scope
    where(invalidation: nil)
  end
end

(There are multiple record classes, hence the polymorphic relation.)

Question: How do I make a scope, that returns records based on the absence of entries in another class pointing back at the class in question.

The current code does not work because the relation pointer is in the records_invalidations table, not the records table.

UPDATE 2

The record_invalidation class matches the table name just fine, I think the problem is the column name. Here's the (simplified) schema:

create_table "records", force: :cascade do |t|
  t.text     "content",        null: false
end

create_table "record_invalidations", force: :cascade do |t|
  t.integer  "archivable_id"
  t.string   "archivable_type"
end

UPDATE 3

I see know why the first error was happening, so I removed that update. Now I am passing the correct table name that I am joining in the SQL query, but I have a number of arguments mismatch. Can I pass another argument to the where clause?

self.joins(:invalidation).where('record_invalidations.id is null')

Gives:

ActiveRecord::StatementInvalid: PG::ProtocolViolation: ERROR:  bind 
message supplies 1 parameters, but prepared statement "a56" requires 2

: SELECT  "records".* FROM "records" 
    INNER JOIN "record_invalidations" ON 
    "record_invalidations"."archivable_id" = "records"."id" AND 
    "record_invalidations"."archivable_type" = $1 WHERE 
    (record_invalidations.id is null) AND "plans"."id" = $2 LIMIT 1

I can't check this at the moment hence the comment, but I think you can do `.joins(:invalidation).where('invalidations.id is null')` or similar, let me know if it works. — Sam Peacey, Sep 11 '15 at 00:11
Closer! I tried: self.joins(:invalidation).where('invalidations.id is null') and got a very close SQL output but seeing an error: ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "invalidation" LINE 1: ...cord_invalidations"."archivable_type" = $1 WHERE (invalidati... ^ — Soren Berg, Sep 11 '15 at 01:15
Can you maybe try `self.table_name = 'records'` (or whatever the table name actually is) in the RecordInvalidation class? Maybe the old scope would work - in this case? — Sam Peacey, Sep 11 '15 at 01:20
Okay, that makes more sense now :) How about `self.joins(:invalidation).where('records.id is null')`, my mistake. — Sam Peacey, Sep 11 '15 at 01:40
The last bug is fixed (Yay!) but a new challenger approaches (boo!). — Soren Berg, Sep 11 '15 at 02:22
Okay, the outputted sql is actually an `inner join`, where you need a `left join`, can you change the `joins` to `includes`. I'm not sure where the bind error message is coming from, do you have any idea what is generating this bit: `"plans"."id" = $2` - there's no reference to `plans` in your code (or indeed in the list of tables being selected from). — Sam Peacey, Sep 11 '15 at 04:31

score 0 · Answer 1 · edited May 23 '17 at 11:44

This may not be the most efficient way, but it works:

def self.default_scope
  # Only show records that were not invalidated
  # Makes a list of all invalidated record ids in a subquery then checks for id
  where.not(:id => RecordInvalidation.select(:archivable_id)
                                     .where(archivable_type: self.name)
                                     .uniq)
end

Thanks to rails scope to check if association does NOT exist

I just added the required filtering for the polymorphic association to that answer.

If anyone has a better solution feel free to add and I will accept that instead of my own.

Rails scope by absence of has_one polymorphic association

1 Answers1