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

Claque

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.

Garamond

If you're storing entire news articles embedded with your users, is the 4MB object storage limit a concern? – Stairs

You can use dbrefs instead of the actual document docs.mongodb.org/manual/applications/database-references – Cairistiona

This answer is no longer correct and should be updated to reflect that. – Wetnurse

@Wetnurse In what way is this answer incorrect? The only error I see is the first sentence “MongoDB does not support joins”, because MongoDB now has $lookup, which performs a left outer join. Is there anything wrong with the two solutions that this answer does describe? – Lacteous

@RoryO'Kane As you note, the answer says MongoDB does not support joins. This is no longer strictly correct, and as I suggested the answer should be updated to clarify that. – Wetnurse

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!!!

Thromboembolism

Answer 3

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

Ness

best answer, nosql is all about letting things go for new structures come to life – Venation

Answer 4

If you are using the new version of MongoDB (3.2), then you would get something similar with the $lookup operator.

The drawbacks with using this operator are that it is highly inefficient when run over large result sets and it only supports equality for the match where the equality has to be between a single key from each collection. The other limitation is that the right-collection should be an unsharded collection in the same database as the left-collection.

The following aggregation operation on the news collection joins the documents from news with the documents from the users collection using the fields user_id from the news collection and the _id field from the users collection:

db.news.aggregate([
    {
        "$lookup": {
            "from": "users",
            "localField": "user_id",
            "foreignField": "_id",
            "as": "user_docs"
        }
   }
])

The equivalent PHP example implementation:

<?php
$m = new MongoClient("localhost");
$c = $m->selectDB("test")->selectCollection("news");
$ops = array(
    array(
        "$lookup" => array(
            "from" => "users",
            "localField" => "user_id",
            "foreignField" => "_id",
            "as" => "user_docs"
        )
    )
);
$results = $c->aggregate($ops);
var_dump($results);
?>

Omalley

"the $lookup operator which can be likened to a left outer join in SQL" op specifically asked for how to do INNER joins. – Wetnurse

Answer 5

You can't do that in mongoDB. And from version 3 Eval() is deprecated, so you shouldn't use stored procedures either.

The only way I know to achieve a server side query involving multiple collections right now it's to use Node.js or similar. But if you are going to try this method, I strongly recommend you to limit the ip addresses allowed to access your machine, for security reasons.

Also, if your collections aren't too big, you can avoid inner joins denormalizing them.

Useless