How to convert this sql query to CDB criteria format on YII 1.1

Question

I don't know how to convert this sql query to yii CDB criteria. please give me an idea to to do it.

$sql="SELECT ICode,name,Date ,COUNT(*) AS tot, COUNT(case progress when '1' then 1 else null end) AS complete 
      FROM  invoice i LEFT JOIN `customer` c  ON `i`.`CID` = `c`.`CID`  
      left outer join (SELECT * FROM invoiceitem ) ii on ii.IID = i.IID  
      GROUP by ICode , name , `Date` 
      order by complete DESC";

Property "CDbCriteria.0" is not defined.

Why do you want to use CDbCriteria? If you need to fetch aggregated data, you can use a query builder to get a result as an array — Michael Krutikov, Aug 09 '19 at 08:04

score 0 · Answer 1 · answered Aug 09 '19 at 18:14

You could write something like:

 <?php

 $db = Yii::app()->db; //THIS IS YOUR DATABASE CONNECTION
 //Query
 $result = $db->createCommand()->select([
        'ICode',
        'name',
        'Date',
        'COUNT(*) AS tot',
        'COUNT(CASE progress WHEN '1' THEN 1 ELSE null END) AS complete',
    ])->from('invoice AS i')
    ->leftJoin(
        'customer AS c',
        'i.CID = c.CID'
    )->leftJoin(
      '(SELECT * FROM invoiceitem ) AS ii',
      'ii.IID = i.IID'
    )->group('ICode, name, `Date`')->order('complete DESC')->queryAll();

How to convert this sql query to CDB criteria format on YII 1.1

1 Answers1