John Doe John Doe - 7 months ago 26
Ruby Question

ActiveRecord: Scope to get parents with no children created at a specific date

I want to get all parent records with no records created at a specific date on a one-to-many relation in Rails 4 with ActiveRecord and Postgres as database.

Migrations:

class CreateParents < ActiveRecord::Migration
def change
enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')

create_table :parents, id: :uuid, default: 'gen_random_uuid()' do |t|
t.string :name
t.timestamps null: false
end
end

class CreateChilds < ActiveRecord::Migration
def change
create_table :childs, id: false do |t|
t.uuid :parent_id, null: false
t.date :created_at, null: false
t.string :name
end

add_foreign_key :childs, :parents
add_index :childs, [:parent_id, :created_at], :unique => true
end
end


Models:

class Parent < ActiveRecord::Base
has_many :childs
end

class Child < ActiveRecord::Base
belongs_to :parent
end


Now I want to get all parents with no child at a specific date with a scope:

class Parent < ActiveRecord::Base
has_many :childs

def self.with_no_childs_created_at(date)
...
end
end


Can anybody help me? I'm going really crazy with this. I tried a lot things with
.includes
,
.references
,
.where
,
.not
,
.joins
etc. but I don't get it.




Update

One suggested solution looked likes this:

def self.with_no_stats_created_at(date)
joins(:childs).where.not(childs: {created_at: date})
end


But this only works if the parent has already a child created in the past. The SQL should demonstrate the problem:

SELECT "parents".*
FROM "parents"
INNER JOIN "childs" ON "childs"."parent_id" = "parents"."id"
WHERE ("childs"."created_at" != $1) [["created_at", "2016-04-19"]]

Answer

You can preload childs to avoid N+1 queries and process it like array:

def self.with_no_childs_created_at(date)
  preload(:childs).select {|p| p.childs.all? {|c| c.created_at != date }}
end