CODEWITHSUNDEEP
phpmongodbmongodb-queryaggregation-framework
amitshree
amitshree

Reputation: 2298

Mongo group and sort is not providing data in proper order

I've following documents in search_analytics collection

{ "_id" : 1, "keyword" : "24", "found" : 1, "created_at" : "2018-02-27 18:49:07" }
{ "_id" : 2, "keyword" : "100", "found" : 1, "created_at" : "2018-02-27 18:49:10" }
{ "_id" : 3, "keyword" : "15032040", "found" : 1, "created_at" : "2018-02-27 18:49:42" }
{ "_id" : 4, "keyword" : "100", "found" : 1, "created_at" : "2018-02-27 18:49:55" }

Now I want to get unique search keywords ordered by recently created document at top.

Here is the php code snippet 

$result = $collection->aggregate([
    ['$match' => ['found' => ['$ne' => 0]]],
    ['$group' => ['_id' => ['keyword' => '$keyword']]],
    ['$sort' => ['created_id' => -1] ],
    ['$limit' => (int) $docCount],
    ['$project' => ['keyword' => '$_id.keyword']]

]);

Which gives following 2 data's when $docCount is 2.

{
    "_id": {
        "keyword": "15032040"
    },
    "keyword": "15032040"
},
{
    "_id": {
        "keyword": "100"
    },
    "keyword": "100"
}

Whereas I was expecting result as following

{
    "_id": {
        "keyword": "100"
    },
    "keyword": "100"
},
{
    "_id": {
        "keyword": "15032040"
    },
    "keyword": "15032040"
}

What is missing here? Without group sorting is working fine.

Upvotes: 0

Views: 965

Answers (3)

s7vr
s7vr

Reputation: 75944

You can use below aggregation.

$result = $collection->aggregate([
    ['$match' => ['found' => ['$ne' => 0]]],
    ['$sort' => ['created_at' => -1 ]],
    ['$group' => [
        '_id' => '$keyword',
        'created_at' => ['$push' => '$created_at']
    ]],
    ['$addFields' => ['created_at' => ['$arrayElemAt' => ['$created_at', 0]]]],
    ['$sort' => ['created_at' => -1 ]],
    ['$limit' => (int) $docCount]
])

Upvotes: 1

amitshree
amitshree

Reputation: 2298

Though sort and group didn't work together for me. I've solved it using php's array_unique function.

Complete solution:

   $docCount = $this->params()->fromQuery('count');
   $storeId = (string) $this->params()->fromQuery('store_id');
   $collection = new \MongoDB\Collection($this->getManager(), $this->db, $this->searchAnalytics);
    $result = $collection->aggregate([
        ['$match' => ['store_id' => $storeId, 'found' => ['$ne' => 0]]],
        ['$sort' => ['created_at' => -1] ]
    ]);
    $array = $result->toArray();
    $arr = array_column($array, 'keyword');
    $unique = array_unique($arr);
    $limited = array_splice($unique, 0, (int)$docCount);

Upvotes: 0

chridam
chridam

Reputation: 103375

You are sorting on a non-existing field which is removed from the pipeline because of the $group pipeline step. You ideally want to sort before the $group stage and then create a list of ordered documents within the group which you can then $slice and $unwind further down the pipeline.

Consider running the following aggregate operation instead:

$result = $collection->aggregate([
    ['$match' => ['found' => ['$ne' => 0]]],
    ['$sort' => ['created_at' => -1 ]],
    ['$group' => [
        '_id' => null,
        'keywords' => ['$addToSet' => '$keyword']
    ]],
    ['$project' => [
        'keyword' => ['$slice' => ['$keywords', (int) $docCount]]
    ]],
    ['$unwind' => '$keyword']
])

NOTE

While $addToSet only ensures that there are no duplicate items added to the set and does not affect existing duplicate elements, it does not guarantee a particular ordering of elements in the modified set.

Upvotes: 2

Related Questions