title: how to join tables using tablegateway
body: <p>How to join tables in zend3 when using tableadapter? The question is not about how to join tables in general, it is about how to do this in zend and where to place the code.</p>
  
  <p>Let's say I habe a *table class for example:</p>
  
  <pre><code>namespace Import\Model;
  use RuntimeException;
  use Zend\Db\TableGateway\TableGatewayInterface;
  
  class ProjectTable
  {
      private $tableGateway='t_project';
  
      public function __construct(TableGatewayInterface $tableGateway)
      {
          $this-&gt;tableGateway = $tableGateway;
      }
  
  public function fetchAll()
  {
      return $this-&gt;tableGateway-&gt;select();
  }
  </code></pre>
  
  <p>I would like to join two tables, how can I do that, ist here the right place to do so? I tried to implement the following function:</p>
  
  <pre><code>public function Project_Unit(Unit $unit = null){
  
      $select = $this-&gt;tableGateway-&gt;getSql()-&gt;select()
      -&gt;join('t_unit', 't_project.ProjectID = t_unit.ProjectID',array('UnitID','CI_Number', 'Unitname','Shortcut','Suppliername'));       //, left
      return $this-&gt;tableGateway-&gt;selectWith($select);
  
  }
  </code></pre>
  
  <p>I didn't get an error, I got instead mixed up data. After that I tried with aliases did not work either.</p>
  
  <p>My question is, how to build this tableclass, if I need to join two tables. The tables will be project 1 -> n unit (key projectID). The second question would be how to use aliases correctly, because I have some fieldnames in both table with different data, for example each table has a column shortcut.</p>
  
  <p>EDIT: New information
   To see where the data comes from, I renamed the variables of my Exchangearray:</p>
  
  <pre><code>public function exchangeArray(array $data)
  {
      $this-&gt;PProjectID= !empty($data['ProjectID']) ? $data['ProjectID'] : null;
      $this-&gt;PCI_Number= !empty($data['CI_Number']) ? $data['CI_Number'] : null;
      $this-&gt;PDescription= !empty($data['Description']) ? $data['Description'] : null;
      $this-&gt;Projectname= !empty($data['Projectname']) ? $data['Projectname'] : null;
      $this-&gt;PShortcut= !empty($data['Shortcut']) ? $data['Shortcut'] : null;
      $this-&gt;PComponent_Class= !empty($data['Component_Class']) ? $data['Component_Class'] : null;
  }
  </code></pre>
  
  <p>Now I get an interesting output (I added the content of my dataarray also)</p>
  
  <p><a href="https://i.stack.imgur.com/5pQBw.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5pQBw.jpg" alt="output in browser"></a></p>
  
  <p>I have two columns which are named the same, that will be shortcut and ci-number, these datafields are mixed up with the same ones from the tableadapter table.</p>
  
  <p>U1 is not the shortcut of galileo it is the shortcut of the unit. The Shortcut of Galileo should be GAL. It seems like the columns which are named the same are filled by the second table (unit) but I won't get any fields from the table unit.</p>
  
  <p>EDIT: to show the addition I made out of the sugestions from jobaer:</p>
  
  <p>I edited my ProjectTable class:</p>
  
  <pre><code>class ProjectTable
  {
      //private $tableGateway='t_project';
      private $projectTableGateway;
      private $unitTableGateway;
  
  
  //  public function __construct(TableGatewayInterface $tableGateway)
  //  {
  //      $this-&gt;tableGateway = $tableGateway;
  //  }
  
  public function __construct(
          TableGatewayInterface $projectTableGateway,
          TableGatewayInterface $unitTableGateway)
  {
      $this-&gt;projectTableGateway = $projectTableGateway;
      $this-&gt;unitTableGateway = $unitTableGateway;
  }
  
  
  public function fetchAll()
  {
  
      $sqlSelect = $this-&gt;unitTableGateway-&gt;getSql()-&gt;select();
  
      /**
       * columns for the "project_table" exactly it is unit_table
       */
      $sqlSelect-&gt;columns(array('CI_Number', 'ProjectID','Unitname','Shortcut','Suppliername'));
  
      /**
       * this can take two more arguments:
       * an array of columns for "unit_table"
       * and a join type, such as "inner"
       */
  $sqlSelect-&gt;join('t_project', 't_unit.ProjectID = t_project.ProjectID');    
  
  
      /**
       * set condition based on columns
       */
      //$sqlSelect-&gt;where(array('unit_table.project_id' =&gt; $id));
  
      $resultSet = $this-&gt;unitTableGateway-&gt;selectWith($sqlSelect);
  
      return $resultSet;
  
  
  
      //return $this-&gt;tableGateway-&gt;select();
  }
  </code></pre>
  
  <p>I also edited my Module.php like suggested, here comes a snippet</p>
  
  <pre><code>    //                      Model\ProjectTable::class =&gt; function($container) {
  //                          $tableGateway = $container-&gt;get(Model\ProjectTableGateway::class);
  //                          return new Model\ProjectTable($tableGateway);
  //                      },
                          Model\ProjectTable::class =&gt; function($container) {
                              $projectTableGateway = $container-&gt;get(Model\ProjectTableGateway::class);
                              $unitTableGateway = $container-&gt;get(Model\UnitTableGateway::class);
                              return new Model\ProjectTable($projectTableGateway, $unitTableGateway);
                          },
  
                          Model\ProjectTableGateway::class =&gt; function ($container) {
                              $dbAdapter = $container-&gt;get(AdapterInterface::class);
                              $resultSetPrototype = new ResultSet();
                              $resultSetPrototype-&gt;setArrayObjectPrototype(new Model\Project());
                              return new TableGateway('t_project', $dbAdapter, null, $resultSetPrototype);
                          }
  </code></pre>
  
  <p>My controller action didn't changed:</p>
  
  <pre><code>return new ViewModel([
                          'projects' =&gt; $this-&gt;projectTable-&gt;fetchAll(),
                              ]);
  </code></pre>
  
  <p>In my view I tried to grab the columns of both tables:</p>
  
  <pre><code>foreach ($projects as $project) : 
      //  $unit=$units-&gt;fetchAllP($project-&gt;ProjectID);
  var_dump(get_object_vars($project));?&gt;
       &lt;tr&gt;
       &lt;td&gt;&lt;?= $this-&gt;escapeHtml($project-&gt;Unitname) ?&gt;&lt;/td&gt;
       &lt;td&gt;&lt;?= $this-&gt;escapeHtml($project-&gt;Projectname) ?&gt;&lt;/td&gt;
       &lt;td&gt;&lt;?= $this-&gt;escapeHtml($project-&gt;Shortcut) ?&gt;&lt;/td&gt;
       &lt;td&gt;&lt;?= $this-&gt;escapeHtml($project-&gt;CI_Number) ?&gt;&lt;/td&gt;
       &lt;td&gt;&lt;?= $this-&gt;escapeHtml($project-&gt;Description) ?&gt;&lt;/td&gt;
          &lt;td&gt;&lt;?= $this-&gt;escapeHtml($project-&gt;Component_Class) ?&gt;&lt;/td&gt;
  
  
          &lt;td&gt;
              &lt;a href="&lt;?= $this-&gt;url('project', ['action' =&gt; 'edit', 'id' =&gt; $project-&gt;ProjectID]) ?&gt;"&gt;Edit&lt;/a&gt;
              &lt;a href="&lt;?= $this-&gt;url('project', ['action' =&gt; 'delete', 'id' =&gt; $project-&gt;ProjectID]) ?&gt;"&gt;Delete&lt;/a&gt;
          &lt;/td&gt;
  
  &lt;?php endforeach; ?&gt;
  </code></pre>
  
  <p>I got an interesting output, so something is still missing. I expected to have al columns out of both joined tables.</p>
  
  <p><a href="https://i.stack.imgur.com/5pQBw.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5pQBw.jpg" alt="enter image description here"></a></p>
  
  <p><strong>EDIT2: to show next version</strong></p>
  
  <p>here is my method fetchAll()/class ProjectTable</p>
  
  <pre><code>public function fetchAll()
      {
  
          $sqlSelect = $this-&gt;unitTableGateway-&gt;getSql()-&gt;select();
          $sqlSelect-&gt;columns(array('UnitID','CI_Number', 'ProjectID','Unitname','Shortcut','Suppliername'));
          $sqlSelect-&gt;join('t_project', 't_unit.ProjectID = t_project.ProjectID', array('Project' =&gt;'Projectname','CI' =&gt;'CI_Number','PDescription' =&gt;'Description','PShortcut' =&gt;'Shortcut','PComponent' =&gt;'Component_Class','PProjectID' =&gt;'ProjectID'));
          //$sqlSelect-&gt;where(array('unit_table.project_id' =&gt; $id));
          $resultSet = $this-&gt;unitTableGateway-&gt;selectWith($sqlSelect);
  
          //return $resultSet;
          return $resultSet-&gt;toArray();
  
          //return $this-&gt;tableGateway-&gt;select();
  </code></pre>
  
  <p>Here is my viewscript:</p>
  
  <pre><code>&lt;?php 
  //var_dump(get_object_vars($projects));
  foreach ($projects as $project) : 
  //var_dump(get_object_vars($project));
  
  ?&gt;
      &lt;tr&gt;
      &lt;td&gt;&lt;?= $project['Project']?&gt;&lt;/td&gt;
      &lt;td&gt;&lt;?= $project['CI']?&gt;&lt;/td&gt;
      &lt;td&gt;&lt;?= $project['Unitname']?&gt;&lt;/td&gt;
    &lt;?php     
   endforeach; ?&gt;
  </code></pre>
  
  <p>}</p>
  
  <p>and here a new screenshot:
  <a href="https://i.stack.imgur.com/kchDK.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kchDK.jpg" alt="screenshot shows the unit, but no column out of project"></a></p>
  
  <p><strong>EDIT3: adding Unit Stuff</strong></p>
  
  <pre><code>class UnitTable
  {
      private $tableGateway='t_unit';
  
      public function __construct(TableGatewayInterface $tableGateway)
      {
          $this-&gt;tableGateway = $tableGateway;
      }
  
      public function fetchAll()
      {
          return $this-&gt;tableGateway-&gt;select();
      }
  </code></pre>
  
  <p>class Unit also:</p>
  
  <pre><code>class Unit implements InputFilterAwareInterface
  {
      public $UnitID;
      public $CI_Number;
      public $ProjectID;
      public $Unitname;
      public $Shortcut;
      public $Suppliername;
  
      private $inputFilter;
  
      public function exchangeArray(array $data)
      {
          $this-&gt;UnitID= !empty($data['UnitID']) ? $data['UnitID'] : null;
          $this-&gt;CI_Number= !empty($data['CI_Number']) ? $data['CI_Number'] : null;
          $this-&gt;ProjectID= !empty($data['ProjectID']) ? $data['ProjectID'] : null;
          $this-&gt;Unitname= !empty($data['Unitname']) ? $data['Unitname'] : null;
          $this-&gt;Shortcut= !empty($data['Shortcut']) ? $data['Shortcut'] : null;
          $this-&gt;Suppliername= !empty($data['Suppliername']) ? $data['Suppliername'] : null;
      }
  </code></pre>
  
  <p>Bcause I only have sampledata yet, a screenshot of my two tables unit and project</p>
  
  <p><a href="https://i.stack.imgur.com/bB5lg.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bB5lg.jpg" alt="databasestuff"></a></p>
  
  <p><strong>EDIT4: Factorypart of module.php</strong></p>
  
  <pre><code>public function getServiceConfig()
      {
          return [
                  'factories' =&gt; [
                          Model\ImportTable::class =&gt; function($container) {
                              $tableGateway = $container-&gt;get(Model\ImportTableGateway::class);
                              return new Model\ImportTable($tableGateway);
                          },
                          Model\ImportTableGateway::class =&gt; function ($container) {
                              $dbAdapter = $container-&gt;get(AdapterInterface::class);
                              $resultSetPrototype = new ResultSet();
                              $resultSetPrototype-&gt;setArrayObjectPrototype(new Model\Import());
                              return new TableGateway('t_dcl', $dbAdapter, null, $resultSetPrototype);
                          },
                          Model\DclimportTable::class =&gt; function($container) {
                              $tableGateway = $container-&gt;get(Model\DclimportTableGateway::class);
                              return new Model\DclimportTable($tableGateway);
                          },
                          Model\DclimportTableGateway::class =&gt; function ($container) {
                              $dbAdapter = $container-&gt;get(AdapterInterface::class);
                              $resultSetPrototype = new ResultSet();
                              $resultSetPrototype-&gt;setArrayObjectPrototype(new Model\Dclimport());
                              return new TableGateway('t_dcl_import', $dbAdapter, null, $resultSetPrototype);
                          },
                          Model\FollowupTable::class =&gt; function($container) {
                              $tableGateway = $container-&gt;get(Model\FollowupTableGateway::class);
                              return new Model\FollowupTable($tableGateway);
                          },
                          Model\FollowupTableGateway::class =&gt; function ($container) {
                              $dbAdapter = $container-&gt;get(AdapterInterface::class);
                              $resultSetPrototype = new ResultSet();
                              $resultSetPrototype-&gt;setArrayObjectPrototype(new Model\Followup());
                              return new TableGateway('t_dcl_wv', $dbAdapter, null, $resultSetPrototype);
                          },
                          Model\UnitTable::class =&gt; function($container) {
                              $tableGateway = $container-&gt;get(Model\UnitTableGateway::class);
                              return new Model\UnitTable($tableGateway);
                          },
                          Model\UnitTableGateway::class =&gt; function ($container) {
                              $dbAdapter = $container-&gt;get(AdapterInterface::class);
                              $resultSetPrototype = new ResultSet();
                              $resultSetPrototype-&gt;setArrayObjectPrototype(new Model\Unit());
                              return new TableGateway('t_unit', $dbAdapter, null, $resultSetPrototype);
                          },
  //                      Model\ProjectTable::class =&gt; function($container) {
  //                          $tableGateway = $container-&gt;get(Model\ProjectTableGateway::class);
  //                          return new Model\ProjectTable($tableGateway);
  //                      },
  
                          Model\ProjectTableGateway::class =&gt; function ($container) {
                              $dbAdapter = $container-&gt;get(AdapterInterface::class);
                              $resultSetPrototype = new ResultSet();
                              $resultSetPrototype-&gt;setArrayObjectPrototype(new Model\Project());
                              return new TableGateway('t_project', $dbAdapter, null, $resultSetPrototype);
                          },
                          Model\ProjectTable::class =&gt; function($container) {
                              $projectTableGateway = $container-&gt;get(Model\ProjectTableGateway::class);
                              $unitTableGateway = $container-&gt;get(Model\UnitTableGateway::class);
  
                              return new Model\ProjectTable($projectTableGateway, $unitTableGateway);
                          }
                          ],
                          ];
      }
  </code></pre>