zf3 db - fetchall returning duplicated rows

Question

I'm having a issue with zend-db, when I use fetchall(), the rows return duplicated, for example:

{
    "title"         : "Florianópolis",
    "neighborhoods" : [ {
                            "0"      : "6",
                            "1"      : "Abraão",
                            "name"   : "Abraão",
                            "number" : "6"
                        },
                        {
                            "0"      : "9",
                            "1"      : "Açores",
                            "name"   : "Açores",
                            "number" : "9"
                        },
                        {
                            "0"      : "5",
                            "1"      : "Agronômica",
                            "name"   : "Agronômica",
                            "number" : "5"
                        },

As you can see, I'm receiving twice their names and numbers. Here's the method that fetches the information :

public function getNeighborhoodsByCity( $city )
{
    try
    {
        $this -> sql = new Sql( $this -> adapter );
        $select = $this -> sql -> select();
        $select -> from( $this -> table );
        $select -> columns( array( 'number',
                                   'name' ) );
        $select -> where( "city = '{$city}'" );
        $select -> order( "name" );
        $statement = $this -> sql -> prepareStatementForSqlObject( $select );
        $result = $statement -> execute() -> getResource() -> fetchall();
    }
    catch ( \Exception $e )
    {
        throw new \Exception ( 'ERROR : ' . $e -> getMessage() );
    }

    return $result;
}

I would like to know what is generating the 0 and 1. Btw, I'm learning Zf3, so any tips to improve this code are more than welcome! Thanks in advance!

score 0 · Accepted Answer · answered May 31 '17 at 23:17

Honestly, I do not know the reason for the error, but you can solve it like this:

Install the Hydrator packages (if is zf3):

$ composer require zendframework/zend-hydrator

Replace your code :

$statement = $this -> sql -> prepareStatementForSqlObject( $select );
$result = $statement -> execute() -> getResource() -> fetchall();

To:

$statement = $sql->prepareStatementForSqlObject ( $select );
$result = $statement->execute ();

if (! $result instanceof ResultInterface || ! $result->isQueryResult ()) {
    return [ ];
}

$resultSet = new HydratingResultSet ( new ArraySerializable(), new ArrayObject() );
$resultSet->initialize ( $result );
return $resultSet;

Include de packages in header file:

use Zend\Db\ResultSet\HydratingResultSet;
use Zend\Stdlib\ArrayObject;
use Zend\Hydrator\ArraySerializable;

Reference:

zend-hydrator

hydrator Documentation

SQL Abstraction and Object Hydration

zf3 db - fetchall returning duplicated rows

1 Answers1