php - How to write join sub query for zend framwork 3

Question

I have sql query

SELECT Q.*, U.userFirstName , U.userLastName, U.userPhoneNumber, U.userEmailAddress,CP.contactFirstName, CP.contactLastName,M.fkAccountManagerId 
FROM sales_quote AS Q 
LEFT JOIN sales_prospact_contacts AS CP ON Q.fkProspectId=CP.fkProspectIdContact AND CP.pkContactId = (SELECT pkContactId FROM sales_prospact_contacts WHERE fkProspectIdContact = CP.fkProspectIdContact ORDER BY contactOrder ASC LIMIT 1) 
LEFT JOIN sales_prospect_account_manager M ON Q.fkProspectId = M.fkProspectId AND M.pkAccountManagerId = (SELECT MAX(pkAccountManagerId) FROM sales_prospect_account_manager WHERE fkProspectId= M.fkProspectId)
LEFT JOIN sales_users U ON U.pkUserId = M.fkAccountManagerId 

How can convert this query to SQL Abstraction (ZF3), i wrote but it it's giving sql error on sub query.

$select =  $this->DB->select();
$select -> from(['Q'=>'sales_quote']);  
$select -> join(['CP'=>'sales_prospact_contacts'], "Q.fkProspectId=CP.fkProspectIdContact AND CP.pkContactId = (SELECT pkContactId FROM sales_prospact_contacts WHERE fkProspectIdContact = CP.fkProspectIdContact ORDER BY contactOrder ASC LIMIT 1)", ['contactFirstName','contactLastName'], $select::JOIN_LEFT);
$select -> join(['M'=>'sales_prospect_account_manager'], 'Q.fkProspectId = M.fkProspectId AND M.pkAccountManagerId = (SELECT MAX(pkAccountManagerId) FROM sales_prospect_account_manager WHERE fkProspectId= M.fkProspectId)', ['fkAccountManagerId'], $select::JOIN_LEFT);
$select -> join(['U'=>'sales_users'], 'U.pkUserId = M.fkAccountManagerId', ['userFirstName', 'userLastName', 'userPhoneNumber', 'userEmailAddress'], $select::JOIN_LEFT);
$statement  = $this->DB->prepareStatementForSqlObject($select);
$results    = $statement->execute();

标签： phpmysqlsqlzend-framework3