如何创建使用子查询的连接? [英] How to create a join that uses a subquery?
问题描述
如何将以下 SQL 查询转换为 CakePhp 查找查询?
How do you convert the following SQL Query to a CakePhp Find Query?
SELECT
a.id, a.rev, a.contents
FROM
YourTable a
INNER JOIN (
SELECT
id, MAX(rev) rev
FROM
YourTable
GROUP BY
id
) b ON a.id = b.id AND a.rev = b.rev
我已经尝试了以下代码:
I have tried the code below:
return $model->find('all', [
'fields' => $fields,
'joins' => [
[
'table' => $model->useTable,
'fields' => ['id','MAX(rev) as rev'],
'alias' => 'max_rev_table',
'type' => 'INNER',
'group' => ['id'],
'conditions' => [
$model->name.'.id= max_rev_table.id',
$model->name.'.rev = max_rev_table.rev'
]
]
],
'conditions' => [
$model->name.'.emp_id' => $empId
]
]);
但是好像在生成的SQL中,没有包含joins
下的字段.所以我没有得到 max(rev)
,我只需要得到带有 max(rev)
的行.
But it seems that in the generated SQL, the fields under the joins
is not included. So I don't get the max(rev)
which I need to get only the rows with max(rev)
.
我尝试重新排列 joins
中的项目,但仍然生成相同的自动生成的 SQL.
I have tried rearranging the items inside joins
but still produces same auto-generated SQL.
你能帮我吗?
推荐答案
没有 fields
或 group
选项用于连接,唯一的选项是 table
、别名
、类型
和条件
.
There are no fields
or group
options for joins, the only options are table
, alias
, type
, and conditions
.
如果你想加入一个子查询,那么你需要显式生成一个:
If you want to join a subquery, then you need to explicitly generate one:
$ds = $model->getDataSource();
$subquery = $ds->buildStatement(
array(
'fields' => array('MaxRev.id', 'MAX(rev) as rev'),
'table' => $ds->fullTableName($model),
'alias' => 'MaxRev',
'group' => array('MaxRev.id')
),
$model
);
并将其传递给joins table
选项:
and pass it to the joins table
option:
'table' => "($subquery)"
另见
这篇关于如何创建使用子查询的连接?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!