使用map和聚合将Mongodb查询转换为php

Question

我编写了一个mongodb查询，很难将其转换为php代码：

var geoips = db.geoip.find().map(function(like){ return like.ip; });

var result = db.audit.aggregate([
    { $match: { ip: { $nin: geoips } } },
    { $group: {
        _id: "$ip",
        count: { $sum: 1 }
    }}
]);

更新：

以上查询相当于以下关系数据库查询

Select ip,count(*) 
from audit 
where ip not in (select ip from geoip)
group by ip

由于我必须在MongoDBVersion3.0中进行此查询，所以无法像回答中所建议的那样利用$lookup。

下面的PHP代码实现了上述目标，并按预期工作。它获得与geoip集合不同的ips。它传递该结果，并对审计集合进行聚合以获得所需的结果。

$geoipcolln = $this->dbConn->selectCollection('geoip');       
$geoips = $geoipcolln->distinct('ip');        
$match = array('ip' => array('$nin' => $geoips));        
$result = $this->collection->aggregate(                    
                 array(
                        '$match' => $match
                    ),
                array('$group'  => array(
                            '_id'       => '$ip',                                
                            'count'     => array('$sum' => 1.0),                            
                        ))    
            ); 

Answer 1

这可以在一个使用$lookup操作符的聚合查询中完成，如下所示：

var result = db.audit.aggregate([
    {
        "$lookup": {
            "from": "geoip",
            "localField": "ip",
            "foreignField": "ip",
            "as": "geoips"
        }
    },
    { "$match": { "geoips.0": { "$exists": false } } },
    { "$group": {
        "_id": "$ip",
        "count": { "$sum": 1 }
    }}
])

然后，可以将其转换为PHP：

<?php
    $m = new MongoClient("localhost");
    $c = $m->selectDB("yourDB")->selectCollection("audit");
    $ops = array(
        array(
            "$lookup" => array(
                "from" => "geoip",
                "localField" => "ip",
                "foreignField" => "ip",
                "as" => "geoips"
            )
        ),
        array( "$match" => array( "geoips.0" => array( "$exists" => false ) ) ),
        array( "$group" => array(
            "_id" => "$ip",
            "count" => array( "$sum" => 1 )
        ))      
    );
    $results = $c->aggregate($ops);
    var_dump($results);
?>