Pragmatism in the real world

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.

7 thoughts on “Returning a ZF2 HydratingResultSet when starting with raw SQL

  1. Thanks, 'tricks' like that might be obvious to some, but it's that sort of examples that I'd like to see more in ZF2 docs.

    BTW. this blog really helps me with my project. I wish there was more active bloggers posting on ZF2.

  2. Thanks so much for this example. I'm new to Zend2 & really struggled to find a decent low-level mapper implementation for my model. This gives me the flexibility I need, especially with complex joins to create models.

  3. $statement = $dbAdapter->createStatement($sql);

    or

    $statement = $dbAdapter->getDriver()->createStatement($sql);

    ???

  4. Would you use something like this to deal child entities from a JOIN query? Or is any kind of entity relationship an automatic "use an ORM" moment?

Comments are closed.