Zend Framework 2如何使用别名多次连接同一张表

Question

我很难在ZF2中实现多个左连接。我已经让第一个左连接工作了，但当我添加另一个左连接时，它就无法运行。

以下是我应该在ZF2中实现的有效SQL查询:

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

来自我的模型：

.....

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

......

有什么想法吗？

- Blackcoat77

Answer 1

以下是我的建议：

<?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.

- Alain Pomirol

---

谢谢Alain。我遇到了这个错误 Since this object was created with a table and/or schema in the constructor, it is read only. 这里有解释 链接。 - Blackcoat77