使用SUM的ZF2自定义数据库查询

Question

Can someone give me a hint what I am doing wrong?

public function getPaymentSumByTypeAndProject($project_id,$type) {
  $type = (int) $type;
  $project_id = (int) $project_id;
  $rowset = $this->tableGateway->select(array('total_amount' => new Expression('SUM(payment.amount)')))->where(array('type' => $type, 'project_id' => $project_id));
  $row = $rowset->toArray();
  if (!$row) {
    throw new \Exception("Busted :/");
  }
  return $rowset;
}

I want to make the same query:

SELECT SUM(amount) FROM payment WHERE type='$type' AND project_id ='$project_id';

Edit: I made small progress, i have figured out how to sum whole column

public function getPaymentSumByTypeAndProject($project_id, $type) {
    $type = (int) $type;
    $project_id = (int) $project_id;
    $resultSet = $this->tableGateway->select(function (Select $select) {
                $select->columns(array(new \Zend\Db\Sql\Expression('SUM(amount) as amount')))->where('type="0"');
            });
    return $resultSet;

Maybe someone could help me to figure out how to add condition: "WHERE type='$type' AND project_id='$project_id'" ?

Answer 1

try to make like that instead of (int) $type

 intval($type);

and

  intval($project_id);

and in your sql change your variables to

   '".$type."' 

AND

  '".$project_id."'