MySQL 在 ZF2 中使用 TableGateway LEFT JOIN

LEFT JOIN in ZF2 using TableGateway

提问by Георги Банков

I have a table:

我有一张桌子：

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

And the second table:

第二张表：

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

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.

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

EDIT:

编辑：

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:

有了这个，它执行以下查询：

SELECT blogs_settings.*, users.usernameAS usernameFROM blogs_settingsINNER JOIN usersON blogs_settings.owner_id= users.user_id

选择blogs_settings.*, users. usernameAS usernameFROM blogs_settingsINNER JOIN usersON blogs_settings。owner_id= users.user_id

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?

但结果集不包含来自加入的“用户”表的用户名字段。但是，当我在 phpmyadmin 中运行查询时，一切正常，并且“用户”表中的“用户名”字段已加入。有什么问题？

EDIT 2ok, I now tried the following:

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

the executed query is:

执行的查询是：

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`

When I run it into phpmyadmin, it joins the username field from the users table. When in zf2, it doesn't.

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

Here's the dump of the whole object:

这是整个对象的转储：

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?

起来……有什么想法吗？

采纳答案by Pakage

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:

添加到@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']);
}

回答by samsonasik

if you're using TableGateway, you can select join like this

如果您使用的是 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;

回答by Dhiraj

This is the exact need for both Joinand Whereclauses with tableGateway.

这正是使用tableGateway 的Join和Where子句的需要。

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

回答by Rid Zeal

You have to include username field in the BlogsSetting Model that is used as model from BlogsSettingTable (The TableGateway)

您必须在用作 BlogsSettingTable（TableGateway）模型的 BlogsSetting 模型中包含用户名字段

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

Hope this helps

希望这可以帮助

回答by webcoder

Give it a try:

试一试：

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

Add to 'getServiceConfig()' in Module.php:

添加到 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;
},

回答by TobiasDeVil

In your class inherited from AbstractTableGateway u can use Select with Closure like this:

在从 AbstractTableGateway 继承的类中，您可以像这样使用 Select 和 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;
}

回答by Ferdi Brown Kurniawan

since the OP hasn't accepted any answer, I'll try to give the solution. I face the same solution as the OP states and the only way to fix it is by adding this line to the model class (in this case this might be 'Blogsetttings.php').

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

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

you should add above line to the exchangeArray() method. Hope it helps

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