Cakephp 3: How to do an aggregate select statement within an inner join using Cakephp's Query Builder?

Question

Well I just can't seem to figure this out. I'm on day two of messing around with this and it's time to enlist some help.

Here's the SQL that works:

SELECT rt1.request_id, rt1.status 
FROM request_transactions rt1 
INNER JOIN (
    SELECT max(id) AS max_id 
    FROM request_transactions 
    GROUP BY request_id
) AS rt2 ON rt1.id = rt2.max_id;

How in the world do I write this using the Cakephp 3 query builder? I'm completely stuck in regards to the inner join with an aggregate function (max).

Many thanks in advance.

EDIT: Here's the table columns and data, if that helps:

SELECT id, user_id, created, modified FROM requests;

 id | user_id |       created       |      modified       
----+---------+---------------------+---------------------
 94 |     101 | 2017-07-04 18:20:47 | 2017-07-04 18:21:26
 95 |     101 | 2017-07-04 18:48:14 | 2017-07-04 18:48:14
 96 |     101 | 2017-07-04 18:48:40 | 2017-07-04 18:48:40
(3 rows)

SELECT id, status, created, request_id FROM request_transactions;                    

 id  |  status  |       created       | request_id 
-----+----------+---------------------+------------
 127 | created  | 2017-07-04 18:20:47 |         94
 128 | read     | 2017-07-04 18:21:03 |         94
 129 | rejected | 2017-07-04 18:21:26 |         94
 130 | created  | 2017-07-04 18:48:14 |         95
 131 | created  | 2017-07-04 18:48:40 |         96
(5 rows)

SELECT rt1.request_id, rt1.status 
FROM request_transactions rt1 
INNER JOIN (
    SELECT max(id) AS max_id 
    FROM request_transactions 
    GROUP BY request_id
) AS rt2 ON rt1.id = rt2.max_id;

 request_id |  status  
------------+----------
         96 | created
         94 | rejected
         95 | created
(3 rows)

What specifically is the problem? Creating a subquery? Creating SQL function calls? ...? — ndm, Jul 04 '17 at 16:55
The problem I'm having is that I can't figure out how to express this SQL problem using the Cakephp 3 Query Builder. $query = $this->RequestTransactions->find('all')->??; — JKaht, Jul 04 '17 at 18:43

score 1 · Answer 1 · answered Jul 04 '17 at 19:20

Alright! After some muddling about, I think I have it:

$requestTransactionsModel = $this->loadModel('RequestTransactions');

$query = $requestTransactionsModel->find('all');
$query->select([
    'max_id' => $query->func()->max('id'),
])
->group('request_id');

$query2 = $requestTransactionsModel->find('all');
$query2->select([
    'request_id',
    'status',
])
->join([
    'table' => $query,
    'alias' => 'rt2',
    'type' => 'INNER',
    'conditions' => ['rt2.max_id = RequestTransactions.id'],
]);

This provides the same data as the SQL that was posted in the question. There were two parts I was having a hard time finding answers to:

I didn't know you could use a query as a table name in join(). That was trial and error.

I expected the conditions to be in the format

conditions => ['rt2.max_id' => 'RequestTransactions.id']

That wasn't working. It was putting quotes around RequestTransactions.id. It needed to be written as

conditions => ['rt2.max_id = RequestTransactions.id']

score 0 · Answer 2 · answered Jul 06 '17 at 04:23

0

You can try this:

$result = $this->$requestTransactionsModel->find('all')->join(
    ['requests'=>[
      'table'=>'requests',
      'alias'=>'r',
      'type'=>'inner',
      'conditions'=>['request_transactions.id = r.max_id'],
      ])->group('request_transactions.request_id');

answered Jul 06 '17 at 04:23

Manh Nguyen

429
1
11
21

That query appears to be missing the MAX(id) function on the request_transactions table, so doesn't accomplish the task. Also, $this->$requestTransactionsModel is invalid. – JKaht Jul 07 '17 at 12:08

Cakephp 3: How to do an aggregate select statement within an inner join using Cakephp's Query Builder?

2 Answers2