mongodb php - how to do "INNER JOIN"-like query

Question

I'm using the Mongo PHP extension.

My data looks like:

users
{
  "_id": "4ca30369fd0e910ecc000006",
  "login": "user11",
  "pass": "example_pass",
  "date": "2010-09-29"
},
{
  "_id": "4ca30373fd0e910ecc000007",
  "login": "user22",
  "pass": "example_pass",
  "date": "2010-09-29"
}

news
{
  "_id": "4ca305c2fd0e910ecc000003",
  "name": "news 333",
  "content": "news content 3333",
  "user_id": "4ca30373fd0e910ecc000007",
  "date": "2010-09-29"
},
{
  "_id": "4ca305c2fd0e910ecc00000b",
  "name": "news 222",
  "content": "news content 2222",
  "user_id": "4ca30373fd0e910ecc000007",
  "date": "2010-09-29"
},
{
  "_id": "4ca305b5fd0e910ecc00000a",
  "name": "news 111",
  "content": "news content",
  "user_id": "4ca30369fd0e910ecc000006",
  "date": "2010-09-29"
}

How to run a query similar like this, from PHP?

SELECT n.*, u.* 
FROM news AS n 
INNER JOIN users AS u ON n.user_id = u.id

Answer 1

MongoDB does not support joins. If you want to map users to the news, you can do the following

1) Do this at the application-layer. Get the list of users, and get the list of news and map them in your application. This method is very expensive if you need this often.

2) If you need to do the previous-step often, you should redesign your schema so that the news articles are stored as embedded documents along with the user documents.

    {
      "_id": "4ca30373fd0e910ecc000007",
      "login": "user22",
      "pass": "example_pass",
      "date": "2010-09-29"
      "news" : [{  
                   "name": "news 222",
                   "content": "news content 2222",
                   "date": "2010-09-29" 
                }, 
                {
                   "name": "news 222",
                   "content": "news content 2222",
                   "date": "2010-09-29"
                }]
    }

Once you have your data in this format, the query that you are trying to run is implicit. One thing to note, though, is that analytics queries become difficult on such a schema. You will need to use MapReduce to get the most recently added news articles and such queries.

In the end the schema-design and how much denormalization your application can handle depends upon what kind of queries you expect your application to run.

You may find these links useful. http://www.mongodb.org/display/DOCS/Schema+Design http://www.blip.tv/file/3704083

I hope that was helpful.

Answer 2

Forget about joins.

do a find on your news. Apply the skip number and limit for paging the results.

$newscollection->find().skip(20).limit(10);

then loop through the collection and grab the user_id in this example you would be limited to 10 items. Now do a query on users for the found user_id items.

// replace 1,2,3,4 with array of userids you found in the news collection.
$usercollection.find( { _id : { $in : [1,2,3,4] } } ); 

Then when you print out the news it can display user information from the user collection based on the user_id.

You did 2 queries to the database. No messing around with joins and figuring out field names etc. SIMPLE!!!