Generate a somewhat complex query with joins on subqueries in the FROM clause using zend framework 2 tableGateway

Question

I'm trying to generate the following query using 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

Below is what I've gotten so far

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

I just need get the left join with the sub queries to work. I can't figure out which combination of from and join methods I need to put together. I've been struggling for 3 hours now and though I'd ask for some help. Any help would greatly be appreciated

Based on the suggestions from @peterpeterson this is the new query I tried

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

Run I run it I get the following error:

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

Maybe I'm still not forming the query correctly. I'm using tableGateway. Maybe it is not possible to do this kind query via tableGateway?

Answer 1

As per the link bellow, you have to pass the third parameter:

$select::JOIN_LEFT

Not sure, you may need to save the variable:

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

and then do the joins

Sql Join zf2

Edit

it seems I had missread your questions, here is how you do it:

<?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');

So basically create another select and then pass it to the join.