CODEWITHSUNDEEP
phpmongodbmongodb-queryrelational-databaseaggregation-framework
FoxShrill
FoxShrill

Reputation: 91

Convert Mongodb shell query with map and aggregate to php

I wrote a mongodb query that I am having a hard time converting to php code:

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 }
    }}
]);

UPDATE:

The above query is the equivalent of the following Relation Database Query

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

Since I had to make this query in mongodb version 3.0, I was unable to take advantage of $lookup as suggested in an answer.

The below PHP code accomplishes the above objective and works as expected. It gets the distinct ips from geoip collection. It passes that result and does an aggregate on the audit collection to get the desired result.

$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),                            
                        ))    
            );

Upvotes: 2

Views: 609

Answers (1)

chridam
chridam

Reputation: 103475

This can be done in one aggregation query using the $lookup operator as follows:

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 }
    }}
])

which can then be translated to PHP as:

<?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);
?>

Upvotes: 1

Related Questions