[Diane]

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

),
));

[Da:Sourcerer]

I don't think that's possible. The CGridView.filter property must be a CModel instance. AFAIK that won't work with a CSqlDataProvider.

[kokomo]

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

[Diane]

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.

[Sisko]

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.

[Diane]

Many thanks. I'll try that.

[Diane]

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?

[Sisko]

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.

[Sisko]

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.

[Diane]

Appreciate your help. Looks like it's working. Thanks.