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.
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();
}
$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');
