mongodb php - 如何做“内部联接”之类的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3829638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mongodb php - how to do "INNER JOIN"-like query
提问by persten
I'm using the Mongo PHP extension.
我正在使用 Mongo PHP 扩展。
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?
如何从 PHP 运行类似这样的查询？
SELECT n.*, u.*
FROM news AS n
INNER JOIN users AS u ON n.user_id = u.id
回答by srivani
MongoDB does not support joins. If you want to map users to the news, you can do the following
MongoDB 不支持连接。如果要将用户映射到新闻，可以执行以下操作
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.
1) 在应用层执行此操作。获取用户列表，获取新闻列表并将它们映射到您的应用程序中。如果您经常需要此方法，则此方法非常昂贵。
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.
2) 如果您需要经常执行上一步，您应该重新设计您的架构，以便将新闻文章与用户文档一起存储为嵌入式文档。
{
"_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.
一旦您拥有这种格式的数据，您尝试运行的查询就是隐式的。但需要注意的一件事是，分析查询在这种模式上变得困难。您将需要使用 MapReduce 来获取最近添加的新闻文章和此类查询。
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+Designhttp://www.blip.tv/file/3704083
您可能会发现这些链接很有用。 http://www.mongodb.org/display/DOCS/Schema+Designhttp://www.blip.tv/file/3704083
I hope that was helpful.
我希望那是有帮助的。
回答by Richard Torcato
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.
然后循环遍历集合并在本例中获取 user_id，您将被限制为 10 个项目。现在对找到的 user_id 项目的用户进行查询。
// 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.
然后，当您打印出新闻时，它可以根据 user_id 从用户集合中显示用户信息。
You did 2 queries to the database. No messing around with joins and figuring out field names etc. SIMPLE!!!
您对数据库进行了 2 次查询。不要搞乱连接和找出字段名称等。简单！！！
回答by luckytaxi
You might be better off embedding the "news" within the users' documents.
您最好将“新闻”嵌入用户文档中。
回答by chridam
If you are using the new version of MongoDB (3.2), then you would get something similar with the $lookupoperator.
如果您使用的是新版本的 MongoDB (3.2)，那么您将获得与$lookup操作符类似的内容。
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 newscollection joins the documents from newswith the documents from the userscollection using the fields user_idfrom the newscollection and the _idfield from the userscollection:
在下面的聚合操作news系列外，从文件news从文件users使用的字段集合user_id从news收集和_id从外地users收集：
db.news.aggregate([
{
"$lookup": {
"from": "users",
"localField": "user_id",
"foreignField": "_id",
"as": "user_docs"
}
}
])
The equivalent PHP example implementation:
等效的 PHP 示例实现：
<?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);
?>
回答by Adrian Lopez
You can't do that in mongoDB. And from version 3 Eval() is deprecated, so you shouldn't use stored procedures either.
你不能在 mongoDB 中做到这一点。从版本 3 开始，不推荐使用 Eval()，因此您也不应该使用存储过程。
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.
我现在知道实现涉及多个集合的服务器端查询的唯一方法是使用 Node.js 或类似的。但是，如果您打算尝试这种方法，出于安全原因，我强烈建议您限制允许访问您的机器的 IP 地址。
Also, if your collections aren't too big, you can avoid inner joins denormalizing them.
此外，如果您的集合不是太大，您可以避免内部连接对它们进行非规范化。
