Cakephp querying Model by strict associated data

Question

I have a model X which is associated with Model Y. X hasMany Y. Y has an attribute "status". I want to find all X such that the status of ALL Ys associated to X is equal to "active", i.e all Y.status =="active".

Here's a simplified schema:

X

id....name

0.....test
1.....two

Y

id....status....x_id

0.....active.....0
1.....inactive..0
2.....active....1
3.....active....1

I Want my query to return x with the ID 1, since both its associated Y records are active. I don't want it to return x with ID 0 since both its Y records aren't active.

I have tried using join queries, but they keep returning the unexpected result by returning both X. I'm using Cakephp 2.2.

Answer 1

$data = TableRegistry::get('Y')->find()->where(['x_id' => 1);

//with assosiation
$data = TableRegistry::get('Y')->find()->where(['x_id' => 1])->contain(['X']);

full documentation here http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html

EDIT

to search by status you can add search parameter in where()

$data = TableRegistry::get('Y')
                ->find()
                ->where(['x_id' => 1, 'status' => 'active'])
                ->contain(['X']);

Using CakePHP 2.x

$data = $this->Y->find('all',
        array(
            'conditions'=>array('x_id'=>1,'status'=>'active'),
            'contain' => array('X')
        ));