CakePHP 2.X: compound (concatenate several db columns) virtualfield as displayfield

Question

I already answered my own question but I wouldn't mind accepting any other answer that is really better than my approach (do mention how it is better). Please contribute.

---

I had a situation where in my User Model, I had first_name, middle_name, last_name as 3 separate fields and only first_name being validated against empty/NULL, i.e., the other 2 can be empty/NULL/whitespace(didn't want to annoy the users with unreasonable validations, I personally know people who don't have/use their last names for some crazy reasons).

With such structure, it is reasonable to expect the displayfield as a compound of all these 3 columns, keeping in mind that the other 2 might be ""/NULL/white-space (since they are all VARCHAR columns). Also, my DB table has columns like created_by, modified_by, approved_by, assigned_to... (heck lot of other aliases for this model, but all of them well related following the "CakePHP convention").

I read the official documentation and several posts here on SO like this one. But none of them offers a solution or sample for this kind of a situation. Perhaps, my DB structure is too unconventional or I'd say "unorthodox" ;) (well, the official documentation does answer on multiple model aliases, which I missed several times, which motivated me to post this question and answer it here on SO).

So, the question is how to correctly address such model relationship and structure?

Nine

Answer 1

TL; DR; - Jump to final solution at the end

I will answer my own question in 2 parts -

1. How to address multiple model aliases in virtual fields
2. How to properly concatenate the fields to get desired "full name"

1. Model aliases

Official documentation for CakePHP 2.X has a section on Virtual fields and model aliases.

When you are using virtualFields and models with aliases that are not the same as their name, you can run into problems as virtualFields do not update to reflect the bound alias. If you are using virtualFields in models that have more than one alias it is best to define the virtualFields in your model’s constructor

So something like this in User.PHP should allow virtualFields to work for any alias you give a model:

public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);
    $this->virtualFields['name'] = sprintf(
        'CONCAT(%s.first_name, " ", %s.middle_name, " ", %s.last_name)', $this->alias, $this->alias, $this->alias
    );
}

2. Empty middle and last names

This can be answered by some additional SQL syntax in the model constructor, I used CASE ... WHEN ... THEN ... ELSE ... END for simplicity, which may be substituted with a better SQL(if possible). But this one doesn't fail either(and is not bad).

CONCAT(%s.first_name, CASE WHEN %s.middle_name = "" THEN " " ELSE CONCAT(" ", %s.middle_name, " ") END, %s.last_name)

Final solution

In model constructor:

public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);
    $this->virtualFields['full_name'] = sprintf(
        'CONCAT(%s.first_name, CASE WHEN %s.middle_name = "" THEN " " ELSE CONCAT(" ", %s.middle_name, " ") END, %s.last_name)', $this->alias, $this->alias, $this->alias, $this->alias
    );
}

and finally, the usual public $displayField = 'full_name';

Now, wherever you do a $this->RelatedModel->UserAlias->find('list'); in controllers, you get an array with id and full_name of your users.

Hope this helps someone, or at least work as a journal for myself :)

Nine