Zend Framework 2 join the same table multiple times using alias

Question

I'm struggling to implement multiple left JOINs into ZF2. I've got first one working, but when I add another one, it doesn't work.

This is the working SQL query which I should implement into zf2:

SELECT 
    ac.ctr_id AS ctr_id,
    ac.ctr_no AS ctr_no,
    ac.ctr_marketer AS marketer,
    ac.ctr_manager AS manager,
    ac.ctr_recruiter AS recruiter,
    l1.emp_realname AS marketer,
    l2.emp_realname AS co_recruiter_manager,
    l3.emp_realname AS recruiter
FROM
    allcontracts AS ac
        JOIN
    lstemployees AS le ON ac.ctr_recruiter = le.emp_id
        LEFT JOIN
    lstemployees AS l2 ON ac.ctr_manager = l2.emp_id
        LEFT JOIN
    lstemployees AS l3 ON ac.ctr_recruiter = l3.emp_id
        LEFT JOIN
    lstemployees AS l1 ON ac.ctr_marketer = l1.emp_id

from my model:

.....

      $where = new Where();
      $this->table='allcontracts';

      $select = new Select($this->table);
      $select->columns(array('*')); // TODO add columns from allcontracts table

   // This one works
    $select->join('lstemployees', 'allcontracts.ctr_recruiter = lstemployees.emp_id');

    // When I add this one below it doesn't work
    $select->join(array('l2' => 'lstemployees'), 'allcontracts.ctr_manager = l2.emp_id', array('*'), 'left');


    $where->like('ctr_no', '%LT');

    if($id!='' && $id > 0)
        $where->equalTo('ctr_id', $id);

    $select->where($where);
    $resultSet = $this->selectWith($select);

......

Any idea?

Answer 1

Here is what I propose:

<?php
use Zend\Db\Sql\Select;

$select = new Select();
$select->columns([Select::SQL_STAR])
   ->from(['ac' => 'allcontracts '])
   ->join(['le' => 'lstemployees'], 'ac.ctr_recruiter = le.emp_id', [])
   ->join(['l1' => 'lstemployees'], 'ac.ctr_marketer = l1.emp_id', ['marketer' => 'emp_realname'], Select::JOIN_LEFT)
   ->join(['l2' => 'lstemployees'], 'ac.ctr_manager = l2.emp_id', ['co_recruiter_manager' => 'emp_realname'], Select::JOIN_LEFT)
   ->join(['l3' => 'lstemployees'], 'ac.ctr_recruiter = l3.emp_id', ['recruiter' => 'emp_realname'], Select::JOIN_LEFT);

// to debug your query     
die($select->getSqlString($dbAdapter->getPlatform()));
// if you haven't $dbAdapter, replace by null but the result will be quoted.