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));
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.
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.
<?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));
}
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.
