使用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表中选择所有字段，并仅从users表中加入'username'字段.提前致谢.非常感谢您的帮助.
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 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));
}
}
以此，它执行以下查询:
With this, it executes the following query:
选择blogs_settings.*，users.username AS username从blogs_settings内部联接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
，但resultSet不包含联接的用户"表中的用户名字段.但是，当我在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中时，它会加入users表中的username字段.在zf2中时，不是.
When I run it into phpmyadmin, it joins the username field from the users table. When in zf2, it doesn't.
这是整个对象的转储:
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] =>
)
上...有什么想法吗?
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屋！
