我有
Contact Table : id, first_name, last_name, company_id
Company Table : id, name
我想在Contact Table中创建虚拟字段,因此它将显示为“contactInCompany”
"first_name last_name - Company.name" e.g: Andre Robin - Google
我怎么能实现这一点,我尝试这种方式,但它不起作用,它不接受另一个虚拟字段作为输入
public $virtualFields = array(
'companyName' => 'SELECT name FROM companies where id = Contact.company_id',
'customerWithCompany' => "CONCAT(Contact.first_name, ' ',
Contact.last_name, ' ', Contact.companyName, '')"
);
我也用这种方式尝试过它并不起作用
'customerWithCompany' => "CONCAT(Contact.first_name, ' ', Contact.last_name, '-',
SELECT name FROM companies where id = Contact.company_id)"
我经常需要这个,我会使用它来放入下拉选择框以选择联系人,所以我希望将联系人名称与公司一起显示 解决方法: 您无法使用虚拟字段执行此操作,因为虚拟字段中的键只是别名
例:
class Contact extends AppModel {
public $virtualFields = array(
'companyName' => 'SELECT name FROM companies where id = Contact.company_id',
'customerWithCompany' => "CONCAT(Contact.first_name, ' ',
Contact.last_name, ' ', Contact.companyName, '')"
);
public function getContacts() {
return $this->find('all', array(
'fields'=>array(
'Contact.id',
'Contact.first_name',
'Contact.companyName',
'Contact.customerWithCompany'
)
)
);
}
}
如果从ContactsController调用getContacts()方法
上面的代码将形成此查询:
SELECT Contact.id, Contact.first_name,
(SELECT name FROM companies where id = Contact.company_id) AS Contact__companyName,
CONCAT(Contact.first_name, ' ', Contact.last_name, ' ', Contact.companyName, '') AS Contact__customerWithCompany
FROM contacts AS Contact;
此查询将不会在mysql上执行,因为无法访问另一列的1列别名.
要实现这一点,你必须使用如下的子查询:Cake php将虚拟字段名称转换为Model__(在下面的例子中是Contact__)
SELECT
SubQuery.id,
SubQuery.first_name,
SubQuery.Contact____companyName,
CONCAT(SubQuery.first_name, ' ', SubQuery.last_name, ' ', SubQuery.Contact____companyName, '') AS Contact__customerWithCompany
FROM
(SELECT Contact.id, Contact.first_name, (SELECT name FROM companies where id = Contact.company_id) AS Contact__companyName
FROM contacts AS Contact) AS SubQuery;
如果你想在cake php中构建子查询,请使用DataSource的buildStatement方法
注意:使用联接是上述查询的另一个更好的解决方案,可以在不使用子查询的情况下实现相同的结果
加入:
public function getContacts() {
$this->virtualFields['customerWithCompany'] = "CONCAT(Contact.first_name, ' ', Contact.last_name, ' ', Company.name)";
return $this->find('all', array(
'fields'=>array(
'Contact.id',
'Contact.first_name',
'Contact.last_name',
'Company.name',
'Contact.customerWithCompany'
),
'joins'=>array(
array(
'table'=>'companies',
'alias'=>'Company',
'type'=>'LEFT',
'conditions'=>array(
'Contact.company_id = Company.id'
)
)
)
)
);
}
来源:https://www./content-1-332401.html
|