Yii Framework Forum: CSqlDataProvider is getting empty set - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CSqlDataProvider is getting empty set Is this really bug or logical error Rate Topic: -----

#1 User is offline   dashaa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 19-December 10
  • Location:Ulaanbaatar, Mongolia

Posted 21 September 2011 - 07:16 AM

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?
0

#2 User is offline   Wiwit Iwan SEP 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 06-April 11
  • Location:Gresik - Indonesia

Posted 15 August 2012 - 03:28 AM

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

0

#3 User is offline   mrkoeh 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 42
  • Joined: 31-October 12
  • Location:Indonesia

Posted 07 January 2013 - 11:52 PM

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

<>
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users