CakePHP 2.X：复合（连接几个db列）virtualfield作为displayfield [英] CakePHP 2.X: compound (concatenate several db columns) virtualfield as displayfield

问题描述

我已经回答了我自己的问题，但我不介意接受任何其他答案，真的比我的方法更好（提到它是更好）。

---

我有一种情况，在我的用户模型

code>，我有 first_name，middle_name，last_name 为3个单独的字段，只有 first_name code> empty / NULL ，即其他2可以为空/ NULL /空格（不想让用户进行不合理的验证，我个人认为人们没有/

有了这样的结构，可以合理预期 displayfield 作为所有这3列的一个复合，记住其他2可能是/ NULL / white-space（因为它们都是VARCHAR列）。此外，我的数据库表具有 created_by ， modified_by ， approved_by ， assigned_to ...（这个模型的其他别名的heck很多，但是所有这些别名都遵循CakePHP约定）。

我阅读了官方文档和这里的几个帖子SO 喜欢这一个。但他们都没有提供这种情况的解决方案或样品。也许，我的DB结构太不寻常，或者我会说unorthodox;）（好，官方文档确实回答多个模型别名，我错过了几次，这激发了我发布这个问题，并在这里回答SO ）。

因此，问题是如何正确地解决这种模型关系和结构？

解决方案

TL; DR 到最后解决方案。

我会在2个部分回答我自己的问题 -

1. 如何解决虚拟字段中的多个模型别名


2. 如何正确连接字段以获取所需的

1。 模型别名

CakePHP 2.X的官方文档中有虚拟字段和模型别名。

当你使用的虚拟字段和模型的别名不是
和他们的名字一样，你可以遇到问题virtualFields做
不更新反映绑定别名。如果在具有多个别名的模型中使用virtualFields
，最好在模型构造函数中定义
virtualFields。

因此，在 User.PHP 中应该允许virtualFields为您提供模型的任何别名工作：

  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。 清空中间名和姓氏

这可以通过模型构造函数中的一些其他SQL语法来解决，我使用 CASE ... WHEN ... THEN ... ELSE ... END ，这可能会被更好的SQL（如果可能）。但

  CONCAT（％s.first_name，CASE WHEN％s .middle_name =THENELSE CONCAT（，％s.middle_name，）END，％s.last_name）
  

最终解决方案

在模型构造函数中：

  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 =THENELSE CONCAT（，％s .middle_name，）END，％s.last_name）'，$ this-> alias，$ this-> alias，$ this-> alias，$ this-> alias 
）; 
} 
  

最后，通常的 public $ displayField = full_name';

现在，无论你做什么 $ this-> RelatedModel-> UserAlias-> find（'list'）; 在控制器中，你得到一个数组 id 和 full_name

希望这有助于某人，或者至少是自己的日志：）

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?

解决方案

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 :)

这篇关于CakePHP 2.X：复合（连接几个db列）virtualfield作为displayfield的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！