[FIXED] ORDER BY on MySQL stored function in CakePHP 3

Issue

I'm working on cakePHP 3. I have a user defined function(UDF or Routine) in mysql database. That function takes a parameter and returns an integer value. I have to order that returned value in MySQL order clause.

I know mysql query to use that function. i.e,

SELECT customer_id FROM table_name ORDER BY routine_name(param1);         //param1 is 'customer_id' which I have written after SELECT

But I don't know that how to build this query in cakePHP 3. If anyone knows the solution, answer will be appreciate.

Here is my cakePHP 3 code.

$purchasesTable = TableRegistry::get("Purchases");
$query = $purchasesTable->find();

$sf_val = $query->func()->routine_name(['Purchases.customer_id' => 'literal']);
$query->select();
$query->order(
    //    Routine/Function call should be here as per MySQL query.
    //    So, I think here I have to do something.
);

Solution

I'd suggest that you have a closer look at the (API) docs, it's all mentioned there. You can pass expression objects to Query::order(), and in case you need to specify the direction, there's also Query::orderAsc() and Query::orderDesc().

So

$expression = $query->func()->routine_name(['Purchases.customer_id' => 'literal']);
$query->order($expression);

or

$query->orderAsc($expression);

or

$query->orderDesc($expression);

See also

• Cookbook > Database Access & ORM > Query Builder > Selecting Data

• API > \Cake\Database\Query::order()

• API > \Cake\Database\Query::orderAsc()

• API > \Cake\Database\Query::orderDesc()

Answered By - ndm