Cakephp How to use SQL month function in cakephp find method

0
votes

I have a query like 

SELECT count(id),DATE_FORMAT(created,'%M') from searches group by month(created)

The output that I got like 

count(id)   DATE_FORMAT(created,'%M')   
16             August
2              September

I applied this query in cakephp find method like below 

$total = $this->Searches->find('count',[
             'fields'=>['Searches.id',DATE_FORMAT('created','%M')],
             'group' =>[month('Searches.created')]
]);

1st I am getting error date_format() expects parameter 1. How can I apply my top sql query in cakephp find method ? I am using cakephp 3 version.

1
cakephpcakephp-3.0

1 Answers

1
votes

The query builder has a func() method that allows you to use SQL functions.

http://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions

$time = $query->func()->date_format([
    'created' => 'identifier',
    "'%M'" => 'literal'
]);

You can then use that $time variable in your query.