Cakephp 3 : How to get max amout row from a table

Question

I have table call users , like

id name amount   created
1   a   100     6-16-2016
2   b   200     5-16-2016

I need max amount full row, I have tried below code but getting syntax error.

  $user = $this->Users->find('all',[
         'fields' => array('MAX(Users.amount)  AS amount'),
  ]); 

Infamous

Answer 1

simplest way

$user = $this->Users->find('all',[
   'fields' => array('amount' => 'MAX(Users.id)'),
]); 

using select instead of an options array

$user = $this->Users->find()
    ->select(['amount' => 'MAX(Users.id)']); 

making use of cake SQL functions

$query = $this->Users->find();
$user = $query
    ->select(['amount' => $query->func()->max('Users.id')]);

the above three all give the same results

if you want to have a single record you have to call ->first() on the query object:

$user = $user->first();
$amount = $user->amount;

Henequen

Answer 2

Simplest method using CakePHP 3:

$this->Model->find('all')->select('amount')->hydrate(false)->max('amount')

Will result in an array containing the maximum amount in that table column.

Googins

Answer 3

Is is better to disable hydration before getting the results, to get the results as an array, instead of entity. Below is a complete example of how to get the results in an array, with distinct steps:

//create query
$query = $this->Users->find('all',[
   'fields' => array('amount' => 'MAX(Users.id)'),
]); 

$query->enableHydration(false); // Results as arrays instead of entities
$results = $query->all(); // Get ResultSet that contains array data.
$maxAmount = $results->toList(); // Once we have a result set we can get all the rows

Luu