CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3mongodbmongoid
kapso
kapso

Reputation: 11903

MongoDB group by (+ sort & limit)

So I have the following group by SQL

select count(*) as NO_OF_MSGS,FROM_USER,PROFILE_IMG,MSG from MESSAGES group by FROM_USER order by NO_OF_MSGS desc,DATE_SENT limit ?,?

How can I replicate this group by in MongoDB. I came across this excellent write up - http://kylebanker.com/blog/2009/11/mongodb-count-group/

This shows how you can use group by in Mongo, but it does not talk about how to implement order by and limit inside a group.

Also it seems Mongoid does not provide support for group function, anyone know if its any different?

Upvotes: 0

Views: 2093

Answers (3)

Gates VP
Gates VP

Reputation: 45277

How can I replicate this group by in MongoDB. I came across this excellent write up - http://kylebanker.com/blog/2009/11/mongodb-count-group/

First off, that is a 2-year old write-up. Those operators (count, group, distinct) are still functional but they are quite slow. Using any of those operators amounts to running a Map/Reduce. And I'm not sure that sharding was ever implemented for those operators (note that the blog post pre-dates sharding).

The modern way to do this would be to use the new Aggregation Framework. This is both much faster and supports sharding. However, it is still in the unstable build.

This shows how you can use group by in Mongo, but it does not talk about how to implement order by and limit inside a group.

The query you are converting is a simple query in SQL, but this is not a simple query in MongoDB. The problem you are likely having with Mongoid is simply that you are doing something that MongoDB simply does not support (outside of the new Aggregation Framework).

If you do not have access to the Aggregation Framework, you will need to do this in multiple steps.

  1. Run a M/R to get the count(*) grouped by X.
  2. Do a query on the M/R output with your sort(), skip(), limit().
  3. The above may not work if you have too much data to sort (~1M users), you may need to index the output of the M/R for the sort to work.

Upvotes: 1

Ian Bishop
Ian Bishop

Reputation: 5205

You should check out Mongoid: Querying.

It provides an explanation of how to use order_by and limit.

EDIT:

Removed comment about distinct and group by being equivalent as @mu pointed out. Instead, you should use group and provide a reduce function as detailed in the article you linked to.

Upvotes: 0

Mukesh
Mukesh

Reputation: 1267

For grouping you can use group_by method of rails

http://api.rubyonrails.org/classes/Enumerable.html#method-i-group_by

And for ordering you can use sort_by method of Array

http://www.ruby-doc.org/core-1.9.3/Array.html#method-i-sort_by-21

Upvotes: 2

Related Questions