Excluding hasMany models in CakePHP

Question

Here's my basic setup... Families hasMany Students hasMany Enrollment

Is there a way to exclude a student if enrollment is empty? Here is my find method.

    $options = array( 
        'order' => array('Family.family_last_name'),
        'group' => 'Family.id',
        'contain' => array(
            'Student', 'Student.Enrollment'
        ),
        'joins' => array(
            array(
                'table' => 'students',
                'alias' => 'Student',
                'type' => 'left',
                'conditions' => array(
                    'Family.id = Student.family_id'
                )
            ),
            array(
                'table' => 'enrollment',
                'alias' => 'Enrollment',
                'type' => 'left',
                'conditions' => array(
                    'Enrollment.student_id = Student.id'
                )
            )
        ),
        'conditions' => array(
            'Enrollment.status =' => 'withdrawn'
        )
    );

    $enrollment = $this->Family->find( 'all', $options);

Here is the array that it is returning. How can I remove Jack?

    [Student] => Array
            (
                [0] => Array
                    (
                        [id] => 92
                        [first_name] => Jack
                        [Enrollment] => Array
                            (
                            )

                    )

                [1] => Array
                    (
                        [id] => 93
                        [first_name] => Jill
                        [Enrollment] => Array
                            (
                                [0] => Array
                                    (
                                        [id] => 99
                                        [student_id] => 93
                                        [grade] => 4
                                    )

                            )

                    )
              )

score 1 · Answer 1 · answered Sep 28 '13 at 00:34

Get rid of the left join when you join to the enrollment. Make it an inner join instead.

$options = array( 
    'order' => array('Family.family_last_name'),
    'group' => 'Family.id',
    'contain' => array(
        'Student', 'Student.Enrollment'
    ),
    'joins' => array(
        array(
            'table' => 'students',
            'alias' => 'Student',
            'type' => 'left',
            'conditions' => array(
                'Family.id = Student.family_id'
            )
        ),
        array(
            'table' => 'enrollment',
            'alias' => 'Enrollment',
            'type' => 'inner',
            'conditions' => array(
                'Enrollment.student_id = Student.id'
            )
        )
    ),
    'conditions' => array(
        'Enrollment.status =' => 'withdrawn'
    )
);

$enrollment = $this->Family->find( 'all', $options);

Nope. Tried that and it just ignored the Enrollment.status = withdrawn — Randy Gonzalez, Sep 28 '13 at 01:46
shouldn't be the join between `Family` and `Student` to be an `INNER JOIN?` — arilia, Oct 01 '13 at 09:24
I actually tried every possible combination of joins LOL! Has anyone experienced any issues with contain and joins working together? — Randy Gonzalez, Oct 02 '13 at 05:49

arilia · Answer 2 · 2013-10-04T09:44:17.333

1

I don't understand why you use joins when you have Containable models. By the way I found this solution: it works but I dont't think it's the best way to do it.

In your Family Model

public $hasMany = array
(
    'Student' => array(
        'conditions' => array('Student.id IN (SELECT DISTINCT student_id FROM enrollments)')
    )
);

Or, if you prefer, you can attach this relationship only when needed using

Family->bindModel(array(
    'hasMany' => array (
            'Student' => array(
                'conditions' => array('Student.id IN (SELECT DISTINCT student_id FROM enrollments)')
        )
    )
)

edited Oct 04 '13 at 09:44

answered Oct 04 '13 at 06:50

arilia

9,373
2
20
44

Thank you Arilia, Containable ignores distant associations, and I need to be able to filter results based on those associations. I will try this and see if it works. – Randy Gonzalez Oct 06 '13 at 07:42

Excluding hasMany models in CakePHP

2 Answers2