Yii Framework Forum: CSqlDataProvider filtering - Yii Framework Forum

Jump to content

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

CSqlDataProvider filtering Rate Topic: -----

#1 User is offline   Diane 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 08-February 12

Posted 26 March 2012 - 09:09 AM

I'm using a gridview to view results of a sql query expression defined in my controller as a CSqlDataProvider. I'm trying to be able to search and filter this gridview but running into trouble. Can anyone help?



My code in the controller

$sql = "SELECT ListingID as id, BillDate as Date, ROUND(SUM(chargeAmount), 2) as Price, Comments as Type FROM tbl_bills GROUP BY ListingID
UNION ALL
SELECT ListingID as id, PaymentDate as Date, SUM(PaymentAmount)*-1 as Price, PaymentType as Type FROM tbl_payments GROUP BY ListingID
ORDER BY id ASC";

$dataProvider = new CSqlDataProvider($sql);
$dataProvider->getData();

$this->render('pHistory2', array(
'dataProvider'=>$dataProvider));


My code in view


$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'history-grid',
'dataProvider'=>$dataProvider,
'columns'=>array(
'id',
'Date',
'Price',
'Type',

),
));
0

#2 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,217
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 26 March 2012 - 09:16 AM

I don't think that's possible. The CGridView.filter property must be a CModel instance. AFAIK that won't work with a CSqlDataProvider.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#3 User is offline   kokomo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 281
  • Joined: 23-July 10

Posted 26 March 2012 - 09:29 AM

There is this post from Gustavo with a solution using CDbCriteria.
0

#4 User is offline   Diane 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 08-February 12

Posted 26 March 2012 - 09:55 AM

Thanks for taking the time to answer me. I checked out the post you suggested and am just not sure how to implement it with a UNION ALL statement. He was using relationships.

Thank you.
0

#5 User is offline   Sisko 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 27-September 10

Posted 27 March 2012 - 04:57 AM

You need to have a model with class variables corresponding to your query columns defined then it is possible.
Something like that
class BillModel extends CFormModel
{
	
	public $id; // I guess you dont need that, but I am not sure
	public $Date;
	public $Price;
	public $Type;

public function rules(){
 // define for search mass assignment
}

public function getSqlDataProvider()
{
   // sql = query + the where conditions to filter
   // that's not as comfortable as CDbCriteria->compare() since you
   // want to add the condition only when there was a search for it
}


There is a filter option for every column in grid, if you dont want everything filered.

Of course you need to assign the searched values to the model in your controller.
0

#6 User is offline   Diane 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 08-February 12

Posted 27 March 2012 - 09:19 AM

Many thanks. I'll try that.
0

#7 User is offline   Diane 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 08-February 12

Posted 27 March 2012 - 09:31 AM

I'm trying out your solution but getting a drop stuck.
What does my sqldataprovider function return and how do I assign searched values to the model?
0

#8 User is offline   Sisko 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 27-September 10

Posted 27 March 2012 - 03:12 PM

Ok, quick and dirty, don't blame me for bad code :)

<?php
class BillsModel extends CFormModel{
	
	public $id;	
	public $price;
	
	public function rules()
	{
		return array(
			array('id,price','safe','on'=>'search')
		);
	}
	
	public function getSqlDataProvider()
	{
		
		$priceCondition = !empty($this->price) ? ' WHERE unionAlias.price ='. $this->price : ' '; 
		$sql = '
		SELECT id,price FROM
		(
			SELECT ListingID as id, Price as price FROM tbl_bills
			UNION ALL
			SELECT ListingID as id, Price as price FROM tbl_payments
		) as unionAlias'
		.$priceCondition;
               // if needed create a CSorter Object and pass to the dataprovider
		return new CSqlDataProvider($sql);
	}
}


Controller
public function actionIndex()
	{
		$model = new BillsModel('search');
		$model->unsetAttributes();
		if (isset($_GET['BillsModel'])){
			$model->attributes = $_GET['BillsModel'];
		}
		$this->render('index',array('model'=> $model));
	}


View
<?php
$this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'history-grid',
	'filter'=>$model,
	'dataProvider'=>$model->getSqlDataProvider(),
	'columns'=>array(
		array(
			'name'=>'id',
			'filter'=>false
			),
		'price'
		)
	)
);
?>


Not so nice, but you should get the idea how this is possible.
2

#9 User is offline   Sisko 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 27-September 10

Posted 28 March 2012 - 03:58 AM

Little addition: you should be aware, that I didnt consider the security aspects when writing this shortened sql query. I guess it would be possible to do a sql injection without further adjustments.
0

#10 User is offline   Diane 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 08-February 12

Posted 29 March 2012 - 10:01 AM

Appreciate your help. Looks like it's working. Thanks.
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