How to convert mongoDB Query in php?

Question

I am facing a problem to convert a mongoDB query into php

This is my mongoDB query

db.contact_facts.mapReduce(
         function() {
             var k = new Date(this.date);
     k.setHours(0);
             k.setMinutes(0);
             k.setSeconds(0);
             k.setMilliseconds(0);
             emit({idM:this.idMailing,
        fact:this.fact,
        year:k.getFullYear(),
        month:k.getMonth(),
        day:k.getDate()
    }, 1);
         }, 
         function (key, values) {
            return Array.sum(values);
         },
         {
     query: { date: { $gt: ISODate('2016-09-20') } },
    out: { reduce: "contact_fact_stats2"},
         }
 );

I am facing a problem to convert this line into php code

query: { date: { $gt: ISODate('2016-09-20') } },

This is what wrote in php

$dm = $this->getContainer()->get('doctrine_mongodb')->getManager();
$qb = $dm->createQueryBuilder('AtayenMainBundle:ContactFact');
$qb->field('date')->equals("ISODate('2016-09-20T08:42:30.000Z')");
$qb->map('function() {
         var k = new Date(this.date);
       k.setHours(0);
             k.setMinutes(0);
             k.setSeconds(0);
             k.setMilliseconds(0);
        emit({idM:this.idMailing,fact:this.fact,date:this.date}, 1)}');

$qb->reduce('function(key, values) {
        return Array.sum(values);

    }');

$cursor = $qb->getQuery()->execute();

chridam · Answer 1 · 2016-09-26T08:54:20.993

I believe the same query can be done more efficiently with the aggregation framework. The performance with the aggregation framework is much better as it runs "within" MongoDB in its C++ code hence more efficient than mapReduce which runs within a V8/spidermonkey (depending on your version) environment within the bundled JS console.

Consider running the following pipeline to get the desired results:

var pipeline = [
    { "$match": { "date": { "$gt": new Date("2016-09-20") } } },
    {
        "$project": {
            "fact": 1,
            "idMailing": 1,
            "formattedDate": { 
                "$dateToString": { "format": "%Y-%m-%d", "date": "$date" } 
            }
        }
    },
    {
        "$group": {
            "_id": {
                "fact": "$fact",
                "idM": "$idMailing",
                "formattedDate":  "$formattedDate"
            },
            "count": { "$sum": 1 }
        }
    }
]
db.contact_facts.aggregate(pipeline);

which in doctrine mongo odm you can run your pipeline operation using the command function as follows:

$connection = $this->getContainer()->get('doctrine_mongodb')->getConnection();
$mongo = $connection->getMongo();
if (!$mongo) {
    $connection->connect();
    $mongo = $connection->getMongo();
}
$db = $mongo->selectDB('test_database');

// construct pipeline
$pipeline = array( 
    array("$match" => array("date" => array("$gt" => new MongoDate(strtotime("2016-09-20")) ) ) ),
    array( 
        "$project" => array(
            "formattedDate" => array( 
                "$dateToString" => array("format" => "%Y-%m-%d", "date"=>  "$date") 
            ),
            "fact" => 1,
            "idMailing" => 1
        )
    ),
    array(
        "$group" => array(
            "_id" => array(
                "fact" => "$fact",
                "idM" => "$idMailing",
                "formattedDate" => "$formattedDate"
            ),
            "count" => array( "$sum" => 1 )
        )
    )
);

// run the aggregate command
$aggregate_results = $db ->command(array( 
    "aggregate" => "contact_facts",
    "pipeline" => $pipeline
));

or create an aggregation query:

$expr = new \Solution\MongoAggregation\Pipeline\Operators\Expr;
$aq = $this->get('doctrine_mongodb.odm.default_aggregation_query')
        ->getCollection('AtayenMainBundle:ContactFact')->createAggregateQuery()
        ->group(['_id' => [
            'year' => $expr->year('$date'),
            'month' => $expr->month('$date'),
            'day' => $expr->day('$date'),
            'fact' => '$fact',
            'idMailing' => '$idMailing'
        ], 'count' => $expr->sum(1)]);

With the mapReduce operation, you can run the command as:

$connection = $this->getContainer()->get('doctrine_mongodb')->getConnection();
$mongo = $connection->getMongo();
if (!$mongo) {
    $connection->connect();
    $mongo = $connection->getMongo();
}
$db = $mongo->selectDB('test_database');


// construct map and reduce functions
$map = new MongoCode("function() {
        var k = new Date(this.date);
        k.setHours(0);
        k.setMinutes(0);
        k.setSeconds(0);
        k.setMilliseconds(0);

        var obj = {
            idM: this.idMailing,
            fact: this.fact,
            year: k.getFullYear(),
            month: k.getMonth(),
            day: k.getDate()
        };
        emit(obj, 1);
    }");

$reduce = new MongoCode("function (key, values) {
    return Array.sum(values);
    }");

$mr = $db->command(array(
    "mapreduce" => "contact_facts", 
    "map" => $map,
    "reduce" => $reduce,
    "query" => array("date" => array("$gt" => new MongoDate(strtotime("2016-09-20")) ) ),
    "out" => array("merge" => "eventCounts")));

$results = $db->selectCollection($mr['result'])->find();

foreach ($results as $res) {
    echo "{$res['_id']} had {$res['value']} counts.\n";
}

@chirdam what should I do to resolve the class not found exception ? — Ibrahim MAATKI, Sep 26 '16 at 10:10
@IbrahimMAATKI Which PHP version are you using? Depending on that aspect, the answers with this question may help http://stackoverflow.com/questions/34667609/ — chridam, Sep 26 '16 at 10:17
Check the last answer in this question http://stackoverflow.com/questions/8617481/doctrine-mongodb-odm-and-datetime-query — chridam, Sep 26 '16 at 11:00

How to convert mongoDB Query in php?

1 Answers1