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
Answered By - ndm
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.