Cakephp 3: How to get the maximum 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 the maximum number of full lines, I tried the code below, but I get a syntax error.

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

Answer 1

the easiest way

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

using select instead of parameter array

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

using SQL functions for a cake

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

the above three give the same results

if you want to have one entry, you must call ->first()in the request object:

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

Answer 2

The easiest method using CakePHP 3:

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

The result will contain an array containing the maximum number in the table column.