Querying MongoDB for last-items-before

Consider I have two collections in MongoDB. One for products with documents like:

{'_id': ObjectId('lalala'), 'title': 'Yellow banana'}

And another stores price changes with documents like:

{'product': DBRef('products', ObjectId('lalala')),
 'since': datetime(2011, 4, 5),
 'new_price': 150 }

One product may have many price changes. The price l开发者_Go百科asts until a new change with later time stamp. I guess you've caught idea.

Say, I have 100 products. I want to query my DB to get know what's the price of each product at the moment of June 9, 2011. What is the most efficient (quick) way to perform this query in MongoDB? Suppose I have no cache solution or cache is empty.

I thought about group statement on prices collection, where reduce function would select last since before a date provided, grouping by product.$id. But in this case I would not benefit from an index on since field and all documents would be scanned.

Any ideas?

I had a similar problem, but for GPS locations. I found the fastest way was to set up a query for each item, which is rather counter-intuitive if your used to SQL databases.

Query for the item where it's timestamp is less or equal than the date your looking for, and limit the result to 1. Repeat for each item. To really speed things up, run multiple querys in parallel to utilise all the cores on the MongoDB server.