Difference between #13 and #14 of Sort and filter a custom or composite CGridView column - that may even contain data from different tables.

changed
Title
Sort and filter a custom or composite CGridView column - that may
even may contain data from different tables.
unchanged
Category
How-tos
unchanged
Tags
complex custom CGridView column sort filter
unchanged
Content
SCENARIO 1
----------

### Overview

I have a table (account) which contains "General Ledger" accounts.
Each account has its own account_description - such as 'inventory',
'sales', etc.

In a second table (client), I have the names of all the clients. The fields are
client_surname, client_name1, client_name2 and client_name3.

All clients have accounts, but not all accounts are linked to a client.
So viewed from the account's perspective, this is a normal one-to-many
relation (actually one-to-zero/one) with account
being parent and client being child.

### Objective

Use only one gridview column.
If the account does not have a client - then display the account_description
(account tbl) in the gridview column; otherwise display the client's names
and surname (client tbl) in the column.

So for some rows, this column will contain data from account tbl; while in other
rows, this column will contain data from client tbl; 

### Model


~~~
[php]
class account_model extends CActiveRecord
{
	/* Gridview Attributes */
	public $varFullname;
	
	/* Own defined Getter function to combine fields */
	public function getCompiledFullname()
	{
		/* Test if account is linked to a Client. If not, use account_description,
otherwise use client names and surname. */
		if ($this->client !== null)
		{
			return  
				$this->client->client_surname . ' ' .
				$this->client->client_name1 . ' ' .
				$this->client->client_name2 . ' ' .
				$this->client->client_name3;
		}
		else
		{
			return $this->account_description;
		}
	}
	
	public function relations()
	{
		return array(
			'client' => array(self::HAS_ONE, 'client',
'accnt_nr'),
		);
	}
	
	public function rules()
	{
		return array(
			array('varFullname', 'safe',
'on'=>'search'),
		);
	}
	
	public function attributeLabels()
	{
		return array(
			'account_description' => 'Name',
		);
	}

	public function search()
	{
		$criteria		= new CDbCriteria;
		$sort 			= new CSort;
		
		/* Eager loading if Account has a Client */
		$criteria->with = array(
			'client' => array()
		);

		/* Filter criteria 
		Testing for null values is important because unused name fields
		will be null and they will filter out records if untreated.

	 	The following compare statement works like this:
		if(account_description == null)
	  	then (use surname and names in comparison)
	  	else (use account_description in comparison).
	  	Compare them to $this->varFullname.  

		Note: IFNULL = MySql; ISNULL = SQL Server*/

		$criteria->compare(
			"IFNULL(account_description,
				CONCAT(	IFNULL(client_surname,''),
						IFNULL(client_name1,''),
						IFNULL(client_name2,''),
						IFNULL(client_name3,''))
			)", $this->varFullname, true);

		/* Sort criteria */
		$sort->attributes = array(
			/* 	if (account_description is null)
				then (sort by client_surname, client_name1...), 
				else (sort by account_description) */
			'varFullname'=>array(
		        'asc'=>"IFNULL(account_description,
					CONCAT(	IFNULL(client_surname,''),
							IFNULL(client_name1,''),
							IFNULL(client_name2,''),
							IFNULL(client_name3,''))
				)",
		        'desc'=>"IFNULL(account_description,
					CONCAT(	IFNULL(client_surname,''),
							IFNULL(client_name1,''),
							IFNULL(client_name2,''),
							IFNULL(client_name3,''))
				) desc",
		    ),
			'*',
		);
		
		/* Default Sort Order*/
		$sort->defaultOrder= array(
			'varFullname'=>CSort::SORT_ASC,
		);
	
		return new CActiveDataProvider($this, array(
			'pagination'=>array('pageSize'=>20),
			'criteria'=>$criteria,
			'sort'=>$sort,
		));
	}

}
~~~


### CGridView Column


~~~
[php]
array(
	'name'	=> 'varFullname',
	'value'	=> '($data->CompiledFullname) ?
$data->CompiledFullname : ""',
	'header'=>
CHtml::encode($model->getAttributeLabel('account_description')),
	'filter'=> CHtml::activeTextField($model,
'varFullname'),
),
~~~

SCENARIO 2
----------

### Overview
In this second scenario, you want to display the name of a colour in the
CGridView
column, based on the value of the 'type' property in your table.

### Model

~~~
[php]
public $varColour;

public function getCompiledColour()
{
	if($this->type == 1)
	{
		return 'Red';
	}
	elseif($this->type == 2)
	{
		return 'Yellow';
	}
	else
	{
	return 'Green';
	}
}

array('varColour', 'safe',
'on'=>'search')

public function attributeLabels()
{
	return array(
		'varColour' => 'Colour',
	);
}

public function search()
{
	$criteria	= new CDbCriteria;
	$sort 		= new CSort;

	/* Filter Criteria */
	$criteria->compare(
		"CASE t.type
			WHEN 1 THEN 'Red'
     		WHEN 2 THEN 'Yellow'
			ELSE 'Green' 
		END", $this->varColour, true);

	/* Sort Criteria */
	$sort->attributes = array(
		
		'varColour'=>array(
	        'asc'=>"
				CASE t.type
					WHEN 1 THEN 'Red'
	         		WHEN 2 THEN 'Yellow'
					ELSE 'Green' 
				END",
	        'desc'=>"
				CASE t.type
					WHEN 1 THEN 'Red'
	         		WHEN 2 THEN 'Yellow'
					ELSE 'Green' 
				END	desc",
	    ),

		'*',
	);
	
	return new CActiveDataProvider($this, array(
		'pagination'=>array(
			'pageSize'=>20,
		),
		'criteria'=>$criteria,
		'sort'=>$sort,
	));
}
~~~


### CGridView Column

~~~
[php]
array(
	'name'=>'varColour',
	'value'=>'($data->CompiledColour) ?
$data->CompiledColour : ""',
	'header'=>
CHtml::encode($model->getAttributeLabel('varColour')),
	'filter' => CHtml::activeTextField($model,
'varColour'),
),
~~~

Write new article