CODEWITHSUNDEEP
postgresqljoinzend-framework2
aramir
aramir

Reputation: 129

join 2 tables using tablegateway in ZF2

Hello thanks for reading,

I've got two tables (using tablegateway) projet, and l_agent_projet They're correcly set, I can use them to insert, delete, update, etc my database

I'm trying to join those two tables using this function:

public function getAgentsByProject($id_projet)
{
    $sql = new Sql( $this->tableGateway->adapter ) ;
    $where = new Where() ;
    $where -> equalTo( 'l_agent_projet.id_projet', $id_projet ) ;

    $select = $sql->select() ;
    $select -> from ( $this->tableGateway->getTable() )
            -> join ( 'projet' , 'projet.id_projet = l_agent_projet.id_projet')
            -> where( $where ) ;

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

    return $result ;
}

But when I try to read the result, using this in one of my controller:

     $test = $this->InitProjectByIDAgentTable()->getAgentsByProject('1') ;
     var_dump($test) ;
     echo "<br />" ;

     foreach ( $test as $tmp1 ):
        foreach  ( $tmp1 as $tmp2 ):
            echo $tmp2 ;
            echo '+' ;
        endforeach ;
        echo "<br />" ;
     endforeach ;

Here's what I've got:

object(Zend\Db\ResultSet\ResultSet)#236 (8) { ["allowedReturnTypes":protected]=> array(2) { [0]=> string(11) "arrayobject" [1]=> string(5) "array" } ["arrayObjectPrototype":protected]=> object(Application\Model\LAgentProject)#220 (2) { ["id_agent"]=> NULL ["id_projet"]=> NULL } ["returnType":protected]=> string(11) "arrayobject" ["buffer":protected]=> NULL ["count":protected]=> int(2) ["dataSource":protected]=> object(Zend\Db\Adapter\Driver\Pdo\Result)#235 (8) { ["statementMode":protected]=> string(7) "forward" ["resource":protected]=> object(PDOStatement)#234 (1) { ["queryString"]=> string(179) "SELECT "l_agent_projet".*, "projet".* FROM "l_agent_projet" INNER JOIN "projet" ON "projet"."id_projet" = "l_agent_projet"."id_projet" WHERE "l_agent_projet"."id_projet" = :where1" } ["options":protected]=> NULL ["currentComplete":protected]=> bool(false) ["currentData":protected]=> NULL ["position":protected]=> int(-1) ["generatedValue":protected]=> NULL ["rowCount":protected]=> int(2) } ["fieldCount":protected]=> int(7) ["position":protected]=> int(0) }
1+1+
3+1+

It says ["fieldCount":protected]=> int(7) , but can only print the first two fields corresponding to the l_agent_projet table. So there is a problem.

But when I copy the sql command generated: 

SELECT "l_agent_projet".*, "projet".*
FROM "l_agent_projet"
INNER JOIN "projet" ON "projet"."id_projet" = "l_agent_projet"."id_projet" 
WHERE "l_agent_projet"."id_projet" = :'1'"

It works great and print all the fields.

I'm new to ZF2 and I don't understand what's going on here. ps: Working on postgresql with PDO:pgsql

Upvotes: 1

Views: 2626

Answers (2)

mirapole
mirapole

Reputation: 1011

To use prepareStatementForSqlObject() inside a table gateway model, you need a instance of \Zend\Db\Sql\Sql and to get this you need an adapter. 

$adapter = $this->tableGateway->getAdapter();
$sql = new Sql($adapter);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

Upvotes: 0

user2334807
user2334807

Reputation:

You need to use the following these two line:

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

instead of the line:

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

The above line will return the array of data.

Upvotes: 1

Related Questions