How to create custom CGridView in Yii
Yii is an extremely powerful PHP framework, which helps you to create complete web applications within a short period of time. It has a broad set of built-in tools that make developers' life easier. But... and there's always a but, easier doesn't necessarily mean that you don't have to code any longer. There are some situations when you can't solve the problem clearly by using Yii's tool-set, and you need to rely on your own coding knowledge, or you should use PHP and Yii's tool-set combined. Remember that PHP frameworks can never replace the entire programming process, but provide great tools for faster development.
 |
| Drawing a matrix on Michael Knight way |
Problem to be solved
In my project I had to create a matrix view of number of dishes ordered on given days. I stored data in a database table which had the following structure:
CREATE TABLE IF NOT EXISTS `ff_order` ( `letter` varchar(5) COLLATE utf8_hungarian_ci NOT NULL', `date` date NOT NULL COMMENT, `no_dishes` int(11) NOT NULL COMMENT, `creation_dt` datetime NOT NULL COMMENT, `modification_dt` datetime NOT NULL COMMENT, `user_id` int(11) NOT NULL COMMENT, PRIMARY KEY (`date`,`letter`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;
If I would have handed over the content of this table to my view by using the default data provider, the result would have looked like this :
 |
| Normal data provider view |
 |
| Matrix view using the same data set |
If the grid can only display the data in a form that is provided by the data provider, let's change our data provider to compile the proper format that we need for our CGridView.
The theoretical part
CGridView represents data received through a data provider. So, the good news is if we can put together a data provider that represent our data in the form that we need, the only thing that we need to do is just pass it to our view file to display. Luckily, the necessary data can be prepared in not only one but several ways in Yii. I'll mention two techniques here which can be done with Yii and will further explain only one of them via an example, but I'm sure that there are many other solutions.
If you have ever generated a skeleton code with Yii's built in code generator called Gii and you have taken a look at any of your controllers you could see that it defined at least one data provider which most likely was a CActiveDataProvider. This data provider provides data in terms of ActiveRecord objects. It uses the AR CActiveRecord::findAll method to retrieve the data from database. In other words, retrieves all of your records from the database. That's it. This provider was created with the intention of this single task. That's why it has its limitations. Don't get me wrong! It's useful in 90% of the cases but sometimes you need more flexibility in your project, just like I had in this particular case.
So what can we do? How can we get data from our database in the required form? Fortunately, Yii comes to our aid with other data provider types like CArrayDataProvider and CSqlDataProvider. Both of them are capable to compile data for your view. While the first one is used to prepare data for display based on an array that you pass to it, the second is capable to retrieve data from your database based on any SQL query. Got it? So, for example if you create a multidimensional array where you arrange your data as you want to see in the view, you can pass it to a CArrayDataProvider, which transmits it to your view. To put together this array you can utilize PHP's versatility. Same is true for CSqlDataProvider but it requires an SQL statement with which you query the data from the database as required. Or you can use them combined: retrieve the data from the database and arrange it as you want to see in the grid view. So easy!
CSqlDataProvider way
Now that we know what we're dealing with we can start to implement our matrix. I'll introduce the SQL data provider in detail here through a complete example.
I already mentioned my table structure above, so here is the view action of my controller, which prepares the data in the required format and passes it to the relevant view through a data provider:
public function actionView() {
// Get number of items for pagination
$count=Yii::app()->db->createCommand('SELECT COUNT(DISTINCT(letter))
FROM ff_order')->queryScalar();
// Get dates from the table
$sql_getLastDates = "SELECT distinct(date) as 'id'
FROM ff_order
ORDER BY date ASC";
$connection = Yii::app()->db;
$command = $connection->createCommand($sql_getLastDates);
$dataArray = $command->queryAll();
// Prepare CASE statements based on the matrix will be created
$sql_case = '';
foreach ($dataArray as $row){
foreach ($row as $key=>$val) {
$day = date('l', strtotime($val));
$sql_case .= "SUM((CASE WHEN date='".$val."' THEN `no_dishes` END)) '".$day."',";
}
}
// We need to cut the trailing comma of the last case
// since our SQL query structure will be incorrect
$final_case = rtrim($sql_case, ",");
// Put together the final query
$sql_getMatrix = "SELECT letter as 'id', ".$final_case."
FROM ff_order
GROUP BY id";
$sqlProvider = new CSqlDataProvider($sql_getMatrix, array(
'totalItemCount'=>$count,
'pagination'=>array(
'pageSize'=>$count,
),
));
$this->render('view',array(
'dataProvider'=>$sqlProvider,
));
}
Let me explain the code step by step in detail. First I run a query to figure out how much menu items I have in the table. I'll use this number as item number for my pagination in the data provider as I want to represent all data on the very same page. If you check my data provider I pass this parameter as pagination number. In this way I can make sure that my data will fit to one page regardless of the number of records in the database table.
In the next few steps I prepare the SQL query whose result will be handed over to the data provider. So, our next task is to put together the following SQL query which responsible to draw the matrix:
SELECT letter as 'id', SUM((CASE WHEN date='2014/04/07' THEN `no_dishes` END)) '2014/04/07', SUM((CASE WHEN date='2014/04/08' THEN `no_dishes` END)) '2014/04/08', SUM((CASE WHEN date='2014/04/09' THEN `no_dishes` END)) '2014/04/09', SUM((CASE WHEN date='2014/04/10' THEN `no_dishes` END)) '2014/04/10', SUM((CASE WHEN date='2014/04/11' THEN `no_dishes` END)) '2014/04/11', SUM((CASE WHEN date='2014/04/12' THEN `no_dishes` END)) '2014/04/12', SUM((CASE WHEN date='2014/04/13' THEN `no_dishes` END)) '2014/04/13' FROM ff_order GROUP BY id";
You can see that it consists of as many SQL CASE statements as many dates we have in our database table. Each date represents a coulmn with the summary of dishes orederd by clients. This SQL query can provide the matrix that we finally need but we can't hardcode the values into the query becasue those are changing all the time. Instead we have to prepare this query dinamically using PHP's felxibility. Here's the code how to generate the SQL CASE statements in PHP:
// Prepare CASE statements based on the matrix will be created
$sql_case = '';
foreach ($dataArray as $row){
foreach ($row as $key=>$val) {
$day = date('l', strtotime($val));
$sql_case .= "SUM((CASE WHEN date='".$val."' THEN `no_dishes` END)) '".$day."',";
}
This code snippet generates all the CASE statements for us. As I have to show only one week of data for this particular project I use name of days as column names. To find out name of a day based on date I use strtotime PHP function. The only thing remained is to stitch together the final SQL query and fire it via a CSqlDataprovider:
// We need to cut the trailing comma of the last case
// since our SQL query structure will be incorrect
$final_case = rtrim($sql_case, ",");
$sql_getMatrix = "SELECT letter as 'id', ".$final_case."
FROM ff_order
GROUP BY id";
$sqlProvider = new CSqlDataProvider($sql_getMatrix, array(
'totalItemCount'=>$count,
'pagination'=>array(
'pageSize'=>$count,
),
));
As you probably noticed I cut off the last comma of the CASE statement before I merged it with the final SQL query otherwise it would have been syntactically incorrect. Finally we load the result to a variable, and that variable is rendered for the view as shown in the code of my complete action above.
Lastly we represent our data by using a simple GridView. I use Hungarian equivalents of English day names so my grid looks something like this:
$this->widget('bootstrap.widgets.TbGridView', array(
'dataProvider'=>$dataProvider,
'columns'=>array(
array(
'name' => 'Menü',
'value' => '$data[\'id\']',
),
array(
'name' => 'Hétfő',
'value' => '$data[\'Monday\']',
),
array(
'name' => 'Kedd',
'value' => '$data[\'Tuesday\']',
),
array(
'name' => 'Szerda',
'value' => '$data[\'Wednesday\']',
),
array(
'name' => 'Csütörtök',
'value' => '$data[\'Thursday\']',
),
array(
'name' => 'Péntek',
'value' => '$data[\'Friday\']',
),
array(
'name' => 'Szombat',
'value' => '$data[\'Saturday\']',
),
array(
'name' => 'Vasárnap',
'value' => '$data[\'Sunday\']',
),
)
));
That's all! I hope you found this article interesting and useful. Feel free to contact me or comment on this post if you have any question on the subject.
