[FIXED] Cakephp 3.x Filter rows on array in multilevel joins

Issue

First important structure of my database:

teachers:
id, int
username, varchar

certificates:
id, int
teacher_id, int
vality_date, date

languages:
id, int
certificate_id, int
language_id, int

Teachers hasMany Certificates hasMany Languages

A teacher can have multiple certificates with multiple languages. Multiple languages can get splitted on multiple certificates.

I'm trying to find a cakephp-way to get all teachers, who have a valid certification for defined languages, probably in multiple certifications, but it's hard to build a query in cakephp. I tried so much, but I always get teachers who have all or only one of the requested languages.

How would you solve this problem?

Solution

You would do this through joins (or with what the QueryBuilder calls matching().

If you would use:

$teachers = $this->Teachers->find()
    ->innerJoinWith('Certificates.Languages');

...you would effectively get a table of teachers with for each teacher the certificates and for each certificate the language. There would probably be duplicates as well.

You can now filter on the joined data (and keep out duplicates):

$lang_list = ['NL', 'DE'];

$teachers = $this->Teachers->find()
    ->where(['Languages.lang IN' => $lang_list])
    ->innerJoinWith('Certificates.Languages')
    ->group('Teachers.id');

I am not sure if this would work directly, but it is definitely something like this.

The SQL IN keyword can be used to limit to values of an array. Alternatively you could construct multiple AND statements (for whatever reason).
Also note that I personally prefer a ...JoinWith over matching. (For no real reason.)

---

For completeness, with matching() it would look like:

$lang_list = ['NL', 'DE'];
$teachers = $this->Teachers->find()
    ->matching('Certificates.Languages', function ($q) use ($lang_list) {
        return $q->where(['lang IN' => $lang_list]);
    });

• CakepHP - Using innerJoinWith
• CakePHP - Filtering by associated data

Answered By - Roberto