CakePHP don't include records whose contained model is empty

Question

So my current paginate settings look like this:

    public $paginate = array(
        'limit' => 30,
        'contain' => array(
                'Model1' => array(
                        'fields' => array('id')
                ),
                'Model2' => array(
                        'conditions' => array(
                                'Model2.type' => 'Sometype'
                        ),
                        'Model3' => array(
                                'fields' => array('someField')
                        )
                )
        )
);

What I want to achieve is that when the contained Model2 happens to be empty (there is no Model2 with the type 'Sometype'), then this record of Model0 (the parent of Model1 and Model2) should not be included in the returned array of $this->paginate('Model0');.

How could this be achieved?

score 0 · Answer 1 · answered Mar 10 '14 at 21:22

I'm not sure if that can be achieved with the containable behavior. I know you can do it with manual joins (disclaimer: I may be off in my query, since I haven't tested it):

$results = $this->Model0->find('all', array(
        'conditions' => array(
            'Model2.id <>' => null,
        ),
        'joins' => array(
            array(
                'table' => 'model2',
                'alias' => 'Model2',
                'type' => 'LEFT',
                'conditions' => array(
                    'Model2.type' => 'Sometype',
                    'Model2.model0_id = Model0.id',
                ),
            ),
            array(
                'table' => 'model3',
                'alias' => 'Model3',
                'conditions' => array(
                    'Model3.model2_id = Model2.id'
                ),
            ),
        ),

    ));

score -1 · Answer 2 · answered Mar 11 '14 at 08:15

Set the root condition to require a valid id for Model2.

public $paginate = array(
    'limit' => 30,
    'conditions'=>array(
            'Model2.id >'=>0
    )
    'contain' => array(
            'Model1' => array(
                    'fields' => array('id')
            ),
            'Model2' => array(
                    'conditions' => array(
                            'Model2.type' => 'Sometype'
                    ),
                    'Model3' => array(
                            'fields' => array('someField')
                    )
            )
    )
);

Depending on your model relationships this might not work. It only works for belongsTo and hasOne I believe. In that case, you should start the pagination from Model2 or use bindModel for temporary model relationships.

score -1 · Answer 3 · answered May 12 '16 at 17:07

Set you conditions at the base level of the query rather than inside the contains. Just make sure to prefix them with the model name.

public $paginate = array(
    'conditions' => array(
        'Model2.type' => 'Sometype'
    ),
    'limit' => 30,
    'contain' => array(
        'Model1' => array(
            'fields' => array('id')
         ),
         'Model2' => array(
             'Model3' => array(
                 'fields' => array('someField')
             ),
         ),
    )

);

CakePHP don't include records whose contained model is empty

3 Answers3