Retrieve data using JOIN sql in Zend Framework 2

Question

I need to retrive data from 2 tables using join.

I have this code, but it fails with Call to undefined method Zend\Db\ResultSet\ResultSet::from():

public function getUsers($id){
    $id  = (int) $id;
    $rowset = $this->tableGateway->select()->from(array('u' => 'user'))
        ->join(array('l' => 'levels'),
            'u.user_id = l.id_user');
    $row = $rowset->current();
    if (!$row) {
        throw new \Exception("Could not find row $id");
    }
    return $row;
}

The SQL command would be:

select user.*,levels.name from user left join levels on user.user_id=levels.id_user

Thanks

UPDATE Using @Mohamad changes I get:

The table name of the provided select object must match that of the table

My UsersTable.php looks like this now:

<?php
// module/Users/src/Users/Model/UsersTable.php:
namespace Users\Model;

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Select;

class UsersTable
{
    protected $tableGateway;

    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
    }

    public function fetchAll()
    {
        $select = new Select();
        $select->from('levels');
        $select->join('user', 'levels.id=user.user_id',Select::SQL_STAR,Select::JOIN_RIGHT);
        $rowset = $this->tableGateway->selectWith ( $select );

        $resultSet = $rowset->current();
        if (!$resultSet) {
            throw new \Exception("Could not find row $id");
        }
        return $resultSet;
    }

Answer 1

i think you must pass two TableGetway to UserTable construct. you have to change Module.php look this:

public function getServiceConfig()
{
    return array(
        'factories' => array(
            'User\Model\UserTable' =>  function($sm) {
                $userTableGateway = $sm->get('UserTableGateway');
                $levelTableGateway = $sm->get('LevelTableGateway');
                $table = new UserTable($userTableGateway,$levelTableGateway);
                return $table;
            },
            'UserTableGateway' => function ($sm) {
                $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                $resultSetPrototype = new ResultSet();
                $resultSetPrototype->setArrayObjectPrototype(new User());
                return new TableGateway('user', $dbAdapter, null, $resultSetPrototype);
            },
            'LevelTableGateway' => function ($sm) {
                $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                $resultSetPrototype = new ResultSet();
                $resultSetPrototype->setArrayObjectPrototype(new Level());
                return new TableGateway('level', $dbAdapter, null, $resultSetPrototype);
            },
        ),
    );
}

then in your model:

protected $userTableGateway;
protected $levelTableGateway;

public function __construct($userTableGateway,$levelTableGateway)
{
    $this->userTableGateway = $userTableGateway;
    $this->levelTableGateway = $levelTableGateway;
}

public function fetchAll()
{
    $select = new Select();
    $select->from('levels');
    $select->join('user', 'levels.id=user.user_id',Select::SQL_STAR,Select::JOIN_RIGHT);
    $rowset = $this->levelTableGateway->selectWith ( $select );

    $resultSet = $rowset->current();
    if (!$resultSet) {
        throw new \Exception("Could not find row $id");
    }
    return $resultSet;
}

i hope helped you