Zend Framework 2 TableGateway returning empty resultset

Question

I am just getting started with developing with Zend Framework 2 and I've run into a roadblock.

At it's simplest expression, the fetchAll function works:

public function fetchAll()
{
    $resultSet = $this->tableGateway->select();
    return $resultSet;
}

However, when I attempt to mix in the join in the following way:

public function fetchAll()
{
    $sql = new Sql($this->tableGateway->getAdapter());

    $select = $sql->select();
    $select->from('Entreprise')
        ->columns(array('id', 'nom', 'categorie_id'))
        ->join(array('C' => 'Categorie'), 'categorie_id = C.id', array('categorie' => 'nom'), \Zend\Db\Sql\Select::JOIN_INNER);
    $resultSet = $this->tableGateway->selectWith($select);
    return $resultSet;
}

The resulting query is:

SELECT "Entreprise"."id" AS "id", "Entreprise"."nom" AS "nom", "Entreprise"."categorie_id" AS "categorie_id", "C"."nom" AS "categorie" FROM "Entreprise" INNER JOIN "Categorie" AS "C" ON "categorie_id" = "C"."id"

The table names, columns are fine since the query doesn't throw an error, but instead simply returns an empty result set. Even removing the join and just leaving the following code doesn't help.

public function fetchAll()
{
    $sql = new Sql($this->tableGateway->getAdapter());

    $select = $sql->select();
    $select->from('Entreprise');
    $resultSet = $this->tableGateway->selectWith($select);
    return $resultSet;
}

Which makes me believe it's simply something wrong with how I get the adapter or instantiate the select, but I just can't seem to figure it out or find any solution.

Can anyone help me figure what I'm doing wrong?

Just to check things, did you run the Query to your database manually to see if this query SHOULD work? =) — Sam

Pier-Luc Gendreau Pier-Luc Gendreau · Accepted Answer · 2012-10-28T15:49:38

The following code works perfectly:

public function fetchAll()
{
    $sql = new Sql($this->tableGateway->getAdapter());

    $select = $sql->select();
    $select->from('Entreprise')
        ->columns(array('id', 'nom', 'categorie_id'))
        ->join(array('C' => 'Categorie'), 'categorie_id = C.id', array('categorie' => 'nom'), \Zend\Db\Sql\Select::JOIN_INNER);
    $resultSet = $this->tableGateway->selectWith($select);
    return $resultSet;
}

...it was a typo that had been entered by accident in another file...

Thanks Sam for the lead, ridiculously simple, but I just didn't think of trying!

Zend Framework 2 TableGateway returning empty resultset

1 Answers