php - 在 ZF2 查询中求和整数值

Question

我有一个查询，它基本上挑选出我的 mysql 表中的所有点。我正在使用 Zend Framework 2，查询基本上如下所示:

public function GetFiveUsersWithHighestPoints($brandId,$startDate, $endDate)
    {
        $select = new Select();
        $select->from(array("h" => $this->table));
        $select->columns(array(
            "id",
            "userId",
            "type",
            "points",
            "missionId",
            "rewardId",
            "brandId",
            "goodsId",
            "time",
        ));
        $select->order("h.points desc");
        $where = new Where();
        $select->join(array("m" => "missions"), "h.missionId = m.id", array("missionId" => "id", "missionName" => "title", "missionAction" => "action"), Select::JOIN_LEFT);
        if (!empty($journeyId)) {
            $where->equalTo("m.journeyId", $journeyId);
        } else {
            $select->join(array("r" => "rewards"), "h.rewardId = r.id", array("rewardId" => "id", "rewardName" => "name"), Select::JOIN_LEFT);
        }
        $where->notEqualTo("h.points", 0);
        $where->equalTo("h.brandId", $brandId);
        $where->between("h.time", $startDate, $endDate);
        //分页代码块。固定用法
        $select->where($where);
        return $this->historyTable->selectWith($select)->toArray();
    }

现在我想做的是所有的点都按用户 ID 分组，这基本上对应于每个用户在给定时间范围内获得了多少点的事实......我怎样才能对所有点进行求和每个 UserId 的点数，所以我得到这样的东西:

1. UserId 26 => 100 积分
2. UserId 27 => 200 积分

等等等

我对在 Zend Framework 2 中编写查询还很陌生，我不确定下一步该做什么...

Answer 1

如何添加带有 GROUP BY 的 SUM() 列，例如:

use Zend\Db\Sql\Expression;

// Include a SUM() expression
$select->cols([

    // Include existing columns
    // etc...

    // Add a SUM col
    'pointsCount' => new Expression('SUM(h.points)'),

]);

// Add a GROUP BY
$select->group('h.userId');

然后，在名称 pointsCount 下的每个结果行中，您应该可以访问您寻求的每个用户的点数总和。