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?

Just write an efficient SQL statement and use Cakephp's [query method](http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#model-query). There is not an easy / visually pleasing/ easy to maintain way to implement via `find`. IMO: You're better off using `query` and writing good comments. — AgRizzo, Jan 12 '16 at 19:18
I know about query method but I want to apply it in cakephp format. — Satu Sultana, Jan 12 '16 at 19:21

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

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

edited May 23 '17 at 12:04

Community

1
1

answered Jan 12 '16 at 20:25

Inigo Flores

4,461
1
15
36

You need to compare the id column, not the balance column. – AgRizzo Jan 12 '16 at 22:13
@AgRizzo You are right, don't know why I got mixed up. Edited the answer. Thanks! – Inigo Flores Jan 12 '16 at 23:13
Is it secure if I use query () ? – Satu Sultana Jan 13 '16 at 04:34
It's secure, as long as you don't open it up to possible SQL injection vulnerabilities. See [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Inigo Flores Jan 13 '16 at 10:07

Cakephp query to get last single field data from multi user

1 Answers1