0

cakephp 3.2.3x

I am trying to show custom fields from right join which is contained (left joined) with an other table and it is not working. How ever contain custom fields is working.

Note 1: When I get rid of contain() my ORM works as I am expecting.

$properties = $this->find() 
            ->select([
                'Property.id', 'Property.company_id', 'Property.address1', 'Property.postcode', 
                'Tenancies.property_id', 'Tenancies.start_date', 'Tenancies.end_date', 'Tenancies.deposit_total',
            ])
            ->rightJoin(['Tenancies' => 'tenancy'],[
                'Tenancies.property_id = Property.id',
                'Tenancies.active = 1'
            ])
            ->contain([
                'Tenancies.Tenants' => function($q) {
                    return $q
                        ->select([
                            'Tenants.id', 'Tenants.stage', 'Tenants.tenancy_id', 'Tenants.holding_fee',
                        ])
                        ->where([
                            'active = 1',
                        ]);
                    }
            ])
            ->where(['Property.active = 1', $conditions]);

Note 2: Generated sql is correct but it is not applying to the query.

'SELECT Property.id AS Property__id, Property.company_id AS Property__company_id, Property.address1 AS Property__address1, Property.postcode AS Property__postcode, Tenancies.property_id AS Tenancies__property_id, Tenancies.start_date AS Tenancies__start_date, Tenancies.end_date AS Tenancies__end_date, Tenancies.deposit_total AS Tenancies__deposit_total FROM property Property RIGHT JOIN tenancy Tenancies ON (Tenancies.property_id = Property.id AND Tenancies.active = 1) WHERE (Property.active = 1 AND Property.company_id = :c0)'

The query should show me only 3 fields of 'Tenancies' but it retrieves all the Tenancies fields.

(int) 0 => [
    'id' => (int) 102,
    'company_id' => (int) 3,
    'address1' => 'Grace Dieu Court',
    'postcode' => 'LE11 4QH',
    'tenancies' => [
        (int) 0 => [
            'id' => (int) 16,
            'property_id' => (int) 102,
            'landlord_id' => (int) 65,
            'agent_id' => (int) 7,
            'company_id' => (int) 3,
            'bedroom' => (int) -1,
            'created' => object(Cake\I18n\FrozenTime) {
                'time' => '2015-05-08T09:30:41+00:00',
                'timezone' => 'UTC',
                'fixedNowTime' => false             
            },
            'active' => true,
             ...
             ...  ## } The rest of all fileds
            'tenants' => [
                (int) 0 => [
                    'id' => (int) 16,
                    'stage' => (int) 7,
                    'tenancy_id' => (int) 16,
                    'holding_fee' => (float) 50
                ],
                (int) 1 => [
                    'id' => (int) 17,
                    'stage' => (int) 7,
                    'tenancy_id' => (int) 16,
                    'holding_fee' => (float) 50
                ]
            ]
        ]
    ]
],
Fury
  • 4,643
  • 5
  • 50
  • 80
  • Possible duplicate of [CakePHP3: select() and dot notation associated](http://stackoverflow.com/questions/35706399/cakephp3-select-and-dot-notation-associated) – ndm Apr 03 '16 at 16:30
  • @ndm The ORM retrieves all the rightJoin() or innerJoinWith() fields when I use the contain() – Fury Apr 03 '16 at 16:55
  • It's not a matter of `rightJoin/innerJoinWith` usage, you're simply not defining the fields to select (properly). For `hasMany/belongsToMany` associations, you cannot select fields for a containment from anywhere else than the corresponding containment configuration. See the possible duplicate for an example of how define the fields. – ndm Apr 03 '16 at 17:08

0 Answers0