cakephp3, use contain() and filter the result by associated table's field

There is contain() method in cakephp. This method is useful and is often used. But if you are using hasMany or belongsToMany in the table files, and if you try to filter the result in accordance with the associated field in where, you get failed.

$this->find() // Suppose this fetches records from Articles
    ->contain(['Comments'])  // Suppose Articles has many Comments
    ->where(['Comments.type' => 1]); //Error!

Articles Table

id	name
1	test article

Comments Table

id	name	article_id
1	comment	1
2	comment	1
3	comment	1

But if the relationship is hasOne or belongsTo, you do not get any error. You can filter the result by associated table's field.

Articles Table

id	name	comment_id
1	test article	1

Comments Table

id	name
1	comment
2	comment
3	comment

This is because, if the tables are hasMany or belongsToMany, separate SQL statements are executed as follows:

# Comments doesn't exist in the first SQL statement,
# so you can not specify fields of Comments to filter Articles.
SELECT * FROM articles;
SELECT * FROM comments WHERE article_id IN (1, 2, 3, 4, 5); 

If you want to filter the Articles records in accordance with Comments records, use a closure to give conditions to the other SQL statement:

$query = $articles->find()->contain([
    'Comments' => function (\Cake\ORM\Query $q) {
       return $q->where(['Comments.type' => 1]);
    }
]);

So that you will get records of Articles where Comments.type is 1.
(You can also use matching() instead of contain().)

document of cakephp about this