How to create a join that uses a subquery?

Question

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
    ]
]);

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).

I have tried rearranging the items inside joins but still produces same auto-generated SQL.

Can you please help me?

which version of CakePHP are you using? Also, have you declared the relationships in your Model correctly? — Amjo, Mar 08 '18 at 09:58
I think I have the v2 CakePHP. I have also declared the relationships — jaypax123, Mar 08 '18 at 12:47

score 8 · Accepted Answer · answered Mar 08 '18 at 14:09

8

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
);

and pass it to the joins table option:

'table' => "($subquery)"

See also

answered Mar 08 '18 at 14:09

ndm

59,784
9
71
110

Is this better than using the 'query' function of the model? – jaypax123 Mar 09 '18 at 01:08
1

@jaypax123 If you're referring to building a statement, then yes and no. `Model::query()` immediately executes a query, while `DboSource::buildStatement()` generates a query string, you can't compare the two in this situation. – ndm Mar 09 '18 at 01:17
I see. I tried your answer and it works fine. Thank you very much for your help! :) – jaypax123 Mar 09 '18 at 02:45

How to create a join that uses a subquery?

1 Answers1

Linked