使用zend框架2在FROM子句中生成一个有点复杂的子查询连接的查询tableGateway

Question

我想使用Zend Framework 2 tableGateway生成以下查询。

SELECT 
dfsstamp, 
dfcstamp, 
df_rstrd_flg, 
dfctid, 
dfname, 
dfloc, 
dfdesc, 
dfcuser, 
dfmuser, 
dfid AS, 
t2.dfdfid, 
ctname, 
cafldnme, 
caid, 
atid, 
atvalue, 
t1.record_count 
FROM 
(
    select * from dssdocflp 
    where dfdfid <= 1 
) as t2 
LEFT JOIN 
(
    select dfdfid, count(*) as record_count from dssdocflp 
    group by dfdfid having dfdfid > 1 
) as t1 
on t2.dfid = t1.dfdfid 
INNER JOIN dsscfgtpp ON ctid = dfctid 
INNER JOIN dsscfgatp ON cactid = ctid 
INNER JOIN dssattrp ON atcaid = caid AND atdfid = dfid 
WHERE dfdbid = '33' 
ORDER BY dfcstamp DESC, dfid ASC, caseqno ASC 

下面是我到目前为止

$select = $this->tableGateway 
        ->getSql() 
        ->select() 
        ->columns(array(
         'dfsstamp' => new Expression('date(dfsstamp)'), 
         'dfcstamp' => new Expression('date(dfcstamp)'), 
         'df_rstrd_flg', 
         'dfctid', 
         'dfname', 
         'dfloc', 
         'dfdesc', 
         'dfcuser', 
         'dfmuser', 
         'dfid', 
         'dfdfid' 
         //'record_count' 
         )) 
        ->join('dsscfgtpp', 'ctid = dfctid', array('ctname')) 
        ->join('dsscfgatp', 'cactid = ctid', array('cafldnme', 'caid')) 
        ->join('dssattrp', 'atcaid = caid AND atdfid = dfid', array('atid', 'atvalue')) 
        ->where(dfdbid = 33) 
        ->order(array('dfcstamp DESC', 'dfid ASC', 'caseqno')) 

我只需要得到左连接与子查询工作得到。我无法弄清楚需要将哪些组合和连接方法放在一起。我一直在挣扎3个小时，尽管我会寻求一些帮助。任何帮助将大大理解

基于从@peterpeterson这是新的查询我试图

$subselect_t2 = new Select(); 
    $subselect_t2->from('dssdocflp') 
     ->where(array('dfdid <= 1')); 

    $subselect_t1 = new Select(); 
    $subselect_t1->from('dssdocflp') 
     ->columns(array(
      'dfdfid', 
      'record_count' => new Expression('count(*)') 
     )) 
     ->group(array('dfdfid'))->having(array('dfdid > 1')); 

    $select = $this->tableGateway 
        ->getSql() 
        ->select() 
        ->columns(array(
         'dfcstamp', 
         'df_rstrd_flg', 
         'dfctid', 
         'dfname', 
         'dfloc', 
         'dfdesc', 
         'dfcuser', 
         'dfmuser', 
         'dfid', 
         'dfdfid', 
         't1.record_count' 
         )) 
        ->from(array('t2' => $subselect_t2)) 
        ->join(array('t1' => $subselect_t1), 't2.dfid = t1.dfdid', array(), 'left') 
        ->join('dsscfgtpp', 'ctid = dfctid', array('ctname')) 
        ->join('dsscfgatp', 'cactid = ctid', array('cafldnme', 'caid')) 
        ->join('dssattrp', 'atcaid = caid AND atdfid = dfid', array('atid', 'atvalue')) 
        ->where($this->documentWhereClause->create($searchData)) 
        ->order(array('dfcstamp DESC', 'dfid ASC', 'dfdfid DESC', 'caseqno')) 
        ->limit(500); 

运行我运行它的建议，我得到以下错误：

Since this object was created with a table and/or schema in the constructor, it is read only.

也许我仍然没有正确地形成查询。我使用tableGateway。也许这是不可能通过tableGateway做这种查询？

Answer 1

按照链接波纹管，你必须通过第三个参数：

$select::JOIN_LEFT 

不知道，你可能需要保存变量：

$select = $this->tableGateway 
        ->getSql() 
        ->select(); 

然后再做连接

Sql Join zf2

编辑

看来我已经missread你的问题，这里是你如何做到这一点：

<?php 
$subselect = new Select; 
$subselect->from('bar')->where->like('y', '%Foo%'); 
$select = new Select; 
$select->from('foo')->join(array('z' => $select39subselect), 'z.foo = bar.id'); 

所以基本上创建另一个选择，然后把它传递给加盟。