CODEWITHSUNDEEP
selectyiicount
amirmohammad
amirmohammad

Reputation: 374

how to convert count(*) and group by queries to yii and fetch data from it

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?

Upvotes: 2

Views: 8583

Answers (3)

karma_police
karma_police

Reputation: 352

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();

Upvotes: 0

Mihkel Viilveer
Mihkel Viilveer

Reputation: 432

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.

Upvotes: 4

Hearaman
Hearaman

Reputation: 8736

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/

Upvotes: 3

Related Questions