CSqlDataProvider is getting empty set

Archives Bug Discussions
1

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'

ORDER BY c.id DESC";

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?

2

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';

		$command=Yii::app()->db->createCommand($sql);

		$count =$command->queryScalar();

		$model=new CSqlDataProvider($sql, array(

			'totalItemCount'=>$count,

			'sort'=>array(

			'attributes'=>array(

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

			),

			),

			'keyField'=>'id',

			'pagination'=>array(

				'pageSize'=>$count,

			),

		));
3

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',

	'totalItemCount'=>$count,

	'pagination'=>array(

        'pageSize'=>50,

    )));


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

	'dataProvider'=>$dataProvider,

	'columns'=>array(

		'kd_brg',

		'thn_ang',

		array(

			'name'=>'HargaPerolehan',

			'htmlOptions'=>array('style'=>'text-align:right;'),

			//'value'=>number_format(HargaPerolehan)

		),

		'NilaiSisa',

		'UmurEkonomis',

		array(

			'name'=>'penyusutan',

			'htmlOptions'=>array('style'=>'text-align:right;'),

			//'value'=>number_format(HargaPerolehan)

		),

		/*array(

			'class'=>'CButtonColumn',

		),*/

	),

));

<>