[FIXED] how to use mysql function inside cakephp2

Issue

I am trying to make a simple query inside my model, I have already done the same thing in my controller, and so far it works.

/users/controller 
    $reservations = $db->fetchAll(
        "SELECT TIME_FORMAT(time_start, '%H:%i') as time_start, 
                TIME_FORMAT(time_end, '%H:%i') as time_end 
         FROM bookings 
         WHERE datepicker = ?", [$date]); 

I know that solution is not efficient, so I wanted to create a method in my function to do the same thing, however I can't find a way to enclode the time_start field with the mysql function TIME_FORMAT() using ->find() method.

update.

I have tried using virtual fields and I got only an Indirect modification of overloaded property has no effect error.

Solution

You want to use virtual fields for these. So in your case you'd want something like this:-

public $virtualFields = array(
    'time_start' => "TIME_FORMAT(time_start, '%H:%i')",
    'time_end' => "TIME_FORMAT(time_end, '%H:%i')"
);

These are defined within the relevant model.

Answered By - drmonkeyninja

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)