Using concatenate, cases, substring in CakePHP 2

Author - Kailash

How to use concatenate, cases, substring in CakePHP 2.* find query.

Suppose given records are:
Problem: Display user details in one column and the first character of user role in front of the user full name. Also suppose in case of superadmin role as well we want to display (a) instead of (s), we can use Case statement for this.

For example for admin and super admin we want to show like this:
(a) First3 Last3

for limitedadmin we want to show like this:
(l) First2 Last2

Solution (I think the query is self explanatory):

$this->virtualFields = array (
'name' => "CONCAT('(',substring(CASE WHEN User.role like 'superadmin' THEN 'a' ELSE User.role END,1,1),') ',User.firstname, ' ',User.lastname)"
$options = array (
'fields' => array (
return $this->find ( "list", $options );

[1] => (a) First3 Last3
[2] => (l) First2 Last2
[3] => (g) First1 Last1
Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published.