php - 通过 CakePHP 3 ORM 查询动态添加列到查询结果

Question

我正在尝试使用 CakePHP 3.7 ORM 编写一个查询，它需要在结果集中添加一列。我知道在 MySQL 中这种事情是可能的：MySQL: Dynamically add columns to query results

到目前为止，我已经实现了 2 个自定义查找器。第一个如下：

// src/Model/Table/SubstancesTable.php
public function findDistinctSubstancesByOrganisation(Query $query, array $options)
{
    $o_id = $options['o_id'];

    $query = $this
        ->find()
        ->select('id')
        ->distinct('id')
        ->contain('TblOrganisationSubstances')
        ->where([
            'TblOrganisationSubstances.o_id' => $o_id,
            'TblOrganisationSubstances.app_id IS NOT' => null
        ])
        ->orderAsc('Substances.app_id')
        ->enableHydration(false);

    return $query;
}

第二个自定义查找器：

// src/Model/Table/RevisionSubstancesTable.php
public function findProductNotifications(Query $query, array $options)
{
    $date_start = $options['date_start'];
    $date_end = $options['date_end'];

    $query = $this
        ->find()
        ->where([
            'RevisionSubstances.date >= ' => $date_start,
            'RevisionSubstances.date <= ' => $date_end
        ])
        ->contain('Substances')
        ->enableHydration(false);

    return $query;
}

我正在使用 Controller 中的查找器对其进行测试：

$Substances = TableRegistry::getTableLocator()->get('Substances');
$RevisionSubstances = TableRegistry::getTableLocator()->get('RevisionSubstances');

$dates = // method to get an array which has keys 'date_start' and 'date_end' used later.

$org_substances = $Substances->find('distinctSubstancesByOrganisation', ['o_id' => 123);

if (!$org_substances->isEmpty()) {

    $data = $RevisionSubstances
        ->find('productNotifications', [
            'date_start' => $dates['date_start'],
            'date_end' => $dates['date_end']
        ])
        ->where([
            'RevisionSubstances.substance_id IN' => $org_substances
        ])
        ->orderDesc('RevisionSubstances.date');

    debug($data->toArray());
}

这背后的逻辑是，我使用第一个自定义查找器来生成一个查询对象，该对象包含表中的唯一（DISTINCT在 SQL 中）id字段substances，基于特定公司（由o_id字段表示）。然后通过实现将这些输入到第二个自定义查找器中where(['RevisionSubstances.substance_id IN' ...。

这有效并为我提供了所有正确的数据。该语句的输出示例debug()如下：

(int) 0 => [
    'id' => (int) 281369,
    'substance_id' => (int) 1,
    'date' => object(Cake\I18n\FrozenDate) {

        'time' => '2019-09-02T00:00:00+00:00',
        'timezone' => 'UTC',
        'fixedNowTime' => false

    },
    'comment' => 'foo',
    'substance' => [
        'id' => (int) 1,
        'app_id' => 'ID000001',
        'name' => 'bar',
        'date' => object(Cake\I18n\FrozenDate) {

            'time' => '2019-07-19T00:00:00+00:00',
            'timezone' => 'UTC',
            'fixedNowTime' => false

        }
    ]
],

我遇到的问题如下：返回的每个结果都包含一个app_id字段（['substance']['app_id']在上面的数组中）。我需要做的是COUNT()基于此对另一个表执行计数（在 MySQL 中），然后将其添加到结果集中。

由于几个原因，我不确定如何执行此操作。首先，我的理解是自定义查找器返回查询对象，但此时不执行查询。因为我还没有执行查询 - 直到调用$data->toArray() - 我不确定我将如何以app_id可以每行引用它的方式引用它？

给我所需结果的等效 SQL 是这样的：

SELECT COUNT (myalias.app_id) FROM (
    SELECT
        DISTINCT (tbl_item.i_id),
        tbl_item.i_name,
        tbl_item.i_code,
        tbl_organisation_substances.o_id,
        tbl_organisation_substances.o_sub_id,
        tbl_organisation_substances.app_id,
        tbl_organisation_substances.os_name
    FROM
        tbl_organisation_substances
    JOIN tbl_item_substances
        ON tbl_organisation_substances.o_sub_id = tbl_item_substances.o_sub_id 
    JOIN tbl_item
        ON tbl_item.i_id = tbl_item_substances.i_id
    WHERE
        tbl_item.o_id = 1
        AND
        tbl_item.date_valid_to IS NULL
        AND
        tbl_organisation_substances.app_id IS NOT NULL
    ORDER BY
        tbl_organisation_substances.app_id ASC
) AS myalias
WHERE myalias.app_id = 'ID000001'

这是一个COUNT()where the app_idis ID000001。

所以在我之前给出的数组中，我需要在数组中添加一些东西来保存它，例如

 'substance' => [
     // ...
 ],
 'count_app_ids' => 5

（假设上面的查询返回了 5 行）。

我有上面查询中提到的所有表的表类。

所以我的问题是，你如何使用 ORM 编写它，并在执行查询之前将结果添加回结果集中？

这甚至可能吗？我能想到的唯一其他解决方案是将数据（从我拥有的查询中）写入一个临时表，然后执行UPDATE基于app_id. 但我真的不热衷于该解决方案，因为这样做可能存在巨大的性能问题。此外，我希望能够对我的查询进行分页，因此理想情况下需要将所有内容限制在 1 个 SQL 语句中，即使它是跨多个查找器完成的。

我用 MySQL 和 CakePHP 对此进行了标记，因为我什至不确定从 MySQL 的角度来看这是否可以实现，尽管它确实在链接的 SO 帖子上看起来可以做到？这增加了必须使用 Cake 的 ORM 编写等效查询的复杂性。

标签： phpmysqlcakephpquery-buildercakephp-3.x