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

You might be better off embedding the "news" within the users' documents.

Answer 2

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.