Zend Framework 2: LEFT JOIN issue

Question

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.

Answer 1

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;
}

Answer 2

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.