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