CODEWITHSUNDEEP
sqljoinzend-framework2
zwolu
zwolu

Reputation: 41

Zend Framework 2 Zend\Db\Select how to join another Select

How can I make a join with another Select Object? I need make a query like this 

$select->join(array('alias' => $subSelect), 'on clause', 'array('*'), 'left');

Something like this was possible in ZF1.x.

In ZF2 first argument of join method is name of table or array with alias but i can't put there \Select. Even if i put here 

$select->getSqlString($this->adapter->platform);

string is quoted, and query is not valid. Join doesn't add brackets too. It's really confusing.

Upvotes: 1

Views: 2125

Answers (2)

csabaD
csabaD

Reputation: 1

$subSelect=new Select(array('A'=>new TableIdentifier('target','CAT')));
    $subSelect->columns(array('fk_w','TI'=>'id','mtarget_f'=>new Expression('sum(F)'),
                'mcost'=>new Expression('round(sum(F*D.mcost),2)')))
    ->join(array('B'=>new TableIdentifier('item','CAT')),'B.id=A.fk_w',
            array())
                    ->join(array('D'=>new TableIdentifier('rate','CAT')),'A.dat=D.year and D.fk_c=B.CCODE and D.fk_b=A.fk_b',
                            array())
                            ->where->equalTo('Year', $year);



    $select=new Select(array('A'=>new TableIdentifier('item','CAT')));
    $select->columns(array('WID'=>'id','AID'=>'AID',
                'item'=>'ITEM_ID','Wtitle'=>'WTITLE'))
    ->join(array('B'=>new TableIdentifier('cn','CAT')),'B.id=A.CCODE',
            array('Ct'=>'name'))
    ->join(array('S'=>$subSelect),'S.fk_w=A.id',array('Mtarget'=>'mcost',
                    'MTarget'=>'m_target',                  
                    'TID'),'left');

Upvotes: 0

Diemuzi
Diemuzi

Reputation: 3527

Maybe this will help you. This is a working example I have in my TableGateway:

public function Profile($params)
{
    $result = $this->select(function (Select $select) use ($params) {
        $select
            ->columns(array(
                'ipaddress_type',
                'ipaddress',
                'domain'
            ))
            ->join('product_hosting_profile', 'product_hosting_profile.productid = webaccount.productid', array(
                'servers',
                'services'
            ))
            ->where(array(
                $this->adapter->getPlatform()->quoteIdentifierChain(array('webaccount', 'accountid')) . ' = ' . $this->adapter->getPlatform()->quoteValue($params['accountid']),
                $this->adapter->getPlatform()->quoteIdentifierChain(array('webaccount', 'productid')) . ' = ' . $this->adapter->getPlatform()->quoteValue($params['productid']),
                $this->adapter->getPlatform()->quoteIdentifierChain(array('webaccount', 'webaccountid')). ' = ' . $this->adapter->getPlatform()->quoteValue($params['webaccountid'])
            ))
            ->limit(1);
    });

    return $result->current();
}

Upvotes: 1

Related Questions