CODEWITHSUNDEEP
rubymongoid
Andrew
Andrew

Reputation: 239097

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

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

Upvotes: 3

Views: 1920

Answers (6)

Mark Farmiloe
Mark Farmiloe

Reputation: 396

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}
        ]
    ]
})

Upvotes: 0

James Lim
James Lim

Reputation: 13062

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},
  ]
)

Upvotes: 0

Simon Repp
Simon Repp

Reputation: 548

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!

Upvotes: 0

Chris Heald
Chris Heald

Reputation: 62688

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},
  ]
})

Upvotes: 4

harsh4u
harsh4u

Reputation: 2610

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.

Upvotes: 1

Patrick Oscity
Patrick Oscity

Reputation: 54734

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}]}
)

Upvotes: 1

Related Questions