mongodb - Multiple $group mongo request PHP

78

I would like to make a request with two $ group. Here you can see the structure of my data :

{ 
        "_id" : ObjectId("573495af4e998fec800041a7"), 
        "uniqid" : "not573495aeda725", 
        "status" : "waiting, 
        "date" : ISODate("2016-05-12T14:39:42.000+0000"), 
        "id_transaction" : null, 
        "hash_file" : null, 
        "user" : "Michel", 
        "desc" : "undefined", 
        "pharmacy" : "p56cdc980ba57f"
    },
    { 
        "_id" : ObjectId("573495af4e998fec800041a7"), 
        "uniqid" : "not573495aeda725", 
        "status" : "waiting, 
        "date" : ISODate("2016-05-12T14:39:42.000+0000"), 
        "id_transaction" : null, 
        "hash_file" : null, 
        "user" : "Julien", 
        "desc" : "undefined", 
        "pharmacy" : "p72gdf210xs68t"
    }

Here is where i am in my aggregate from now :

 $pipeline = array(
                    array(
                        '$project' => array(
                            'year' => array('$year' => array('$add' => array('$date',$offset))),
                            'month' => array('$month' => array('$add' => array('$date',$offset))),
                            'day' => array('$dayOfMonth' => array('$add' => array('$date',$offset))),
                            'hour' => array('$hour' => array('$add' => array('$date',$offset))),
                            "uniqid" => 1,
                            "status" => 1,
                            "date" => 1,
                            "pharmacy" => 1
                        )
                    ),
                    array(
                        '$match' => array(
                            '$and' => array(
                                array(
                                    "status" => array('$ne' => null)
                                ),
                                array(
                                    "status" => array('$ne' => "error")
                                ),
                                array(
                                    "date" => array('$lte' => new MongoDate(time()))
                                ),
                                array(
                                    "date" => array('$gt' => new MongoDate(strtotime($sixMonthAgo->format('Y-m-d'))))
                                )
                            )
                        )
                    ),
                    array(
                        '$group' => array(
                            '_id' => array(
                                "month" => array('$month' => '$date'),
                                "year" => array('$year' => '$date'),
                            ),
                            'count' => array('$sum' => 1)
                        )
                    ),
                    array(
                        '$group' => array(
                            '_id' => array(

                            ),
                            'count' => array('$sum' => 1)
                        )
                    )
                );

I would like to have the number on lines where status = "waiting" grouping my request by "year/month" and by "pharmacy".

Thank you so much !

533

Answer

Solution:

             array(
                    '$group' => array(
                        '_id' => array(
                            "month" => array('$month' => '$date'),
                            "year" => array('$year' => '$date'),
                             "status"  

                        ),
                        'count' => array('$sum' => 1)
                    )
                ),

then instead of grouping - use match for _id.status ="waiting"

242

Answer

Solution:

You do not need any $project ... Mongo Query should be -

[
  {
    "$match": {
      "status": "waiting",
      "date": {
        "$gt": {
          "$date": "DATE_SIX_MONTH_AGO"
        },
        "$lt": {
          "$date": "CURRENT_DATE"
        }
      }
    }
  },
  {
    "$group": {
      "_id": {
        "month": {
          "$month": "$date"
        },
        "year": {
          "$year": "$date"
        },
        "pharmacy": "$pharmacy"
      },
      "count": {
        "$sum": 1
      }
    }
  }
]

In PHP it should be -

$pipeline = array(
    array(
        '$match' => array(
            "status" => "waiting",
            "date" => array(
                '$lte' => new MongoDate(time()), 
                '$gt' => new MongoDate(strtotime($sixMonthAgo->format('Y-m-d')))
                )
        )
    ),
    array(
        '$group' => array(
            '_id' => array(
                "month" => array('$month' => '$date'),
                "year" => array('$year' => '$date'),
                "pharmacy" => '$pharmacy'
            ),
            'count' => array('$sum' => 1)
        )
    )
);

I did not test in PHP, check and correct the syntax errors if any.

People are also looking for solutions to the problem: php - preg_replace href value only if there string x is not found in it

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.