3

I have a user which contains a reference field "o" which points to an organisation:

> db.users.findOne()
{
"o" : ObjectId("4ec3548544ae1b7234548826")
}

Organisations contain a field "n":

> db.organisations.findOne()
{
    "n" : "My organization" 
}

I want a list of users sorted by o.n, preferably in Scala / Lift.

Sri
  • 5,805
  • 10
  • 50
  • 68
  • 2
    That won't work. If you can select all users, you can sort application-side, if not you may want to look at denormalization and duplicate the organisation name in the user records. – Thilo Dec 05 '11 at 07:00
  • Ouch.. that's unfortunate.. :-/ – Sri Dec 05 '11 at 07:06

1 Answers1

9

What you are effectively asking for is a JOIN. MongoDB has no notion of a JOIN.

From the server's perspective, collections simply don't know about each other. Some tools abstract this away (like Morphia), but there are really only two basic ways to make this happen:

  1. Manual join: load the users, then load the organizations, merge them together and join client-side.
  2. Denormalize: store a copy of the N field inside of the users collection (and keep it in sync). This will make your query work fast, but it will complicate updates.

This lack of JOINs is one of the fundamental MongoDB trade-offs. If this is a common query or essential query, you either have to do #1, #2 or #3: pick a different DB.

Gates VP
  • 44,957
  • 11
  • 105
  • 108
  • or #4: find a way to get your application working without the sort. – Thilo Dec 06 '11 at 06:18
  • 4
    This is no longer true as of version 3.2: https://docs.mongodb.com/master/reference/operator/aggregation/lookup/#pipe._S_lookup – Madbreaks Dec 08 '16 at 19:00
  • try sorting 500.000 docs by its relation's field with limit of 10 docs. i can't understand why this is so painful. – Renan Coelho May 26 '20 at 02:32