CakePHP如何限制hasMany查询中两个表的字段?

3gtaxfhh  于 2022-11-24  发布在  PHP
关注(0)|答案(1)|浏览(124)

我的数据库中有两个表:

管理过程有许多管理过程文档

我希望检索所有记录,但(出于性能原因)使用select()限制字段(列):
于是我试探着:

<?php

$tablePA = TableRegistry::get('ProcessoAdministrativo');
    
debug($tablePA->find()
        ->select('codigo_pa')
        ->contain(['DocumentoProcessoAdministrativo' => 
           fn($q) => $q->select(['id','descricao','ordem','processo_administrativo_id'])
        ])->toArray());

但结果是所有进程管理员没有文档进程管理员示例:

[
    (int) 0 => object(App\Model\Entity\ProcessoAdministrativo) {
        'codigo_pa' => 'PGE/001.000091/2021',
        'documento_processo_administrativo' => []
    }
]

我的另一个尝试是使用enableAutoFields(true),结果是“几乎完成”:

<?php

$tablePA = TableRegistry::get('ProcessoAdministrativo');

debug($tablePA->find()
        ->select('codigo_pa')
        ->contain(['DocumentoProcessoAdministrativo' => 
           fn($q) => $q->select(['id','descricao','ordem','processo_administrativo_id'])
        ])->toArray());

这一次,我得到了hasMany记录(并且只包含我定义的字段),但不幸的是,ProcessoAdministrativo中的所有字段也都被检索到了(但我只需要一个字段:codigo_pa):

[
    (int) 0 => object(App\Model\Entity\ProcessoAdministrativo) {
        'codigo_pa' => 'PGE/001.000091/2021',
        'id' => (int) 91877,
        'exito_sucumbencia_id' => null,
        'especializada_id' => (int) 97,
        'classificacao_id' => null,
        'materia_id' => null,
        //...and much more fields here :(
        'documento_processo_administrativo' => [
            (int) 0 => object(App\Model\Entity\DocumentoProcessoAdministrativo) {
                'id' => (int) 120709,
                'descricao' => 'Termo de Abertura',
                'ordem' => (int) 0,
                'processo_administrativo_id' => (int) 91877,
            }
   //...

那么,如何使用CakePHP find()限制两个表的字段呢?

7xzttuei

7xzttuei1#

合并hasMany/belongsToMany记录(在单独的查询中获得的)转换到结果中发生在PHP级别,因此您必须选择ProcessoAdministrativo的主键,例如外键约束的另一侧,否则ORM无法将结果缝合在一起,因为它无法区分哪个DocumentoProcessoAdministrativo记录属于哪个ProcessoAdministrativo记录,因为没有数据可以与processo_administrativo_id外键匹配。

$query = $tablePA
    ->find()
    ->select(['id', 'codigo_pa'])
    ->contain([
        'DocumentoProcessoAdministrativo' => fn($q) => 
            $q->select(['id', 'descricao', 'ordem', 'processo_administrativo_id'])
    ]);

如果您不希望结果中包含ProcessoAdministrativo.id字段,则需要在查询记录后将其过滤掉,例如使用结果格式化程序:

$query = $tablePA
    ->find()
    // ...
    ->formatResults(function (\Cake\Collection\CollectionInterface $results) {
        return $results->map(function ($row) {
            unset($row['id']);

            return $row;
        });
    });

相关问题