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

Mohammad Mehdi Habibi · Accepted Answer · 2013-11-09 18:38:12Z

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

edited Nov 9, 2013 at 18:38

answered Nov 9, 2013 at 11:03

Mohammad Mehdi Habibi

1,6231 gold badge16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

codiaf Over a year ago

I had to modify the sql command as I was doing it wrong, instead from user is from levels join user right join on levels.id=user.user_id but now I get: The table name of the provided select object must match that of the table and both levels and user are existing tables

Mohammad Mehdi Habibi Over a year ago

follow this: framework.zend.com/manual/2.1/en/user-guide/…

codiaf Over a year ago

I've updated the post above. That was the tutorial I was following. The fetchAll function just returns all the fields in a single table but I was trying to get also from another table with join

codiaf Over a year ago

Ah ok, I have

public function indexAction()     {         return new ViewModel(array(             'user' => $this->getUsersTable()->fetchAll(),         ));     }

in my UsersController.php so that is the problem right?

codiaf Over a year ago

I was removing errors, and now I think just one left remains in how I manage to show the data in index.phtml. I get Trying to get property of non-object in C:\wamp\www\ticketprojects\module\Users\view\users\users\index.phtml on line 21. In my indexAction I have 'users' => $this->getUsersTable()->fetchAll(), is this OK?

|

Collectives™ on Stack Overflow

Retrieve data using JOIN sql in Zend Framework 2

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related