cakephp - How to select fields of a contained association?

Question

I would like to execute the following query where I'd like to read only the necessary fields from associated.
I used a dot notation in select() below to better explain what I want.
Basically the select() seems to concern Users only. Is it possible to specify the fields of Sites?

$orders = $this->Orders->find()
    ->contain([
        'Sites.Users'=> function ($q) {
            return $q
                ->select([
                    'Sites.id',
                    'Sites.user_id',
                    'Users.id',
                    'Users.name',
                    'Users.owner_id',
                    'Users.firstname',
                    'Users.lastname'
                ])
                ->autoFields(false);
        },
    ])
    ->first();

Answer 1

You have to configure the containments individually, selecting fields for other containments won't work.

To be exact, you cannot select fields for a containment from anywhere else than the corresponding containment configuration, with the only exception of belongsTo/hasOne associations that are using the join strategy, fields for them can be selected in the select() call of the immediate "parent" query, as these associations are going to be retrieved via a join in that query.

If for example Sites would be a belongsTo association, then you could select the fields for it via the select() call on Orders. If Users would be a belongsTo, but Sites a hasMany, then you could use the select() call for Sites to select fields for Users.

That being said, in your case you either use the queryBuilder option to define callbacks in a nested array structure

contain([
    'Sites' => [
        'queryBuilder' => function ($q) {
            return $q
                ->select([
                    'Sites.id',
                    'Sites.user_id'
                ]);
        },
        'Users' => function ($q) {
            return $q
                ->select([
                    'Users.id',
                    'Users.name',
                    'Users.owner_id',
                    'Users.firstname',
                    'Users.lastname'
                ]);
        }
    ]
])

or, if you don't actually need the query builder, use the fields option

contain([
    'Sites' => [
        'fields' => [
            'Sites.id',
            'Sites.user_id'
        ],
        'Users' => [
            'fields' => [
                'Users.id',
                'Users.name',
                'Users.owner_id',
                'Users.firstname',
                'Users.lastname'
            ]
        ]
    ]
])

See also

• Cookbok > Database Access & ORM > Query Builder > Passing Conditions to Contain
• API > CakeORMQueryBuilder::contain()