使用 TableGateway 在 ZF2 中左连接 [英] LEFT JOIN in ZF2 using TableGateway
问题描述
我有一张桌子:
*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.owner_id = users.user_id 上使用 ZF2 中的 TableGateway 从 blogs_settings 表中选择所有字段并仅连接用户表中的用户名"字段.提前致谢.非常感谢您的帮助.
How may I select all the fields from blogs_settings table and join only the 'username' field from the users table using TableGateway in ZF2, on blogs_settings.owner_id = users.user_id. Thanks in advance. Your help is much appreciated.
namespace ObjectModel;
use ZendDbTableGatewayTableGateway;
use ZendDbSqlSelect;
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));
}
}
这样，它执行以下查询:
With this, it executes the following query:
SELECT blogs_settings.*, users.username AS username FROM blogs_settingsINNER JOIN users ON blogs_settings.owner_id = users.user_id
SELECT blogs_settings.*, users.username AS username FROM blogs_settings INNER JOIN users ON blogs_settings.owner_id = users.user_id
但结果集不包含已加入的用户"表中的用户名字段.但是，当我在 phpmyadmin 中运行查询时，一切正常，并且我已经加入了用户"表中的用户名"字段.有什么问题?
but the resultSet does not contain the username field from the joined 'users' table. However, when I run the query in phpmyadmin, everything is okay and I have the 'username' field from the 'users' table joined. What's the problem?
编辑 2好的，我现在尝试了以下方法:
EDIT 2 ok, I now tried the following:
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 中运行它时，它会加入用户表中的用户名字段.在 zf2 中，它不会.
When I run it into phpmyadmin, it joins the username field from the users table. When in zf2, it doesn't.
这是整个对象的转储:
ZendDbResultSetResultSet Object
(
[allowedReturnTypes:protected] => Array
(
[0] => arrayobject
[1] => array
)
[arrayObjectPrototype:protected] => ObjectModelBlogsSettings 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] => ZendDbAdapterDriverPdoResult 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] =>
)
向上...有什么想法吗?
Up... any ideas?
推荐答案
添加到@samsonasik 的答案并解决其评论中的问题.您将无法从该语句返回的内容中获取连接值.该语句返回没有连接行的模型对象.您需要在将其准备为原始 SQL 并将每个结果行作为数组而不是对象返回给您的级别上作为 SQL 执行它:
Adding to @samsonasik's answer and addressing the issues in its comments. You won't be able to get the joined values out of what is returned from that statement. That statement returns the model object which won't have the joined rows. You'll need to execute it as SQL at a level which will prepare it as raw SQL and return you each resulting row as an array rather than an object:
$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']);
}
这篇关于使用 TableGateway 在 ZF2 中左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
