CSqlDataProvider is getting empty set

I have a multilingual page running following sql for getting the contents. I use CSqlDataProvider for generating the list.

$sql = "SELECT c.id, c.status, c.category_id, c.title, c.language, t.language translation

FROM {{contents}} c

LEFT JOIN {{translations}} t ON t.source_id=c.id

GROUP BY c.id, c.language, c.status, c.category_id, t.language

HAVING (c.language='en' OR t.language='en') AND c.status='6' AND c.category_id='11'


In phpMyadmin this query is returning many results. But in CSqlDataProvider it is returning an empty set. If I change the language to ‘mn’, no problem at all, it retrieving all possible results.

$totalCount = count(Yii::app()->db->createCommand($sql)->queryAll());

$this->_dataProvider = new CSqlDataProvider($sql, array(

	'totalItemCount' => (int)$totalCount,

	'pagination' => array(

		'pageSize' => $this->pageSize,

		'pageVar' => self::PAGE_VAR,



I really don’t get this. Does anybody had a similar problem in CSqlDataProvider? If so, how did you solve it?

I have the same problem… Did you solved the problem?

Here’s my code:

$sql='SELECT t.id AS id, t.date AS tanggal, t.rid AS bukti, t.faktur AS faktur, t.payment AS pembayaran, t.penyetor AS penyetor, l.pj AS pj, c.company_id AS dis, c.company_name AS nama_dis FROM kup_transactions AS t LEFT JOIN kup_ledger AS l ON t.faktur=l.billnumber LEFT JOIN kup_company AS c ON l.company=c.id WHERE l.type_ledger=3 GROUP BY t.id';


		$count =$command->queryScalar();

		$model=new CSqlDataProvider($sql, array(




				 'id', 'tanggal', 'bukti', 'faktur', 'pembayaran', 'penyetor', 'pj', 'dis', 'nama_dis',








1.please check yous SQL statement

its my code,

$count=Yii::app()->db->createCommand('SELECT COUNT(kd_brg) FROM view_penyusutan')->queryScalar();

$sql="SELECT M.rph_aset, P.* FROM view_penyusutan P, dbbmn10.t_masterhm M WHERE P.kd_brg = M.kd_brg";

$dataProvider=new CSqlDataProvider($sql,array(

	'keyField' => 'kd_brg',





$this->widget('zii.widgets.grid.CGridView', array(