1. SCENARIO 1
2. SCENARIO 2
3. SCENARIO 3

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 ¶

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 ¶

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 ¶

public $varColour;

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

public function rules()
{
	return array(
		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 ¶

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

SCENARIO 3 ¶

Display all records from two different tables in the same gridview ¶

In scenario 1 and 2, I used records from a single table and then spiced them up - perhaps with data from another table.

But in this scenario I want to display all records from both tables in a gridview. Check out this wiki and comment: Wiki