how to convert count(*) and group by queries to yi

Question

I want to convert this query in yii

SELECT count(*) AS cnt, date(dt) FROM tbl_log where status=2 GROUP BY date(dt) 

and fetch data from that. I try this command (dt is datetime field):

$criteria = new CDbCriteria();
$criteria->select = 'count(*) as cnt, date(dt)';
$criteria->group = 'date(dt)';
$criteria->condition = 'status= 2';
$visit_per_day = $this->findAll($criteria);

but no data will fetch! wath can I do to get data?

Answer 1

Try this below code

     $logs = Yii::app()->db->createCommand()
            ->select('COUNT(*) as cnt')
            ->from('tbl_log')  //Your Table name
            ->group('date') 
            ->where('status=2') // Write your where condition here
            ->queryAll(); //Will get the all selected rows from table

Number of visitor are:

    echo count($logs); 

Apart from using cDbCriteria, to do the same check this link http://www.yiiframework.com/forum/index.php/topic/10662-count-on-a-findall-query/

Answer 2

If you use Yii2 and have a model based on table tbl_log, you can do it in model style like that:

$status = 2;
$result = Model::find()
    ->select('count(*) as cnt, date(dt)')
    ->groupBy('date(dt)')
    ->where('status = :status')
    ->params([':status' => $status ])
    ->all();

Answer 3

Probably you see no data because you need assign data to model attributes which doesn't exist.

$criteria = new CDbCriteria();
$criteria->select = 'count(*) AS cnt, date(dt) AS dateVar';
$criteria->group = 'date(dt)';
$criteria->condition = 'status= 2';
$visit_per_day = $this->findAll($criteria);

This means that your model must have attributes cnt and dateVar in order to show your data. If you need custom query then check Hearaman's answer.