CODEWITHSUNDEEP
mongodb
mshell_lauren
mshell_lauren

Reputation: 5236

mongo query based on field calculation

I am looking for a way to query mongo for documents matching the results between two fields when compared to a variable.

For example, overlapping date ranges. I have a document with the following schema:

{startDate : someDate, endDate : otherDate, restrictions : {daysBefore : 5, daysAfter : 5}}

My user will supply their own date range like 

var userInfo = { from : Date,  to : Date}

I need the documents that satisfy this condition: 

startDate - restrictions.daysBefore <= userInfo.to && endDate + restrictions.daysAfter >= userInfo.from;

I tried using a $where clause, but I loose the context of the to and from since they are defined outside of the scope of the where function.

I would like to do this without pulling down all of the results, or creating another field upon insert.

Is there a simple way this query can be done?

Upvotes: 2

Views: 3437

Answers (1)

Charlie Page
Charlie Page

Reputation: 251

The aggregation framework [AF] will do what you want. The AF backend is written in C++ and therefor much faster then using JavaScript as an added bonus. In addition to faster then JavaScript there are number of reasons we discourage the use of $where some of which can be found in the $where docs. The AF docs(i.e. the good stuff to use): http://docs.mongodb.org/manual/reference/aggregation/ I am uncertain the format of the data you are storing, and this will also have an affect on performance. For instance if the date is the standard date of milliseconds since Jan 1st 1970 (unix epoch) and daysBefore is stored in (miliseconds per day) * (number of days), you can use simple math as the example below does. This is very fast. If not there are date conversions available in the AF, but that is of course more expensive to do the conversions in addition to getting the differences.

In Python (your profile mentions Django) datetime.timedelta can be used be used for daysBefore. For instance for 5 days: import datetime daysBefore=datetime.timedelta(5)

There are two main ways to go about what you want to use in the AF. Do the calculation directly and match on it, or create a new column and match against that. Your specific use case and testing against will be necessary for complicated or large scale deployments. An aggregate command from the shell to match against the calculation in Python:

fromDate=<program provided>
db.collection.aggregate([{"$match":{"startDate":{ "$lt": {"$add": ["$restrictions.daysBefore", fromDate]}}}}])

If you want to run multiple calculations in the same $match use $and:[{}, {}, …, {}]. I omitted that for clarity. Further aggregation documentation for the AF can be found at: http://api.mongodb.org/python/current/examples/aggregation.html#aggregation-framework

Note that “aggregation” also includes map reduce in Mongo, but this case the AF should be able to do it all (and much more quickly).

If you need any further information about the AF or if there is anything the docs don’t make clear, please don’t hesitate to ask.

Best, Charlie

Upvotes: 3

Related Questions