CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4activerecord
John Doe
John Doe

Reputation: 23

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 1

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"]]

Update 2

This solved the problem (suggested by @Ilya):

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

Upvotes: 2

Views: 357

Answers (2)

steel
steel

Reputation: 12540

You can do it entirely in the database, which is faster most of the time:

scope :without_children_at(date) = {
  joins(:childs).where("DATE(created_at) != DATE(?)", date)
}

Upvotes: 0

Ilya
Ilya

Reputation: 13487

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

Upvotes: 1

Related Questions