I have a table called Transaction
with relation User
, Transaction has a field called balance
.
Data looks like:
id user_id balance
1 22 365
2 22 15
3 22 900
4 32 100
4 32 50
I need all users associative data and last insert balance field of User
. For example here id=3
is last inserted data for user_id=22
.
In raw SQL I have tried this:
select * from transactions where id in (select max(id) from transactions group by user_id)
If I add here a inner join I know I can also retrieve User data. But how can I do this in CakePHP?
IMHO, subqueries are ugly in CakePHP 2.x. You may as well hard code the SQL statement and execute it through query()
, as suggested by @AgRizzo in the comments.
However, when it comes to retrieving the last (largest, oldest, etc.) item in a group, there is a more elegant solution.
In this SQL Fiddle, I've applied the technique described in
The CakePHP 2.x equivalent would be:
$this->Transaction->contains('User');
$options['fields'] = array("User.id", "User.name", "Transaction.balance");
$options['joins'] = array(
array('table' => 'transactions',
'alias' => 'Transaction2',
'type' => 'LEFT',
'conditions' => array(
'Transaction2.user_id = Transaction2.user_id',
'Transaction.id < Transaction2.id'
)
),
);
$options['conditions'] = array("Transaction2.id IS NULL");
$transactions=$this->Transaction->find('all', $options);