无法使用cakephp中的containsable从2个表中获取数据

Question

I cant get data from 2 tables with conditions on each. There is no example in the docs. I just need rows from students table where a field is flagged as inactive and corresponding rows from Guardian table the email field is not null. Guardian has many Students. I get results but I get null values for Guardian email. I have tried many combinations with ID, model name etc but I am just not getting it.

$guardianFound = $this->Student->find('all', array(
    'contain' => array( 'Guardian', array(
        'conditions' => array('guardian_email !=' => null),
        'fields' => array('id,guardian_email','guardian_first_name,guardian_last_name')
    )),
    'conditions' => array('student_inactive' => 1),
    'fields'=> array('student_inactive' ),
    'recursive'=> -1
)); 

Result:

(int) 0 => array(
    'Student' => array(
        'student_inactive' => true
    ),
    'Guardian' => array(
        'guardian_email' => '',
        'guardian_first_name' => 'Tulay',
        'guardian_last_name' => 'Karadavut',
        'id' => '100'
    )
)

http://book.cakephp.org/2.0/en/core-libraries/behaviors/containable.html

Answer 1

It looks like your query is working as expected, except that you probably want to exclude where guardian_email is null OR empty.

To specify multiple values for a field in CakePHP, you can usually wrap it in another array, but for your case, since you're negating NULL and empty, it's a little complicated because of how CakePHP handles db field types. See this question for more info: Cake PHP complex find 'OR' is not working properly with null and empty string. So to simplify it you can set your Guardian conditions like so:

'conditions' => array(
    'guardian_email IS NOT NULL',
    'guardian_email != ""'
)

You don't need the recursive key here since you're specifying what to contain using containable. So the final find call would be:

$guardianFound = $this->Student->find('all', array(
    'contain' => array(
        'Guardian' => array(
            'conditions' => array(
                'guardian_email IS NOT NULL',
                'guardian_email != ""'
            ),
            'fields' => array(
                'id', 'guardian_email', 'guardian_first_name', 'guardian_last_name'
            )
        )
    ),
    'conditions' => array('student_inactive' => 1),
    'fields' => array('student_inactive')
));

Note that you're finding all inactive students, then only performing the guardian conditions on the guardians associated with that student. If no Guardian record matches the conditions the field would still be returned but with all the values as NULL.

I'm assuming here that because Student belongsTo Guardian, and there would only be one Guardian per Student, you actually want to only return student records where the guardian email is provided. If that's the case, you just need to move the guardian conditions into the main conditions.

$guardianFound = $this->Student->find('all', array(
    'contain' => array(
        'Guardian' => array(
            'fields' => array(
                'id', 'guardian_email', 'guardian_first_name', 'guardian_last_name'
            )
        )
    ),
    'conditions' => array(
        'student_inactive' => 1,
        'Guardian.guardian_email IS NOT NULL',
        'Guardian.guardian_email != ""'
    ),
    'fields' => array('student_inactive')
));