How to create a Mongoid "within date range or nil" query?

Question

I'm trying to create a Mongoid query for records that match a certain date range, or have nil values. Here is my ruby code that performs the function that I would like to turn into a Mongoid query:

class MyModel
  include Mongoid::Document
  field :name
  field :enabled, type: Boolean, default: false
  field :start_date, type: DateTime
  field :end_date, type: DateTime

  def self.active
    documents = where(enabled: true)
    documents = documents.keep_if {|doc| doc.start_date.nil? || doc.start_date <= Date.today}
    documents = documents.keep_if {|doc| doc.end_date.nil? || doc.end_date >= Date.tomorrow}
    documents
  end
end

How can I improve performance by turning this method into a Mongoid query?

Update:

Here is the RSpec test that I'm using to verify the correct behavior:

describe '.active' do
  let!(:disabled){ Fabricate(:model, enabled: false, name: 'disabled') }
  let!(:enabled_without_date){ Fabricate(:active_model, name: 'enabled_without_date') }
  let!(:past){ Fabricate(:active_model, start_date: 1.week.ago, end_date: Date.yesterday, name: 'past') }
  let!(:current){ Fabricate(:active_model, start_date: Date.today, end_date: Date.tomorrow, name: 'current') }
  let!(:future){ Fabricate(:active_model, start_date: Date.tomorrow, end_date: 1.week.from_now, name: 'future') }
  
  it 'returns only enabled and within the current time' do
    MyModel.count.should == 5
    models = MyModel.active.to_a
    models.should_not be_empty
    models.should_not include disabled
    models.should_not include past
    models.should_not include future
    models.should include enabled_without_date
    models.should include current
  end
end

Answer 1

If you convert the criteria:

(start <= X OR start.nil?) AND (end >= Y OR end.nil?)

into the disjunctive form, you get:

(start <= X AND end >= Y) OR (start <= X and end.nil?) OR (start.nil? and end >= Y) or (start.nil? and end.nil?)

You can then express this via a single $or clause:

$or: [
  {:start_date.lte => start_date, :end_date.gte => end_date},
  {:start_date => nil, :end_date.gte => end_date},
  {:start_date.lte => start_date, :end_date => nil},
  {:start_date => nil, :end_date => nil},
]

If both values must be either set or nil (that is, you can't have one set and one nil), this becomes even simpler:

$or: [
  {:start_date.lte => start_date, :end_date.gte => end_date},
  {:start_date => nil},
]

To meet your spec, the full query would be:

Model.where({
  :enabled => true,
  :$or => [
    {:start_date.lte => Date.today.to_time, :end_date.gte => Date.tomorrow.to_time},
    {:start_date => nil},
  ]
})

Answer 2

Maybe this will do the trick:

where(
  enabled: true,
  {:$or => [{start_date: nil}, {:start_date.lte => Date.today.to_time   }]},
  {:$or => [{end_date:   nil}, {:end_date.gte   => Date.tomorrow.to_time}]}
)

Answer 3

Use below code:

where(:enabled => true, :$or => [{:start_date => nil}, {:start_date =>['$lte' => Date.today]}], :$or => [{:end_date => nil}, {:end_date =>['$gte' =>Date.tomorrow]}])

Hope this help you.

Answer 4

Using

Document.where(
  enabled: true,
  '$or' => [
    { start_date: nil },
    { :start_date.lte => Date.today.to_time }
  ],
  '$or' => [
    { end_date: nil },
    { :end_date.gte => Date.tomorrow.to_time }
  ]
).each do |d|
  puts d.inspect
end

I was able to get

[2] pry(main)> load './documents.rb'
#<Document _id: 51e89c690e21d8ab0d9cf012, enabled: true, start_date: nil, end_date: nil>
#<Document _id: 51e8a2e62147b4bfb5f12c65, enabled: true, start_date: 2012-02-01 05:00:00 UTC, end_date: 2014-02-01 05:00:00 UTC>
#<Document _id: 51e8a4797372723f449765bd, enabled: true, start_date: nil, end_date: 2014-02-01 05:00:00 UTC>
=> true

Update

You are right. My answer was completely bogus. The second $or key would have overridden the first. However, even wrapping the entire thing with $and (thus using an array of hashes) doesn't help - mongoid could be preventing multiple $or conditions.

On a brighter note, Chris Heald's solution works. Here is a complete proof. Run it with rspec mongoid_query.rb.

start_date = Date.today
end_date = Date.tomorrow
where(
  enabled: true,
  :$or => [
    {:start_date.lte => start_date, :end_date.gte => end_date},
    {:start_date => nil, :end_date.gte => end_date},
    {:start_date.lte => start_date, :end_date => nil},
    {:start_date => nil, :end_date => nil},
  ]
)

Answer 5

All answers given so far seem alright to me. However I'll add another syntax variant that has worked for me in previous projects, which also takes care of the redundant $or key scenario, which seems a little fishy to me (but maybe doing that is totally ok anyway).

Document.where({
   '$and' => [
       :enabled => true,
       '$or' => [
           :start_date => nil,
           :start_date => { '$lte' => Date.today.to_time }
       ],
       '$or' => [
           :end_date => nil,
           :end_date => { '$gte' => Date.tomorrow.to_time }
       ]
    ]
})

In terms of further advice: Have you reviewed your spec as well? (You never know ... ;)). Also make sure to debug and test only parts of the problem, e.g. get the :enabled filter working on it's own, get the date filters working on their own, get the nil filters working on their own, and then try to combine them again - Maybe that will lead you to the core of the problem. Also I'm seeing different variants of specifying the Date that $lte and $gte are compared against here. I myself had success with providing a Time class to compare against, make sure to experiment with that as well!

Answer 6

It appears to be a limitation in the mongodb driver itself! Just using the mongodb java shell I created three records:

db.so.insert({s:0,e:10})
db.so.insert({s:10,e:20})
db.so.insert({s:20,e:30})

then tried the following queries:

db.so.find({s:{$lte:15}, e:{$gte:15}})  
   ==> finds just the middle record - correct
db.so.find({s:{$lte:15}, $or:[{e:{$exists:false}},{e:{$gte:15}}]})  
   ==> finds just the middle record - correct
db.so.find({$or:[{s:{$exists:false}},{s:{$lte:15}}], $or:[{e:{$exists:false}},{e:{$gte:15}}]})
   ==> finds both the middle and the last record - OOPS
db.so.find({$or:[{s:{$exists:false}},{$and:[{s:{$lte:15}},{e:{$gte:15}}]}]})
   ==> finds just the middle record - correct

I presume this means you cannot use more than one $or at the same level (as it says you can nest them). I will post this as a question on the mongodb site and see whether they agree. In the meantime, in your case the fourth query points the way to a possible work round, though Chris Heald has already suggested almost the same:

Model.where({
    :enabled => true,
    :$or => [
        {:start_date => nil},
        :$and => [
            {:start_date.lte => Date.today.to_time}, 
            {:end_date.gte => Date.tomorrow.to_time}
        ]
    ]
})