CakePHP 2.x中如何编写SELECT子查询适配PostgreSQL？

问题背景 #

原本在MySQL中可正常运行的SQL：

select id,complicatedCalculation as geodistance from User join others having geodistance < 100;

迁移到PostgreSQL后报错提示geodistance不存在。原因是PostgreSQL严格遵循SQL标准，不允许在HAVING子句中直接引用字段别名；而MySQL对此做了非标准兼容。

正确的SQL解决方案是改用子查询：

select * from (select id, complicatedCalculation as geodistance from User join others) as sub where geodistance < 100;

现有尝试的问题 #

尝试用CakePHP 2.x的buildStatement构造子查询，但生成的SQL不符合预期。代码如下：

$db = $this->User->getDataSource();
$core = [ 'fields' => [ 'id', 'complicatedCalculation as geodistance' ],
        'table' => $db->fullTableName($this->User),
        'join' => (others) ]
$sub_query = $db->buildStatement($core, $this->User);
$result = $this->User->find('all', [
        'fields' => [ 'id', 'geodistance' ],
        'table' => $db->expression($sub_query),
        'alias' => 'zzz',  // not used but PGSQL requires it.
        'conditions' => [ 'geodistance <=' => 100 ]
    ]);

生成的错误SQL：

SELECT "User"."id" AS "User__id", complicatedCalculation as geodistance FROM "public"."users" AS "User" LEFT JOIN (others) WHERE "geodistance" <= 100

问题出在CakePHP 2.x的find方法不会把table参数识别为子查询来源，仍会直接使用原模型表。

可行解决方案 #

方法一：通过from参数指定子查询表 #

在CakePHP 2.x中，需要将子查询包装为数据库表达式，并通过from参数传入主查询，同时正确别名子查询表：

$db = $this->User->getDataSource();

// 构造子查询的参数数组
$subQueryParams = [
    'fields' => [
        'id',
        'complicatedCalculation as geodistance'
    ],
    'table' => $db->fullTableName($this->User),
    'joins' => [
        // 填写你的关联配置示例
        [
            'table' => 'others',
            'alias' => 'Other',
            'type' => 'INNER',
            'conditions' => [
                'User.id = Other.user_id' // 替换为实际关联条件
            ]
        ]
    ]
];

// 生成子查询SQL并包装为表达式
$subQuery = $db->buildStatement($subQueryParams, $this->User);
$subQueryExpr = $db->expression("($subQuery) AS sub");

// 主查询使用子查询作为来源表
$result = $this->User->find('all', [
    'fields' => ['sub.id', 'sub.geodistance'],
    'from' => [$subQueryExpr],
    'conditions' => ['sub.geodistance <=' => 100],
    'alias' => 'sub'
]);

此代码会生成符合预期的SQL：

SELECT "sub"."id" AS "sub__id", "sub"."geodistance" AS "sub__geodistance" FROM (SELECT id, complicatedCalculation as geodistance FROM "public"."users" AS "User" INNER JOIN "public"."others" AS "Other" ON User.id = Other.user_id) AS sub WHERE "sub"."geodistance" <= 100

方法二：直接使用原生SQL（备选） #

如果ORM方式仍有问题，直接使用query()方法执行原生SQL是最稳妥的方案：

$sql = "SELECT * FROM (
    SELECT id, complicatedCalculation as geodistance 
    FROM users 
    JOIN others ON users.id = others.user_id -- 替换为实际关联条件
) AS sub WHERE geodistance <= 100";
$result = $this->User->query($sql);

内容的提问来源于stack exchange，提问作者Michael NGV