CODEWITHSUNDEEP
mysqlcakephp
Sahbaj Uddin
Sahbaj Uddin

Reputation: 41

How to change Mysql DATE_FORMAT for table column in cakephp?

What is the equivalent cakephp find query for the following sql query?

Assume that price_date field type is datetime

$sql = "SELECT id,product_id,date_format("%Y-%m-%d",price_date) AS pd from products"

Not like this $this->Product->query($sql);

I want it like $this->Product->find('...

Upvotes: 0

Views: 1045

Answers (3)

Oldskool
Oldskool

Reputation: 34837

You would have to define it as a virtualField in your Product model:

class Product extends AppModel {

    public $virtualFields = array(
        'pd' => 'date_format(price_date, "%Y-%m-%d")'
    );

}

Then price_date will always return in Y-m-d format, aliased as pd, as if it were a field in your database. If you want it to return like that under another name, simply change the key in the array. Using it as a find, you can then simply:

$this->Product->find('all', array(
    'fields' => array('id', 'product_id', 'pd')
));

Upvotes: 1

Dave
Dave

Reputation: 29121

Just specify it in your find() fields array:

'fields' => array(
    //...
    'date_format("%Y-%m-%d", price_date)',
    //...
)

Upvotes: 0

Alessandro Minoccheri
Alessandro Minoccheri

Reputation: 35963

try this:

$this->Product->find('all', array(
  'recursive' => -1,
  'fields' => array('id', 'product_id', 'date_format("%Y-%m-%d",price_date) AS pd from products')
));

Upvotes: 0

Related Questions