如何动态修改CakePHP中belongsTo关联的查询条件？

当前实现 #

在我的OrdersTable.php中：

$this->belongsTo('OrderStatuses', [
    'foreignKey' => ['order_status_id', 'language_id'],
    'joinType' => 'INNER',
    'className' => 'OrderStatuses',
]);

当我使用find()查询包含OrderStatuses的Orders时，上述关联会生成如下SQL：

SELECT * FROM
    `order` `Orders`
        INNER JOIN `order_status` `OrderStatuses` ON (
                `OrderStatuses`.`order_status_id` = (`Orders`.`order_status_id`)
            AND `OrderStatuses`.`language_id` = (`Orders`.`language_id`)
        ) WHERE
        `Orders`.`order_id` = 123456

具体来说，OrderStatuses.language_id = (Orders.language_id)会选择对应订单语言的状态翻译。

需求目标 #

我希望按当前用户的语言选择状态，而非订单本身设置的语言。因此需要指定一个特定的language_id，即关联时使用固定整数值。

SELECT * FROM
    `order` `Orders`
        INNER JOIN `order_status` `OrderStatuses` ON (
                `OrderStatuses`.`order_status_id` = (`Orders`.`order_status_id`)
            AND `OrderStatuses`.`language_id` = (5)
        ) WHERE
        `Orders`.`order_id` = 123456

由于用户可切换语言，需要根据当前环境中的language_id动态实现此逻辑。虽以下尝试在表类内进行，但最终需通过$table->associations()在外部动态修改关联。

已尝试方案 #

$this->belongsTo('OrderStatuses', [
    'foreignKey' => ['order_status_id', 'language_id'],
    'conditions' => [
        'OrderStatuses.language_id' => 5,
    ],
    'joinType' => 'INNER',
    'className' => 'OrderStatuses',
]);

此方案导致未在"order"表中找到记录，生成的SQL如下：

INNER JOIN `order_status` `OrderStatuses` ON (
  `OrderStatuses`.`language_id` = 5 
  AND (
    `OrderStatuses`.`order_status_id` = (`Orders`.`order_status_id`) 
    AND `OrderStatuses`.`language_id` = (`Orders`.`language_id`)
  )   )

$this->belongsTo('OrderStatuses', [
    'foreignKey' => ['order_status_id', 'language_id'=>5],
    'joinType' => 'INNER',
    'className' => 'CakePHPOpencart.OrderStatuses',
]);

此方案导致数据库错误，生成的SQL如下：

INNER JOIN `order_status` `OrderStatuses` ON (
  `OrderStatuses`.`order_status_id` = (`Orders`.`order_status_id`) 
  AND `OrderStatuses`` = (`Orders`.`5`)   )

更新1 #

听起来你可能需要从第一个示例的foreignKey选项中移除language_id键

若不在关联的foreignKey中包含language_id，会出现如下错误：

Cannot match provided foreignKey for "OrderStatuses", got "(order_status_id)" but expected foreign key for "(order_status_id, language_id)"

显然这是因为OrderStatusesTable（Orders关联的表）的主键是复合主键，包含language_id：

// OrderStatusesTable::initialize()
$this->setPrimaryKey(['order_status_id', 'language_id']);

这是通过bin/cake bake生成的，因为实际数据库表使用复合主键。bake工具会提示：

One moment while associations are detected.
<warning>Bake cannot generate associations for composite primary keys at this time</warning>.

Baking table class for OrderStatuses...

Creating file (…)/OrderStatusesTable.php
Wrote `(…)/OrderStatusesTable.php`

内容的提问来源于stack exchange，提问作者ᴍᴇʜᴏᴠ