How to get only selected fields from contain in cakephp3

Question

I have a query as below but I need only certain fields not all the fields. I have set the autoFields to false but the query still returns all the fields.

$tenancies = $this
        ->find('all')
        ->autoFields(false)
        ->select([ 
            'Tenancy.id', 'Tenancy.created', 'Tenancy.stage',
            'Properties.id', 'Properties.address1', 'Properties.postcode',
            'Tenants.stage',
        ])
        ->contain('Properties', function(\Cake\ORM\Query $query) {
            return $query->where([
                'Properties.active' => 1
            ]);
        })
        ->contain(['Tenants'])
        ->leftJoinWith('Tenants', function(\Cake\ORM\Query $query) {
            return $query
                ->autoFields(false)
                ->select([
                    'id', 'stage'
                ])
                ->where([
                    'Tenants.active' => 1
                ]);
        })
        ->where([
            'Tenancy.active' => 1,
            $conditions
        ])
        ->order([
            'Tenancy.created' => 'DESC',
            'Tenants.tenancy_id'
        ])
        ->group(['Properties.id'])
        ->autoFields(false); 

    return $tenancies;

Prints =>

object(App\Model\Entity\Tenancy) {

    'id' => (int) 3934,
    'created' => object(Cake\I18n\FrozenTime) {

        'time' => '2016-03-20T18:25:20+00:00',
        'timezone' => 'UTC',
        'fixedNowTime' => false

    },
    'stage' => (int) 2,
    'tenants' => [
        (int) 0 => object(Cake\ORM\Entity) {

            'id' => (int) 8922,
            'user_id' => (int) 56456,
            'tenancy_id' => (int) 3934,
            'needs_guarantor' => true,
            'guarantor_id' => null,
            'holding_fee' => (float) 0,
            ...
            ...

Note: Using matching or leftJoinWith I can select the fields that I need but it doesn't work on contain

Answer 1

The main answer is you need to set the associations correctly. Once the associations are set correctly you can print it as simple as this.

return $this->find()
                ->select([
                    'Property.id', 'Property.company_id', 'Property.address1', 'Property.address2', 
                    'Property.address3','Property.postcode',  
                ])
                ->contain([                    
                    'Tenancies' => function($q) {
                        return $q
                            ->select([
                                'Tenancies.id','Tenancies.property_id','Tenancies.created',
                                'Tenancies.stage', 
                            ])
                            ->contain([
                                'Tenants' => function($q) {
                                    return $q
                                        ->select([
                                            'Tenants.id', 'Tenants.stage', 'Tenants.tenancy_id', 'Tenants.holding_fee',
                                        ])
                                        ->where([
                                            'active = 1',
                                        ]);
                                    }
                            ])
                            ->contain([
                                'Tenants'  
                            ])
                            ->where([
                                'Tenancies.active = 1',
                            ]);
                    }
                ])
                ->where(['Property.active = 1', $conditions])
                ->toArray();