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 ( 'name' ) ); return $this->find ( "list", $options ); Result: Array (  => (a) First3 Last3  => (l) First2 Last2  => (g) First1 Last1 )