How to retrieve only those records that have at least one associated record?

Question

The data should show only the tenancies that contains data. In normal query normally I do this be right join.

I know contain() is left join on default but can contain be right join at all?

My query:

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

e.g. I need to get read of nodes that tenancies that are null 'tenancies' => [], and it should show only node 1.

Prints=>

(int) 0 => object(App\Model\Entity\Property) {

        'id' => (int) 95,
        'company_id' => (int) 3,
        'address1' => '40 Arthur Street',
        'postcode' => 'LE11 3AY',
        'tenancies' => [],
        '[new]' => false,
        '[accessible]' => [
            '*' => true
        ],
        '[dirty]' => [],
        '[original]' => [],
        '[virtual]' => [],
        '[errors]' => [],
        '[invalid]' => [],
        '[repository]' => 'Property'

    },
    (int) 1 => object(App\Model\Entity\Property) {

        'id' => (int) 102,
        'company_id' => (int) 3,
        'address1' => 'Grace Dieu Court',
        'postcode' => 'LE11 4QH',
        'tenancies' => [
            (int) 0 => object(App\Model\Entity\Tenancy) {

                'id' => (int) 16,
                'property_id' => (int) 102,
                'created' => object(Cake\I18n\FrozenTime) {

                    'time' => '2015-05-08T09:30:41+00:00',
                    'timezone' => 'UTC',
                    'fixedNowTime' => false

                },
                'stage' => (int) 6,
                'landlord_offer_sent' => false,
                'offer_letter_contract_id' => (int) 37,
                'tenants' => [
                    (int) 0 => object(Cake\ORM\Entity) {

                        'id' => (int) 16,
                        'stage' => (int) 7,
                        'tenancy_id' => (int) 16,
                        'holding_fee' => (float) 50,
                        '[new]' => false,
                        '[accessible]' => [
                            '*' => true
                        ],
                        '[dirty]' => [],
                        '[original]' => [],
                        '[virtual]' => [],
                        '[errors]' => [],
                        '[invalid]' => [],
                        '[repository]' => 'Tenants'

                    },
                    (int) 1 => object(Cake\ORM\Entity) {
                    ...
                    ...
                    ...

I have tried inner join 'joinType' => 'INNER' but no luck:

class TenancyTable extends Table
{

    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('tenancy');
        $this->displayField('id');
        $this->primaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsTo('Properties', [
            'foreignKey' => 'property_id',
            'className' => 'property',
            'joinType' => 'INNER'
        ]);

Answer 1

Not possible via containments for hasMany associations

This cannot be done with containments, as hasMany ones are being retrieved via a separate query. Instead you'll have to add a proper join yourself, an INNER join (more portable than a RIGHT join) could do it if you want to restrict the results to only those that have associated Tenancies.

A simple Query::innerJoinWith() without conditions, and some grouping to avoid duplicates

$properties = $this
    ->find()
    // ...
    ->innerJoinWith('Tenancies')
    ->group('Property.id');

... and you're done, this will add a join like

INNER JOIN tenancies Tenancies ON Property.id = Tenancies.property_id

innerJoinWith() works for all other types of associations too

For belongsTo and hasOne associations however, it could be done via containments, as for these types of associations, all data is being retrieved in the same query. To filter by those that have an associated record, just change the join type to INNER, like

$this
    ->find()
    ->contain([
        'BelongsToAssociated' => [
            'joinType' => 'INNER'
        ]
    ]);

See also

• Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data
• Cookbook > Database Access & ORM > Query Builder > Using innerJoinWith

Answer 2

This worked when I simply changed contains to matching.

It seems to be possible, see Filtering By Associated Data