GROUP BY AND SUM queries-CodePudding

I can't find working solution for this.

I wanna for example make the query like (in SQL):

"SELECT SUM(visits),* FROM db_name WHERE name = 'John' GROUP BY date

When I use code like:

$data=$collection->aggregate([
   { $match: { name: "John" } },
   { $group: { _id: "$cust_id", total: { $sum: "$visits" } } },
]);

Parser send me an error:

Parse error: syntax error, unexpected '{', expecting ']' in /usr/home

There is not actual documentation for queries like this in PHP.

CodePudding user response：

You're trying to use JavaScript object syntax for a PHP associative array.

PHP uses [] around the array and key => value for the elements. String constants need to be quoted.

$data=$collection->aggregate([
    [ $match => [ "name" => "John" ] ],
    [ $group => [ "_id" => "$cust_id", "total" => [ $sum => "$visits" ] ] ],
]);

CodePudding user response：

Ok, I try to show all rows by column DATE in format like: (sum of visits people with first name John, day by day)

In SQL SELECT SUM(visits),* FROM db_name WHERE name = 'John' GROUP BY date

DATE: 2022-01-01 VISITS: 4
DATE: 2022-01-02 VISITS: 1
DATE: 2022-01-03 VISITS: 0
DATE: 2022-01-04 VISITS: 7
DATE: 2022-01-05 VISITS: 12

This is my current code:

$test = $collection->aggregate([
    [ '$match' => [ "NAME" => "John" ]],
    [ '$group' => [ "_id" => $DATE, "VISITS" => ['$sum'=>1],]]
]);

foreach ($test as $row){
echo 'DATE:'.$row['DATE'].' VISITS:'.$row['VISITS'].'<br>';
}

But I'm stuck without dates and with only one VISITS value. What I do wrong?