[FIXED] Cakephp INNER JOIN (SELECT ... )

Issue

Cakephp version: 3.5.

I have a claims table. A project has many claims. A claim has one project, a number (1, 2, 3 and running), and each number may be revised multiple times. The combination of project_id, number and rev_number makes a uniqe claim. Table looks something like this (on the right hand I have marked the rows I want in a query):

+----+------------+--------+------------+---------+
| id | project_id | number | rev_number | i want  |
+----+------------+--------+------------+---------+
| 1  | 2          | 1      | 1          |         |
+----+------------+--------+------------+---------+
| 2  | 1          | 1      | 1          | <- this |
+----+------------+--------+------------+---------+
| 3  | 1          | 2      | 1          |         |
+----+------------+--------+------------+---------+
| 4  | 1          | 3      | 1          |         |
+----+------------+--------+------------+---------+
| 5  | 1          | 2      | 2          |         |
+----+------------+--------+------------+---------+
| 6  | 1          | 2      | 3          | <- this |
+----+------------+--------+------------+---------+
| 7  | 1          | 3      | 2          | <- this |
+----+------------+--------+------------+---------+

So I want the latest revision (with the highest rev_number) for each claim number where project_id = 1.

The sql I want to produce with ORM is like

SELECT Claims.*
FROM claims Claims
INNER JOIN (
    SELECT number, MAX(rev_number) AS latest
        FROM claims
        WHERE project_id = 1
        GROUP BY number) AS GroupedClaims
    ON GroupedClaims.number = Claims.number
    AND GroupedClaims.latest = Claims.rev_number;

How can this sql be produced with ORM? How is the INNER JOIN (SELECT ... ) produced? I can't find a solution with join(), innerJoin(), innerJoinWith() or matching() after looking into that.

Solution

As commented by @ndm, by using join() or innerJoin() with a queryObject ((sub)query), it works.

$subq = $this->Claims->find()
            ->select(['number' => 'Claims.number', 'latest' => 'MAX(Claims.rev_number)'])
            ->where(['project_id' => $pid])
            ->group(['number']);

AND

$query = $this->Claims->find()
            ->contain(['ClaimRows'])
            ->join([
                'GroupedClaims' => [
                    'table' => $subq,
                    'type' => 'INNER',
                    'conditions' => ['GroupedClaims.number = Claims.number', 'GroupedClaims.latest = Claims.rev_number']
                ]
            ])
            ->all();

OR

$query = $this->Claims->find()
            ->contain(['ClaimRows'])
            ->innerJoin(
                ['GroupedClaims' => $subq],
                ['GroupedClaims.number = Claims.number', 'GroupedClaims.latest = Claims.rev_number'])
            ->all();

Answered By - Christian