题 使用TableGateway在ZF2中左键连接


我有一张桌子:

*CREATE TABLE IF NOT EXISTS `blogs_settings` (
  `blog_id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `meta_description` text NOT NULL,
  `meta_keywords` text NOT NULL,
  `theme` varchar(25) NOT NULL DEFAULT 'default',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `date_created` int(11) NOT NULL,

  PRIMARY KEY (`blog_id`),
  KEY `owner_id` (`owner_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;*

第二个表:

*CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(128) NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `birthday` date NOT NULL,
  `avatar_id` int(11) DEFAULT NULL,
  `user_level` tinyint(1) NOT NULL DEFAULT '1',
  `date_registered` int(11) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `is_banned` tinyint(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`user_id`),
  KEY `is_active` (`is_active`),
  KEY `user_level` (`user_level`),
  KEY `is_banned` (`is_banned`),
  KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;*

如何从blogs_settings表中选择所有字段,并使用ZF2中的TableGateway仅加入users表中的“username”字段, blogs_settings.owner_id = users.user_id。提前致谢。非常感谢您的帮助。

编辑:

namespace Object\Model;

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

class BlogsSettingsTable {

protected $tableGateway;
protected $select;

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

public function getBlogs($field = '', $value = '') {
    $resultSet = $this->tableGateway->select(function(Select $select) {
                $select->join('users', 'blogs_settings.owner_id = users.user_id', array('username'));
            });

    return $resultSet;
}

public function getBlog($blogID) {
    $id = (int) $blogID;

    $rowset = $this->tableGateway->select(array('blog_id' => $id));
    $row = $rowset->current();

    if (!$row) {
        throw new Exception('Could not find row with ID = ' . $id);
    }

    return $row;
}

public function addBlog(BlogsSettings $blog) {
    $data = array(
        'owner_id' => $blog->owner_id,
        'title' => $blog->title,
        'meta_description' => $blog->meta_description,
        'meta_keywords' => $blog->meta_keywords,
        'theme' => $blog->theme,
        'is_active' => $blog->is_active,
        'date_created' => $blog->date_created,
    );

    $this->tableGateway->insert($data);
}

public function deleteBlog($blogID) {
    return $this->tableGateway->delete(array('blog_id' => $blogID));
}

}

有了它,它执行以下查询:

选择 blogs_settings。*, usersusername 如 username 从 blogs_settings 内部联接 users 上 blogs_settingsowner_id = usersuser_id

但是resultSet不包含已加入的“用户”表中的用户名字段。但是,当我在phpmyadmin中运行查询时,一切正常,我加入了'users'表中的'username'字段。有什么问题?

编辑2 好的,我现在尝试了以下内容:

public function getBlogs() {
    $select = $this->tableGateway->getSql()->select();
    $select->columns(array('blog_id', 'interest_id', 'owner_id', 'title', 'date_created'));
    $select->join('users', 'users.user_id = blogs_settings.owner_id', array('username'), 'left');

    $resultSet = $this->tableGateway->selectWith($select);

    return $resultSet;
}

执行的查询是:

SELECT `blogs_settings`.`blog_id` AS `blog_id`, `blogs_settings`.`interest_id` AS `interest_id`, `blogs_settings`.`owner_id` AS `owner_id`, `blogs_settings`.`title` AS `title`, `blogs_settings`.`date_created` AS `date_created`, `users`.`username` AS `username` FROM `blogs_settings` LEFT JOIN `users` ON `users`.`user_id` = `blogs_settings`.`owner_id`

当我将它运行到phpmyadmin时,它会加入users表中的用户名字段。在zf2中,它没有。

这是整个对象的转储:

Zend\Db\ResultSet\ResultSet Object
(
[allowedReturnTypes:protected] => Array
    (
        [0] => arrayobject
        [1] => array
    )

[arrayObjectPrototype:protected] => Object\Model\BlogsSettings Object
    (
        [blog_id] => 
        [interest_id] => 
        [owner_id] => 
        [title] => 
        [meta_description] => 
        [meta_keywords] => 
        [theme] => 
        [is_active] => 
        [date_created] => 
    )

[returnType:protected] => arrayobject
[buffer:protected] => 
[count:protected] => 1
[dataSource:protected] => Zend\Db\Adapter\Driver\Pdo\Result Object
    (
        [statementMode:protected] => forward
        [resource:protected] => PDOStatement Object
            (
                [queryString] => SELECT `blogs_settings`.`blog_id` AS `blog_id`, `blogs_settings`.`interest_id` AS `interest_id`, `blogs_settings`.`owner_id` AS `owner_id`, `blogs_settings`.`title` AS `title`, `blogs_settings`.`date_created` AS `date_created`, `users`.`username` AS `username` FROM `blogs_settings` LEFT JOIN `users` ON `users`.`user_id` = `blogs_settings`.`owner_id`
            )

        [options:protected] => 
        [currentComplete:protected] => 
        [currentData:protected] => 
        [position:protected] => -1
        [generatedValue:protected] => 0
        [rowCount:protected] => 1
    )

[fieldCount:protected] => 6
[position:protected] => 
)

起来......任何想法?


17
2018-02-11 14:07


起源


重复? - > stackoverflow.com/questions/14354802/... - Crisp
它没有解释,我不知道如何编写我的查询。您能否帮我详细解释如何加入mysql表? - Георги Банков
只能锁定和获取“BlogSettings”类中定义的列(请参阅 __construct(TableGateway $tableGateway)) - webcoder


答案:


添加@samsonasik的答案并在其评论中解决问题。您将无法从该语句返回的内容中获取连接值。该语句返回不具有连接行的模型对象。您需要将它作为SQL执行,并将其作为原始SQL进行准备,并将每个结果行作为数组而不是对象返回:

$sqlSelect = $this->tableGateway->getSql()->select();
$sqlSelect->columns(array('column_name_yourtable'));
$sqlSelect->join('othertable', 'othertable.id = yourtable.id', array('column_name_othertable'), 'left');

$statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
$resultSet = $statement->execute();
return $resultSet;

//then in your controller or view:

foreach($resultSet as $row){
    print_r($row['column_name_yourtable']);
    print_r($row['column_name_othertable']);
}

13
2017-09-22 22:02



为我解决了!非常感谢,我无法相信这对于简单的加入来说有多复杂...... - Michael Lumbroso
没问题@MichaelLumbroso :) - Pakage
你是男人,非常感谢!我花了3个小时试图弄清楚这个。如果可以,我会给你+10 :) - Ilya Suzdalnitski
它没有从连接表中获取列。仅获取第一个表的列 - Rahul


如果你正在使用TableGateway,你可以选择这样的连接

$sqlSelect = $this->tableGateway->getSql()->select();
$sqlSelect->columns(array('column_name'));
$sqlSelect->join('othertable', 'othertable.id = yourtable.id', array(), 'left');

$resultSet = $this->tableGateway->selectWith($sqlSelect);
return $resultSet;

13
2018-02-11 20:49



似乎问题不在于查询,而在于获取结果本身。当我运行我的查询到phpmyadmin它工作,当我想在zf2视图中显示结果时,它似乎不起作用。 - Георги Банков
是的,ГеоргиБанков是对的。如果在视图中使用var_dump,您可以看到查询,并且该查询是完美的。但它的值为null,无法从视图中访问。有人可以为此提供解决方案 - Ruwantha
@samsonasik这个工作的单位测试怎么样?我总是得到 Method Double \ TableGatewayInterface \ P7 :: getSql()`找不到 - rogaa


您必须在BlogsSetting模型中包含用户名字段,该模型用作BlogsSettingTable(TableGateway)中的模型

class BlogsSetting {
    public $blog_id;
    public $interest_id;
    public $owner_id;
    public $title;
    public $meta_description;
    public $meta_keywords;
    public $theme;
    public $is_active;
    public $date_created;
    public $username;

    public function exchangeArray($data)
    {
        // Create exchangeArray
    }
}

希望这可以帮助


2
2018-04-20 07:24





这是两者的确切需要 加入 和 哪里 条款 tableGateway

public function getEmployeefunctionDetails($empFunctionId) {
    $empFunctionId = ( int ) $empFunctionId;
    //echo '<pre>'; print_r($this->tableGateway->getTable()); exit;
    $where = new Where();
    $where->equalTo('FUNCTION_ID', $empFunctionId);

    $sqlSelect = $this->tableGateway->getSql()->select()->where($where);    

    $sqlSelect->columns(array('FUNCTION_ID'));
    $sqlSelect->join('DEPARTMENTS', 'DEPARTMENTS.DEPARTMENT_ID = EMPLOYEE_FUNCTIONS.DEPARTMENT_ID', array('DEPARTMENT_ID','DEPARTMENT_NAME'), 'inner');
    $sqlSelect->join('ROLES', 'ROLES.ROLE_ID = EMPLOYEE_FUNCTIONS.ROLE_ID', array('ROLE_ID','ROLE_NAME'), 'inner');

    //echo $sqlSelect->getSqlString(); exit;
    $resultSet = $this->tableGateway->selectWith($sqlSelect);

    if (! $resultSet) {
        throw new \Exception ( "Could not find row $empFunctionId" );
    }
    return $resultSet->toArray();
}

2
2018-02-14 06:54



使用Zend \ Db \ TableGateway \ TableGateway; //使用Zend \ Db \ Sql \ Sql;使用Zend \ Db \ Sql \ Select;使用Zend \ Db \ Sql \ Where; - Dhiraj


在继承自AbstractTableGateway的类中,您可以使用Select with Closure,如下所示:

use Zend\Db\Sql\Select;
...
public function getAllBlockSettings()
{
    $resultSet = $this->select(function(Select $select) {
        $select->join('users', 'blogs_settings.owner_id = users.user_id', array('username'));
    });

    return $resultSet;
}

1
2018-02-11 18:57



你如何看待构建器中的TableGateway实例? - TobiasDeVil
整个模型是我的第一篇文章。 - Георги Банков
有没有办法使用TableGateway将原始SQL查询运行到数据库中? - Георги Банков


试一试:

namespace Object\Model;

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

class BlogsSettingsTbl extends AbstractTableGateway {
    public function __construct($adapter) {
        $this->table = 'blogs_settings';
        $this->adapter = $adapter;
        $this->initialize();
    }

    public function fetchAll() {
        $where = array(); // If have any criteria
        $result = $this->select(function (Select $select) use ($where) {
                    $select->join('users', 'blogs_settings.owner_id = users.user_id', array('username'));
                    //echo $select->getSqlString(); // see the sql query
                });
        return $result;
    }
}

在Module.php中添加到'getServiceConfig()':

'Object\Model\BlogsSettingsTbl' => function($sm) {
    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
    $table = new BlogsSettingsTbl($dbAdapter); // <-- also add this to 'USE' at top
    return $table;
},

1
2018-04-19 10:01





由于OP没有接受任何答案,我会尝试给出解决方案。 我面临与OP状态相同的解决方案,解决它的唯一方法是将此行添加到模型类中(在这种情况下,这可能是'Blogsetttings.php')。

$this->username= (!empty($data['username'])) ? $data['username'] : null;

你应该将以上行添加到exchangeArray()方法中。 希望能帮助到你


0
2018-02-12 09:09