[FIXED] CakePHP How to limit the fields of both tables in a hasMany query?

Issue

I have two tables in my database:

processo_administrativo hasMany documento_processo_administrativo

And I would like to retrive all records but (for performance reasons) limiting the fields (columns) using select():

So I tried:

<?php

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

But the result was all ProcessoAdministrativo without DocumentoProcessoAdministrativo Example:

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

My another attempt was using enableAutoFields(true) and the result was "almost there":

<?php

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

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

This time, I got the hasMany records (and only with the fields that I defined) but, unfortunately, all fields from ProcessoAdministrativo were retrieved too (but I just want one field: 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,
            }
   //...

So, How Can I limit the fields of both tables using CakePHP find() ?

Solution

Merging hasMany/belongsToMany records (which are being obtained in separate queries) into the results happens on PHP level, therefore you must select the primary key of ProcessoAdministrativo, eg the other side of your foreign key constraint, otherwise the ORM cannot stitch the results together, as it cannot tell which DocumentoProcessoAdministrativo record belongs to wich ProcessoAdministrativo record, as there would be no data that it could match against the processo_administrativo_id foreign key.

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

If you don't want the ProcessoAdministrativo.id field in the results, then you need to filter it out after the records have been queried, for example using a result formatter:

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

            return $row;
        });
    });

Answered By - ndm