How to use having() in tableGateway in ZF2

Question

How to use having() clause in ZF2?

There is almost no examples on the web how to prepare correct select object with having.

I have query like:

SELECT root_schema_id as `schema_id`
FROM `standard_specific_root_schemas`
WHERE `vehicle_id` IN (".implode(",",$vehiclesIds).")
GROUP BY `schema_id`, rootSubGroup_id HAVING count(*)=".$noOfVehicles

And I'm trying to run it in ZF2:

public function getVehicleWithinCommonRootSubgroupInSpecific($vehiclesIds)
{
    $where = new Where();
    $where->in('vehicle_id', $vehiclesIds);

    $having = new Having('count(*) = '.count($vehiclesIds));

    $rowset = $this->tableGateway->select(function (Select $select) use ($where, $having) {
        $select
            ->where($where)
            ->having($having);
    });

    if (!$rowset) {
        throw new \Exception("Could not find schemas for group $groupId");
    }
    return $rowset;
}

Of course that part in ZF2 is not finished yet as I wanted to check if it's working first. I've tried few ways of providing params to having method but everything generates errors.

Help please, I'm desperate...

Answer 1

I cannot test your query, but can try and reproduce the query you need.

I adjusted the having to use ->expression() instead of a variable via the construct. I also added the group statement.

To view the query I added a var_dump:

    $where = new \Zend\Db\Sql\Where();
    $where->in('vehicle_id', $vehiclesIds);

    $having = new \Zend\Db\Sql\Having();
    $having->expression('count(*) = ?', count($vehiclesIds));

    $rowset = $this->tableGateway->select(function (\Zend\Db\Sql\Select $select) use ($where, $having) {
        $select
            ->where($where)
            ->group(array('schema_id', 'rootSubGroup_id'))
            ->having($having);

        var_dump( $select->getSqlString() );
    });

Let me know if this helps.

---

To circumvent the error mentioned in the comments you would have to do something like below:

$sql = $this->tableGateway->getSql();

$select = $sql->select();

$where = new \Zend\Db\Sql\Where();
$where->in('vehicle_id', $vehiclesIds);

$having = new \Zend\Db\Sql\Having();
$having->expression('count(*) = ?', count($vehiclesIds));

$select
    ->where($where)
    ->group(array('schema_id', 'rootSubGroup_id'))
    ->having($having);

$preparedQuery = $sql->prepareStatementForSqlObject($select);

var_dump( $preparedQuery->getSql() );

However, if I'm right, the tableGateway does this for you so the error should go away once you start using the select to query the database.

Also, you can use the above to do that too, just replace this:

$preparedQuery = $sql->prepareStatementForSqlObject($select);

var_dump( $preparedQuery->getSql() );

With:

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