CakePHP 2模型查询构建器与原生SQL返回结果数量差异排查

问题背景 #

维护遗留CakePHP 2.10项目时，通过Application模型关联多表获取全量数据，发现同一时间范围内，查询构建器返回9768条记录，而逻辑一致的原生SQL返回23904条（原生结果准确），需排查差异原因。

排查方向 #

• JOIN类型不匹配：原生SQL使用LEFT JOIN，但CakePHP查询构建器的joins数组默认生成INNER JOIN。这会过滤掉主表中关联表无匹配数据的记录，比如某条Application没有对应的ApplicationResponse、ApplicationPayday或Commission时，查询构建器会直接排除该记录，而原生SQL会保留并将关联字段设为NULL，导致行数减少。
• 表名前缀不一致：原生SQL中的表名带tlp_前缀（如tlp_applications），但查询构建器的joins配置用的是无前缀表名（application_responses）。若项目配置了表前缀，CakePHP可能自动添加前缀，但如果配置错误或手动写表名时遗漏前缀，会导致关联表错误，过滤掉部分数据。
• 自动合并重复主记录：CakePHP的find('all')会自动合并主表（Application）id相同的记录。多表关联产生笛卡尔积时（比如一个Application对应多条ApplicationPayday或Commission），查询构建器会将这些结果合并为一个Application数组，而原生SQL会返回所有笛卡尔积行，这是行数差异的常见原因。
• 时间范围条件差异：检查getStartDate()和getEndDate()的返回值在两种查询中是否完全一致，包括时间格式、时区、时分秒部分。虽然>=/<=和BETWEEN逻辑等价，但如果日期的时间部分处理不同（比如一方截断了时分秒），会导致查询范围不一致。
• 对比实际生成的SQL：开启CakePHP的SQL日志，查看查询构建器实际执行的SQL语句，和原生SQL直接对比，能快速定位差异点。可通过开启debug模式，或调用$this->Application->getDataSource()->getLog()获取执行日志。

相关代码 #

/**
 * Get consolidated report
 */
public function getReport($report = 'full')
{
    $this->Application = ClassRegistry::init('Application');

    $applications = $this->Application->find('all', array(
        'fields' => array(
            'Application.*',
            'ApplicationPayday.*',
            'Commission.*',
            'ApplicationResponse.*'
        ),
        'conditions' => array(
            'Application.modified_timestamp >=' => $this->getStartDate(),
            'Application.modified_timestamp <=' => $this->getEndDate()
        ),
        'joins' => array(
            array(
                'table' => 'application_responses',
                'alias' => 'ApplicationResponse',
                'conditions' => array(
                    'ApplicationResponse.application_id = Application.id'
                )
            ),
            array(
                'table' => 'application_paydays',
                'alias' => 'ApplicationPayday',
                'conditions' => array(
                    'ApplicationPayday.application_id = Application.id'
                )
            ),
            array(
                'table' => 'commissions',
                'alias' => 'Commission',
                'conditions' => array(
                    'Commission.application_response_id = ApplicationResponse.id'
                )
            )
        ),
        'recursive' => -1
    ));

    /* Query to Execute */
    $sql = "SELECT
            payday.BankAccount,
            payday.EmpIncomeType,
            application.tlp_aff_id,
            application.modified_timestamp
            FROM
            tlp_applications application
            LEFT JOIN tlp_application_responses response ON response.application_id = application.id
            LEFT JOIN tlp_application_paydays payday ON application.id = payday.application_id
            LEFT JOIN tlp_commissions commissions ON commissions.application_response_id = response.id
            WHERE
            (application.modified_timestamp BETWEEN '" . $this->getStartDate() . "' AND '" . $this->getEndDate() . "')";

    /* Execute Query */
    $submits = ConnectionManager::getDataSource('default')->fetchAll($sql);


    echo "count query builder: " . count($applications) . ' <br />'; // outputs 9768
    echo "count raw sql: " . count($submits) . ' <br />'; // outputs 23904
}

内容的提问来源于stack exchange，提问作者Ryan H