Cakephp query to get last single field data from multi user

Question

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?

Answer 1

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

• Retrieving the last record in each group

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