Returning a ZF2 HydratingResultSet when starting with raw SQL

If you’re using Zend Framework 2’s Zend\Db and want to write raw SQL that returns a HydratingResultSet, then you can do this:

use Zend\Db\Adapter\AdapterAwareInterface;
use Zend\Db\Adapter\Adapter;
use Zend\Stdlib\Hydrator\ArraySerializable;
use Zend\Db\ResultSet\HydratingResultSet;
use Zend\Db\Adapter\DriverResultInterface;

use MyEntity\MyEntity;  // some sort of entity object

class MyMapper implements AdapterAwareInterface
{
    protected $dbAdapter;

    public function fetchRowsWithValue($fieldValue)
    {
        $sql = "SELECT * FROM my_table_name
                WHERE my_field_name = ?
                ";

        $params = array(
            $fieldValue,
        );

        return $this->fetch($sql, $params);
    }

    protected function fetch($sql, $params = null)
    {
        $dbAdapter = $this->getDbAdapter();
        $statement = $dbAdapter->createStatement($sql);
        $statement->prepare();

        $result = $statement->execute($params);

        if ($result instanceof ResultInterface) {
            $hydrator     = new ArraySerializable();
            $rowPrototype = new MyEntity();
            $resultset    = new HydratingResultSet($hydrator, $rowPrototype);
            $resultset->initialize($result);

            return $resultset;
        }

        return $result;
    }

    // Don't forget to implement getDbAdapter() and setDbAdapter()!

}

When you iterate over the HydratingResultSet, you get a populated instance of MyEntity. The $hydrator instance is the object that knows how to populate your entity object. In my case, I’ve used an ArraySerializable hydrator, but there’s other options available, or you can write your own.

As an aside, if you don’t want hydrated entities, then the $result that’s returned from the statement’s execute() call is an instance of ZendDbAdapterDriverResultInterface which means that you can iterate over it and get an array for each row in the result set.