php - 在 Zend Framework 2 中使用 JOIN sql 检索数据

Question

我需要使用连接从 2 个表中检索数据。

我有这段代码，但它失败了 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;
}

SQL 命令为:

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

谢谢

更新使用@Mohamad 更改我得到:

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

我的 UsersTable.php 现在看起来像这样:

<?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

我认为您必须将两个 TableGetway 传递给 UserTable 结构。你必须改变 Module.php看这个:

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

然后在你的模型中:

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

希望对你有帮助