CODEWITHSUNDEEP
mysqljoinzend-framework2
Георги Банков
Георги Банков

Reputation: 771

Zend Framework 2: LEFT JOIN issue

public function getInterests($userID) {
    $result = $this->tableGateway->select(function (Select $select) use ($userID) {
                $select->join('interests', 'users_interests.interest_id = interests.interest_id', array('*'), 'left');
                $where = new Where();
                $where->equalTo('user_id', $userID);
                $select->where($where);
            });

    return $result;
}

Here is my method. It simply selects all records from users_interests with user_id = $userID and joins the 'interests' table. So far, so good, but when trying to display the fetched results, the fields from the joined table just do not exist. Here is the dump of the $result:

Zend\Db\ResultSet\ResultSet Object
(
[allowedReturnTypes:protected] => Array
    (
        [0] => arrayobject
        [1] => array
    )

[arrayObjectPrototype:protected] => Object\Model\UsersInterests Object
    (
        [settings_id] => 
        [user_id] => 
        [interest_id] => 
    )

[returnType:protected] => arrayobject
[buffer:protected] => 
[count:protected] => 2
[dataSource:protected] => Zend\Db\Adapter\Driver\Pdo\Result Object
    (
        [statementMode:protected] => forward
        [resource:protected] => PDOStatement Object
            (
                [queryString] => SELECT `users_interests`.*, `interests`.* FROM `users_interests` LEFT JOIN `interests` ON `users_interests`.`interest_id` = `interests`.`interest_id` WHERE `user_id` = :where1
            )

        [options:protected] => 
        [currentComplete:protected] => 
        [currentData:protected] => 
        [position:protected] => -1
        [generatedValue:protected] => 0
        [rowCount:protected] => 2
    )

[fieldCount:protected] => 6
[position:protected] => 
)

I badly need help on this because I am supposed to finish my project until Sunday. Thanks in advance.

Upvotes: 3

Views: 4798

Answers (4)

prava
prava

Reputation: 3986

You can use the following to apply left join. $select::JOIN_LEFT instead of 'left'.

public function getInterests($userID) {
    $result = $this->tableGateway->select(function (Select $select) use ($userID) {
        $select->join('interests', 'users_interests.interest_id = interests.interest_id', array('*'), $select::JOIN_LEFT);
        $where = new Where();
        $where->equalTo('user_id', $userID);
        $select->where($where);
    });

    return $result;
}

Upvotes: 2

Diemuzi
Diemuzi

Reputation: 3527

I have written about this before and maybe it will help you as well. TableGateway with multiple FROM tables

Upvotes: 1

Andrew
Andrew

Reputation: 12809

Have you iterated over the resultset? You can see there's two matching rows:

[rowCount:protected] => 2

You have a ResultSet object, but it will not load any of the rows until requested, they are "lazy loaded" when you iterate over the object.

You can force the resultset to get them all for you:

var_dump($resultSet->toArray()); // force load all rows

or iterate over the ResultSet:

foreach($resultset as $row) {
    var_dump($row); // each row loaded on request
}

Upvotes: 1

Mayukh Roy
Mayukh Roy

Reputation: 1815

It seems you have a problem in the WHERE clause of the join. This also shows in the error here:

 [queryString] => SELECT `users_interests`.*, `interests`.* FROM `users_interests` LEFT JOIN  .
 `interests` ON `users_interests`.`interest_id` = `interests`.`interest_id` 
 WHERE `user_id` = :where1

Try this:

$select->from($this->table)
       ->join('interests', 'users_interests.interest_id = interests.interest_id',
         array('*'), 'left');   
$where = new  Where();
$where->equalTo('user_id', $userID) ;
$select->where($where);

I can not follow your code completely, like here:

$this->tableGateway->select(function (Select $select) use ($userID) {

But, here is a very nice article on this. I think, you can simplify your code a little.

Upvotes: 1

Related Questions