query the collection to improve performance

[nemesisKO]

I want get filtered collection between to date and store it into some variable. then use this variable into another functions to group by and ... for avoiding each function filtered entire collection by date and group by. my code is

$result = $query->whereRaw([
        '$and' =>
        $searchArray
       ]
);

$groupByResults = $result->raw(function ($collection) {
    return $collection->aggregate([
        [
            '$group' => [
                "_id" => ['$substr' => ['$hs_code', 0, 2]],
                "success_num" => ['$sum' => '$total_value_usd'],
            ],
        ],
        [
            '$sort' => [
                "success_num" => -1,
            ],
        ],
        [
            '$facet' => [
                "data" => [['$skip' => 0 * 10], ['$limit' => 10]],
            ],
        ],
    ]);
});

and the $groupByResult get entire collection and groupBy and ignore first $result query. do you know how to fix it?

[GromNaN]

You cannot use Query\Builder::raw(Closure) with other methods of the query builder.

Try moving the where part into a $match stage.

$groupByResults = $result->raw(function ($collection) {
    return $collection->aggregate([
        [
            '$match' => $searchArray,
        ],
        [
            '$group' => [
                "_id" => ['$substr' => ['$hs_code', 0, 2]],
                "success_num" => ['$sum' => '$total_value_usd'],
            ],
        ],
        [
            '$sort' => [
                "success_num" => -1,
            ],
        ],
        [
            '$facet' => [
                "data" => [['$skip' => 0 * 10], ['$limit' => 10]],
            ],
        ],
    ]);
});