[dashaa]

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?

[Wiwit Iwan SEP]

dashaa, on 21 September 2011 - 07:16 AM, said:

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?

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

[mrkoeh]

Wiwit Iwan SEP, on 15 August 2012 - 03:28 AM, said:

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

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',
		),*/
	),
));

<>