CakePHP 4.4.11：查询中无关联访问其他表的实现方法咨询

需求说明 #

• 筛选潜在客户归档表（Lead Archives）中指定account_id的记录
• 筛选联系人表（Contacts）中指定account_id的记录
• 筛选联系人归档表（Contact Archives）中指定account_id的记录

潜在客户归档表关联设置 #

$this->belongsTo('Users', [
    'setForeignKey' => 'user_id',
    'joinType' => 'INNER'
]);

$this->belongsTo('Contacts', [
    'setForeignKey' => 'contact_id'
]);

$this->belongsTo('Accounts', [
    'setForeignKey' => 'account_id'
]);

我的账户潜在客户组件查询代码 #

$query = $LeadArchives->find('myAccountLeads', [
    'contain' => ['Users', 'Contacts', 'Accounts'],
    'status' => $this->status,
    'user_id' => $this->params[9],
    'account_id' => $this->params[0]
]);

潜在客户归档表查询器（Finder） #

尝试在OR条件中关联联系人归档表时抛出异常，因潜在客户归档表仅通过contact_id关联活跃联系人表，不想新增contact_archive_id外键：

public function findMyAccountLeads(Query $query, array $options): object
{
    $query
        ->where([
            'LeadArchives.status' => $options['status'],
            'LeadArchives.user_id' => $options['user_id'],
            'OR' => [
                ['LeadArchives.account_id' => $options['account_id']],
                ['Contacts.account_id' => $options['account_id']],
                //['ContactArchives.account_id' => $options['account_id']] <-- 此处抛出异常
            ]
        ]);

    return $query;
}

已尝试方案 #

使用Union查询能正确获取联系人表和联系人归档表的记录，但未整合潜在客户归档表的查询：

$queryOne = $Contacts->find()
     ->where([
          'account_id' => 1998
      ]);

$queryTwo = $ContactArchives->find()
      ->where([
          'account_id' => 1998
      ]);

$query = $queryTwo->union($queryOne);

希望将潜在客户归档表的查询与上述Union查询结合，或找到更优实现方式，同时想了解CakePHP 5是否有相关特性支持。

解决方案 #

方案1：Union整合三表查询（字段需匹配） #

Union要求查询返回的字段数量、类型一致，需统一字段结构：

// 潜在客户归档查询，对齐字段名
$leadQuery = $LeadArchives->find()
    ->select([
        'id' => 'LeadArchives.id',
        'account_id' => 'LeadArchives.account_id',
        'record_type' => $query->func()->literal('"lead_archive"'), // 标记记录来源
        // 补充其他需要的字段，保持与另外两个查询的字段数量/类型一致
    ])
    ->where([
        'LeadArchives.status' => $this->status,
        'LeadArchives.user_id' => $this->params[9],
        'LeadArchives.account_id' => $this->params[0]
    ]);

// 联系人表查询
$contactQuery = $Contacts->find()
    ->select([
        'id' => 'Contacts.id',
        'account_id' => 'Contacts.account_id',
        'record_type' => $query->func()->literal('"contact"'),
        // 匹配字段
    ])
    ->where([
        'Contacts.account_id' => $this->params[0]
    ]);

// 联系人归档表查询
$contactArchiveQuery = $ContactArchives->find()
    ->select([
        'id' => 'ContactArchives.id',
        'account_id' => 'ContactArchives.account_id',
        'record_type' => $query->func()->literal('"contact_archive"'),
        // 匹配字段
    ])
    ->where([
        'ContactArchives.account_id' => $this->params[0]
    ]);

// 合并三个查询
$finalQuery = $leadQuery->union($contactQuery)->union($contactArchiveQuery);

方案2：Finder中用子查询关联联系人归档表 #

从潜在客户归档表出发，通过子查询实现OR条件关联：

public function findMyAccountLeads(Query $query, array $options): object
{
    // 子查询：获取指定account_id下的联系人归档对应的contact_id
    $contactArchiveSubquery = $this->ContactArchives->find()
        ->select(['contact_id'])
        ->where(['ContactArchives.account_id' => $options['account_id']]);

    $query
        ->where([
            'LeadArchives.status' => $options['status'],
            'LeadArchives.user_id' => $options['user_id'],
            'OR' => [
                ['LeadArchives.account_id' => $options['account_id']],
                ['Contacts.account_id' => $options['account_id']],
                // 匹配潜在客户关联的联系人是否在目标account的归档列表中
                ['LeadArchives.contact_id IN' => $contactArchiveSubquery]
            ]
        ])
        ->contain(['Users', 'Contacts']);

    return $query;
}

CakePHP 5相关说明 #

CakePHP 5对ORM查询的优化集中在性能和语法简洁度上，Union核心用法与4.x兼容，新增了更便捷的unionAll()调用及子查询语法简化，但上述方案升级后依然适用。

内容的提问来源于stack exchange，提问作者Zenzs