title: LEFT JOIN in ZF2 using TableGateway
body: <p>I have a table:</p>
  
  <pre><code>*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 ;*
  </code></pre>
  
  <p>And the second table:</p>
  
  <pre><code>*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 ;*
  </code></pre>
  
  <p>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 <code>blogs_settings.owner_id = users.user_id</code>. Thanks in advance. Your help is much appreciated.</p>
  
  <h1><strong>EDIT:</strong></h1>
  
  <pre><code>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-&gt;tableGateway = $tableGateway;
      $this-&gt;select = new Select();
  }
  
  public function getBlogs($field = '', $value = '') {
      $resultSet = $this-&gt;tableGateway-&gt;select(function(Select $select) {
                  $select-&gt;join('users', 'blogs_settings.owner_id = users.user_id', array('username'));
              });
  
      return $resultSet;
  }
  
  public function getBlog($blogID) {
      $id = (int) $blogID;
  
      $rowset = $this-&gt;tableGateway-&gt;select(array('blog_id' =&gt; $id));
      $row = $rowset-&gt;current();
  
      if (!$row) {
          throw new Exception('Could not find row with ID = ' . $id);
      }
  
      return $row;
  }
  
  public function addBlog(BlogsSettings $blog) {
      $data = array(
          'owner_id' =&gt; $blog-&gt;owner_id,
          'title' =&gt; $blog-&gt;title,
          'meta_description' =&gt; $blog-&gt;meta_description,
          'meta_keywords' =&gt; $blog-&gt;meta_keywords,
          'theme' =&gt; $blog-&gt;theme,
          'is_active' =&gt; $blog-&gt;is_active,
          'date_created' =&gt; $blog-&gt;date_created,
      );
  
      $this-&gt;tableGateway-&gt;insert($data);
  }
  
  public function deleteBlog($blogID) {
      return $this-&gt;tableGateway-&gt;delete(array('blog_id' =&gt; $blogID));
  }
  </code></pre>
  
  <p>}</p>
  
  <p>With this, it executes the following query:</p>
  
  <p>SELECT <code>blogs_settings</code>.*, <code>users</code>.<code>username</code> AS <code>username</code> FROM <code>blogs_settings</code> INNER JOIN <code>users</code> ON <code>blogs_settings</code>.<code>owner_id</code> = <code>users</code>.<code>user_id</code></p>
  
  <p>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?</p>
  
  <p><strong>EDIT 2</strong>
  ok, I now tried the following:</p>
  
  <pre><code>public function getBlogs() {
      $select = $this-&gt;tableGateway-&gt;getSql()-&gt;select();
      $select-&gt;columns(array('blog_id', 'interest_id', 'owner_id', 'title', 'date_created'));
      $select-&gt;join('users', 'users.user_id = blogs_settings.owner_id', array('username'), 'left');
  
      $resultSet = $this-&gt;tableGateway-&gt;selectWith($select);
  
      return $resultSet;
  }
  </code></pre>
  
  <p>the executed query is:</p>
  
  <pre><code>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`
  </code></pre>
  
  <p>When I run it into phpmyadmin, it joins the username field from the users table. When in zf2, it doesn't.</p>
  
  <p>Here's the dump of the whole object:</p>
  
  <pre><code>Zend\Db\ResultSet\ResultSet Object
  (
  [allowedReturnTypes:protected] =&gt; Array
      (
          [0] =&gt; arrayobject
          [1] =&gt; array
      )
  
  [arrayObjectPrototype:protected] =&gt; Object\Model\BlogsSettings Object
      (
          [blog_id] =&gt; 
          [interest_id] =&gt; 
          [owner_id] =&gt; 
          [title] =&gt; 
          [meta_description] =&gt; 
          [meta_keywords] =&gt; 
          [theme] =&gt; 
          [is_active] =&gt; 
          [date_created] =&gt; 
      )
  
  [returnType:protected] =&gt; arrayobject
  [buffer:protected] =&gt; 
  [count:protected] =&gt; 1
  [dataSource:protected] =&gt; Zend\Db\Adapter\Driver\Pdo\Result Object
      (
          [statementMode:protected] =&gt; forward
          [resource:protected] =&gt; PDOStatement Object
              (
                  [queryString] =&gt; 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] =&gt; 
          [currentComplete:protected] =&gt; 
          [currentData:protected] =&gt; 
          [position:protected] =&gt; -1
          [generatedValue:protected] =&gt; 0
          [rowCount:protected] =&gt; 1
      )
  
  [fieldCount:protected] =&gt; 6
  [position:protected] =&gt; 
  )
  </code></pre>
  
  <p>Up... any ideas?</p>