Sort a collections base on record of embedded object

Given a list of users with each users have their own embedded projects (timestamped). How do i get a list of users along with their latest projects?

e.g :

{"_id" : ObjectId("4dcc2a5b3db44135cf000004"),
 "name" : "User1",
 "projects" : [
   {"created_at" : ISODate("2011-05-13T08:16:22Z"),
    "_id" : ObjectId("4dcce8d63db4410f04000001"),
    "name" : "User1 Project 1"
   },
   {"created_at" : ISODate("2011-05-13T08:16:22Z"),
    "_id" : ObjectId("4dcce8d63db4410f04000002"),
    "name" : "User1 Project 2"
   }
 ]
}
{"_id开发者_如何学JAVA" : ObjectId("4dcc2fed3db44135cf000007"),
 "name" : "User2",
 "projects" : [
   {"created_at" : ISODate("2011-05-13T09:36:33Z"),
    "_id" : ObjectId("4dccfba13db4410f68000001"),
    "name" : User2 Project 2"
   }
 ]
}

I want to display this data in a tabular manner as : | user name | latest project name |

I realised that using mongoid i could do something like

users = User.all
users.each do | user |
   p user.name
   p user.latest_project_name
end

with latest_project_name as :
   def latest_project_name
      self.projects.desc(:created_at).try(:name) || ''
   end

the question are : 1. would this introduce n+1 problems as in sql? 2. how if I want to sort the table by latest project name? could I do this inside mongodb? or do I have to do an Array.sort?

note : I'm rather new to mongodb, so this might be a design problem. Still figuring out how to think in mongodb.

---

You can't sort an embedded document at this stage, instead if you need to sort your projects you should split them out into their own collection and use a document reference between users and projects.

See: https://jira.mongodb.org/browse/SERVER-142

As a small aside you can use the _id field to sort by since the ObjectID contains a timestamp (which actually removes the need for your created_at field, although having it doesn't hurt). See: http://www.mongodb.org/display/DOCS/Object+IDs if you want to know more about the format of the ObjectID.

---

If 'projects' is always sorted by 'created_at' then you can use the $slice operator to get hold of the last N entries of the 'projects' arrays. Otherwise you need to sort on the client side the 'projects' array and slice on the application level.