如何在Zend 2中编写带IN条件的子查询？MySQL语句转译问题

The problem in your original code lies in how you're constructing the IN condition with a subquery—using a placeholder string combined with an Expression doesn't work as expected in Zend\Db\Sql. Instead, you should leverage the built-in in() method of the Where clause, which natively supports subqueries as the value.

Here's the corrected Zend Framework 2 code that properly replicates your original MySQL query:

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Expression;
use Zend\Db\ResultSet\ResultSet;

// Retrieve your database adapter
$adapter = $this->getAdapter();

// Step 1: Build the inner users subquery
$usersSubquery = new Select('users');
$usersSubquery->columns(['id']);
$usersSubquery->where([
    'account_id' => 452,
    'added_by' => 20694,
    'status' => 'active'
]);

// Step 2: Build the cust_info subquery with call_status calculation
$custInfoSubquery = new Select('cust_info');
$custInfoSubquery->columns([
    'billsec',
    // Use Expression to handle the IF condition correctly
    'call_status' => new Expression("IF(ANSWERED_NUM IS NULL, 'Missed', 'Answered')")
]);
// Correctly add the IN subquery condition (no placeholder needed here)
$custInfoSubquery->where->in('billsec', $usersSubquery);

// Step 3: Build the outer query to group and count results
$outerSelect = new Select();
// Use the cust_info subquery as the source table with alias 'a'
$outerSelect->from(['a' => $custInfoSubquery]);
$outerSelect->columns([
    'billsec',
    'call_status',
    'total_calls' => new Expression('COUNT(*)')
]);
// Group by the required columns to match your original query
$outerSelect->group(['a.billsec', 'a.call_status']);

// Execute the query and fetch results
$statement = $adapter->createStatement();
$outerSelect->prepareStatement($adapter, $statement);
$result = $statement->execute();

// Convert the result to a usable array (optional, adjust based on your needs)
$resultSet = new ResultSet();
$resultSet->initialize($result);
$finalResults = $resultSet->toArray();

Key Fixes Explained: #

• Proper IN Subquery Handling: Using $custInfoSubquery->where->in('billsec', $usersSubquery) tells Zend\Db to generate valid IN (SELECT ...) syntax instead of quoting the subquery as a string (which caused your original IN('') issue).
• Outer Query Structure: We explicitly create an outer select that uses the cust_info subquery as its source (aliased as a), mirroring your original query's nested structure.
• Expression for IF Condition: Wrapping the call_status logic in an Expression object ensures it's rendered correctly in the final SQL without unwanted escaping.

If you're using a TableGateway, you can simplify execution by passing the outer select directly to its select() method:

$resultSet = $this->select($outerSelect);
$finalResults = $resultSet->toArray();

内容的提问来源于stack exchange，提问作者susheel sahoo