Cakephp 2 retrieve data where condition depends on child table

Question

I am having a strange behavior which I do not understand with my cakephp 2. In my Model 'Study' I have a has many relation:

class Study extends AppModel {

public $hasOne = array(
    'SubjectFilter' => array(
        'className' => 'Subject_filter',
        'dependent' => true
    )

    );


public $hasMany = array(

    'ExecutedStudyTable' => array(
            'className' => 'ExecutedStudyTable',
            'foreignKey' =>'study_id',
            'dependent' => true,
    ),   

);

The ExecutedtStudyTable Model looks like this:

class ExecutedStudyTable extends AppModel {

    public $belongsTo= array(
    'Study' => array(
        'className' => 'Study',
        'foreignKey' => 'study_id'
    ),
    'User' => array(
        'className' => 'User',
        'foreignKey' => false,
        'conditions' => array('ExecutedStudyTable.user_id = User.id')
    )

);

} When retrieving data everything looks good until I try to do this:

        $studies = $this -> Study -> find('all',array(
 'conditions' => array(
            'Study.user_id' => $cId,
            'OR'=>array(
                array('Study.state'=>'active'),
                array('Study.state'=>'rehearsal')
            ),                
            'SubjectFilter.studyCode'=>null,
            'ExecutedStudyTable.user_id'=>$user
 )
    ));

I get this error: Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ExecutedStudyTable.user_id' in 'where clause'

Cakephp builds this query like this:

 SELECT `Study`.`id`, `Study`.`user_id`, `Study`.`created`, `Study`.`modified`, `Study`.`started`, `Study`.`completed`, `Study`.`title`, `Study`.`description`, `Study`.`numberParticipants`, `Study`.`state`, `User`.`id`, `User`.`username`, `User`.`password`, `User`.`role`, `User`.`firstName`, `User`.`familyName`, `User`.`email`, `User`.`addressStreet`, `User`.`addressCity`, `User`.`addressZip`, `User`.`phone1`, `User`.`phone2`, `User`.`created`, `User`.`modified`, `User`.`passwordResetCode`, `User`.`passwordResetDate`, `User`.`sendUsernameDate`, `SubjectFilter`.`id`, `SubjectFilter`.`study_id`, `SubjectFilter`.`m`, `SubjectFilter`.`f`, `SubjectFilter`.`age18_24`, `SubjectFilter`.`age25_34`, `SubjectFilter`.`age35_44`, `SubjectFilter`.`age45_54`, `SubjectFilter`.`age55plus`, `SubjectFilter`.`studyCode` FROM `eyevido`.`studies` AS `Study` LEFT JOIN `eyevido`.`users` AS `User` ON (`Study`.`user_id` = `User`.`id`) LEFT JOIN `eyevido`.`subject_filters` AS `SubjectFilter` ON (`SubjectFilter`.`study_id` = `Study`.`id`) WHERE `Study`.`user_id` = 1402402538 AND ((`Study`.`state` = 'active') OR (`Study`.`state` = 'rehearsal')) AND `SubjectFilter`.`studyCode` IS NULL AND `ExecutedStudyTable`.`user_id` = 1130451831

The ExecutedStudyTable is not joined like the SubjectFilter and no alias is defined for the table. Why is this working correctly on the hasOne relation and not on the hasMany? Where do I make the mistake?

I appreciate your replies

Answer 1

If you want to filter a model by associated model's field, one way is by Joining tables.

Note: Mention proper table name for model ExecutedStudyTable. Also mention the join condition between two tables.

$studies = $this->Study->find('all',array(
 'fields' => array('Study.*'),
 'joins' => array(
      array('table' => 'executed_study_table', // Table name
        'alias' => 'ExecutedStudyTable',
        'type' => 'INNER',
        'conditions' => array(
            'ExecutedStudyTable.<field name> = Study.<field>', // Mention join condition here
        )
    )
 ),
 'conditions' => array(
        'Study.user_id' => $cId,
        'OR'=>array(
            array('Study.state'=>'active'),
            array('Study.state'=>'rehearsal')
        ),                
        'SubjectFilter.studyCode'    => null,
        'ExecutedStudyTable.user_id' => $user
  ),
  'recursive' => -1
));