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?

Duplicate? -> http://stackoverflow.com/questions/14354802/tablegateway-with-multiple-from-tables/14371056#14371056 — Crisp, Feb 11 '13 at 15:25
It's not explained and I'm not sure how to write my query. May you help me with a concrete explanation of how to join a mysql table? — Георги Банков, Feb 11 '13 at 18:18
kind of locking and fetching only the columns which are defined in your 'BlogSettings' class (see `__construct(TableGateway $tableGateway)`) — webcoder, Apr 19 '13 at 09:43

score 14 · Accepted Answer · answered Sep 22 '14 at 22:02

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

Solved it for me! Thanks a ton, I can't believe how complicated this is just for a simple join... — Michael Lumbroso, Dec 10 '14 at 23:35
You're the man, thanks a lot! I've spent last 3 hours trying to figure this one out. If I could I would give you +10 :) — Ilya Suzdalnitski, Dec 18 '14 at 13:25
Its not getting columns from joined table. Only getting columns for the first table only — Surinder, Apr 22 '18 at 01:37

score 13 · Answer 2 · edited Dec 18 '14 at 12:00

13

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;

edited Dec 18 '14 at 12:00

Ilya Suzdalnitski

52,598
51
134
168

answered Feb 11 '13 at 20:49

samsonasik

440
1
6
11

2

It seems that the problem is not with the query, but with the fetched result itself. When I run my query into the phpmyadmin it works, when I want to display the results in zf2 view, it doesn't seem to work. – Георги Банков Feb 26 '13 at 19:31
Yes @ГеоргиБанков is right. If it var_dump in the view you can see query and that query is perfect. But it the values are null and cannot be accessed from the view. Can some one please give a solution for this – Ruwantha Aug 28 '13 at 06:47
@samsonasik how is the unittest for this working? I always get `Method `Double\TableGatewayInterface\P7::getSql()` not found.` – rogaa Aug 31 '16 at 10:44

score 2 · Answer 3 · answered Apr 20 '13 at 07:24

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

score 2 · Answer 4 · answered Feb 14 '14 at 06:54

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

use Zend\Db\TableGateway\TableGateway; //use Zend\Db\Sql\Sql; use Zend\Db\Sql\Select; use Zend\Db\Sql\Where; — Dhiraj, Feb 14 '14 at 06:54

score 1 · Answer 5 · answered Feb 11 '13 at 18:57

1

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

answered Feb 11 '13 at 18:57

TobiasDeVil

67
3

And how exactly looks TableGateway instance that you put into the constructor? – TobiasDeVil Feb 11 '13 at 20:01
The whole model is in my first post. – Георги Банков Feb 12 '13 at 19:04
Is there a way to run a raw sql query into the database using TableGateway? – Георги Банков Feb 12 '13 at 19:08

score 1 · Answer 6 · answered Apr 19 '13 at 10:01

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

score 0 · Answer 7 · answered Feb 12 '14 at 09:09

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

LEFT JOIN in ZF2 using TableGateway

EDIT:

7 Answers7

Linked