网友回复
可以使用mongodb的高级查询$lookup
{ $lookup: { from: <collection to join>, localField: <field from the input documents>, foreignField: <field from the documents of the "from" collection>, as: <output array field> } }
其实换成相当于sql语句的join用法
SELECT *, <output array field> FROM collection WHERE <output array field> IN (SELECT * FROM <collection to join> WHERE <foreignField> = <collection.localField>);
我们以php为例,来编写一个$lookup的聚合查询连表多表示例代码:
<?php $manager = new MongoDB\Driver\Manager("mongodb://localhost:27017"); // 插入数据 $bulk = new MongoDB\Driver\BulkWrite; // 插入商品数据 $bulk->insert(['prodid' => 1, 'title' => '产品1']); $bulk->insert(['siteid' => 2, 'title' => '产品2']); $_data = $manager->executeBulkWrite('test.product', $bulk); $bulk = new MongoDB\Driver\BulkWrite; // 插入订单数据 $bulk->insert(['orderid' => 1, 'title' => '订单1',"prodid"=>1]); $bulk->insert(['orderid' => 2, 'title' => '订单2',"prodid"=>1]); $_data = $manager->executeBulkWrite('test.order', $bulk); $param = [ 'aggregate' => 'product', //表名 'pipeline' => [ [ '$lookup' => ['from' => "order", 'localField' => 'prodid', 'foreignField' => 'orderid', 'as' => 'orders'] ]], 'cursor' => new \stdClass()// 高版本需要带上这个值 ]; $command = new \MongoDB\Driver\Command($param); $cursor = $manager->executeCommand('test', $command); foreach ($cursor as $document) { $document = objectToArray($document); var_dump($document); } function objectToArray($object) { $result = array(); $object = is_object($object) ? get_object_vars($object) : $object; foreach ($object as $key => $val) { $val = (is_object($val) || is_array($val)) ? objectToArray($val) : $val; $result[$key] = $val; } return $result; }