php - Zend Framework 2 TableGateway MAX查询

Question

几天以来，我在使用 tablegateway 和 zf2 函数进行 mysql 查询时遇到了问题。

我想获得 'reservation_spot' 列的最大值，其中 'reservation_datetime' = &reservation_datetime查询应该是这样的

SELECT MAX(`reservation_spot`) FROM `reservation` WHERE `reservation_datetime`='2015-09-30 8:00'

我已经尝试了很多方法来解决这个问题，但我做不到

这是我的功能

public function getMaxValueWhereDate($reservation_datetime)
    {
        $select = $this->tableGateway->getSql()->select();
        $select->columns(array(
            'maxValue' => new Expression('MAX(reservation_spot)')
        ));
        $select->where(array('reservation_datetime' => $reservation_datetime));

        $rowset = $this->tableGateway->selectWith($select);
        $row = $rowset->current();
        if (!$row) {
            throw new \Exception("Could not retrieve max value");
        }

        return $row;
    }

加上这个

$reservation->reservation_spot = $this->getMaxValueWhereDate($reservation_datetime);
$reservation->reservation_spot++;

在此之后，当我运行我的表单时出现错误:

Statement could not be executed (42000 - 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '55', '41', '1', 0, '2015-09-08 16:23:27')' at line 1)

我函数的查询是

SELECT MAX(reservation_spot) AS `maxValue` FROM `reservation` WHERE `reservation_date` = '2015-09-30 08:00'

@UPDATE 已解决，编辑为

public function getMaxValueWhereDate($reservation_datetime)
{
    $sql = $this->tableGateway->getSql();
    $select = $sql->select();
    $select->columns(array(
        'maxValue' => new Expression('MAX(reservation_spot)')
    ));
    $select->where(array('reservation_datetime' => $reservation_datetime));

    return $this->tableGateway->selectWith($select);
}

和

$reservation->reservation_spot = (int)$this->getMaxValueWhereDate($reservation_datetime);

Answer 1

您返回的行结果是您模型的实例，而不是您想要的值。

在您的第一个示例中，更改此行:

return $row->maxValue;

您还必须在您的模型中声明 maxValue 字段，否则它不会被映射。

如果您的模型中不需要此字段，另一种可能性是使用已经存在的字段:

$select->columns(array(
    'reservation_spot' => new Expression('MAX(reservation_spot)')
));

然后:

return $row->reservation_spot;