LEFT JOIN in ZF2 using TableGateway

Question

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.

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.username AS username FROM blogs_settings INNER JOIN users ON 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?

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

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.

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?

Answer 1

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

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

Answer 2

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']);
}

Answer 3

This is the exact need for both Join and Where clauses with 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();
}

Answer 4

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').

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

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

Answer 5

You have to include username field in the BlogsSetting Model that is used as model from BlogsSettingTable (The 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
    }
}

Hope this helps

Answer 6

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:

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

Answer 7

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

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