mysql - Yii 1.1 model relation can't use joined table column on select
问题描述
This is my class
Class System extends CActiveRecord
{
public function relations() {
return array(
'SystemInverterModuleMountGrouped' => array(self::HAS_MANY, 'SystemInverterModule', array('id' => 'system_inverter_id'),
'through' => 'SystemInverter',
'group' => 'system_id, SystemInverterModuleMountGrouped.mount_id',
'condition' => 'SystemInverterModuleMountGrouped.mount_id is not null AND SystemInverterModuleMountGrouped.mount_id != "" AND number_of_mounts > 0 AND
CASE WHEN Mount.unit_of_measure = 1 THEN
SystemInverterModuleMountGrouped.existing_array != 1
ELSE true
END',
'select' => '*, '
. 'SUM(SystemInverterModuleMountGrouped.number_of_mounts) AS number_of_mounts_grouped, ',
'with' => 'Mount'
)
);
}
}
This is working fine, but now I want to sum number_of_mounts in a certain condition
array(
'select' => '*, '
. 'IF(Mount.unit_of_measure IN (1,2,3), 0, SUM(SystemInverterModuleMountGrouped.number_of_mounts)) AS number_of_mounts_grouped, ',
)
It doesn’t work and yii throws an error
Active record “SystemInverterModule” is trying to select an invalid column “IF(Mount.unit_of_measure IN (1”. Note, the column must exist in the table or be an expression with alias.
Notice that I’m able to use Mount.unit_of_measure on the condition
'condition' => 'CASE WHEN Mount.unit_of_measure = 1 THEN '
It works with raw sql query
SELECT IF(Mount.unit_of_measure IN (1,2,3), 0, SUM(SystemInverterModuleMountGrouped.number_of_mounts)) AS number_of_mounts_grouped
FROM `SystemInverterModules` `SystemInverterModuleMountGrouped`
LEFT OUTER JOIN `SystemInverter` `SystemInverter`
ON (`SystemInverter`.`id` = `SystemInverterModuleMountGrouped`.`system_inverter_id`)
LEFT OUTER JOIN `Inventory` `Mount`
ON (`SystemInverterModuleMountGrouped`.`mount_id` = `Mount`.`id`)
WHERE (SystemInverterModuleMountGrouped.mount_id is not null AND SystemInverterModuleMountGrouped.mount_id != "" AND
number_of_mounts > 0 AND
CASE WHEN Mount.unit_of_measure = 1 THEN
SystemInverterModuleMountGrouped.existing_array != 1
ELSE true END
)
AND (`SystemInverter`.`system_id` = '42146')
GROUP BY system_id, SystemInverterModuleMountGrouped.mount_id
ORDER BY sort_mounting ASC
解决方案
在这种情况下,您应该使用数组进行声明select
- 字符串格式不适用于复杂的表达式:
'select' => [
'*',
'IF(Mount.unit_of_measure IN (1,2,3), 0, SUM(SystemInverterModuleMountGrouped.number_of_mounts)) AS number_of_mounts_grouped',
],
推荐阅读
- javascript - 如何使用 html、javascript、jquery 和 regex 从网页中提取 guid
- sql - 如何在 spark sql 中解压地图?
- bitbucket - 我想在 Bitbucket(stash) 中使用一个现有的 repo,现在没有人在使用它
- c# - 无法将值绑定到集合 asp.net core mvc 的模型
- java - 在按钮单击时使用 Android Studio 计算 2 个字符串中的元音
- javascript - 如何使用循环在反应中显示图像
- uber-api - Uber API 集成范围
- ssl - 如何将密码套件设置为 2048 位非对称密钥
- swift - Swift 中 pthread_mutex_lock 的替代方案?
- python - pygame中的子弹典当