Sorting across collection strategies

[danpolites]

We have been using Mongo for over a year now and everything has been great with it. It works perfectly for our dynamic objects and it has been easy to maintain. The problem that we are running into now is with reporting. We basically need to provide reports across our entire system to our users. My thought was to use something like Redis in front of mongo to cache the user information for these reports, but now I am stuck on the sorting for this information. If we were to denormalize the data, we would have user information copied across 20-30 collections. That just sounds like a nightmare to maintain and keep up-to-date. Pulling the data out into our application and sorting that way could potentially lead to performance issues due to the amount of data that could possible be returned over a year and the amount of users accessing reports at that time. So here is what I'm trying to solved.

user collection

{firstName:'John', lastName:'Smith', id:'123abc'}

{firstName:'Andy', lastName:'Smith', id:'123def'}

report collection 1  (assume millions of records and many more fields)

{someCount:34, someScore, 23, userId:'123abc'}

{someCount:1 someScore, 12, userId:'123def'}

report collection 2 (assume millions of records and many more fields)

{someCount:1 someScore, 12, userId:'123abc'}

{someCount:1 someScore, 12, userId:'123def'}

and so on

Let's say I want to put data from report collection 2 into jqGrid. How can I sort that information on the user's first name/last name.

Andy Smith 1 12

John Smith 1 12

At this point it's looking like we need to, at the very least, move our user information and reporting data to a relational database. I would rather not maintain two databases, however. Thoughts?

[Brandon Diamond]

I'm not sure I understand the problem.

MongoDB supports arbitrary ordering and limiting. Sorting can be made
more efficient using an index. If you need to return data in sorted
order, you can use the .sort() method (http://www.mongodb.org/display/
DOCS/Querying#Querying-Sorting)

Using a redis cache may not be necessary, especially if it makes it
harder to sort.

Finally, my understanding is that jqGrid will do sorting on client
side. If you instantiate the grid with a tremendous amount of data,
sorting will be slow. Generally, you can use a timestamp (or _id,
which contains a timestamp) to limit the data included in the report
to a managable quantity of recent data.

Can you perhaps rephrase the question (if the above is off track)?
Thanks!
- Brandon