2

In cakephp 3 query gives error when added 'IN' condition in contain query.

$itemsquery = $Students->find('all');
$itemsquery->select(['Students.id']);

$itemsquery->matching('CategoriesStudents');
$itemsquery->contain([
'CategoriesStudents' => function ($q) use ($category_ids) {
return $q->where(['CategoriesStudents.category_id IN' => $category_ids]);
}
]);

// debug($itemsquery);
$items = $itemsquery->toArray();

Error

Impossible to generate condition with empty list of values for field (`CategoriesStudents`.`category_id`)

Error: An Internal Error Has Occurred.
An Internal Server Error Occurred

Tables are

  1. categories_students

category_id, student_id

  1. students

  2. category

$category_ids array

Array
(
    [1] => 57
    [2] => 60
    [3] => 58
    [4] => 13
    [5] => 78
    [6] => 56
    [7] => 59
    [8] => 79
    [9] => 95
    [10] => 94
    [11] => 96
    [12] => 93
    [13] => 82
    [14] => 23
    [15] => 82
    [16] => 14
    [17] => 16
    [18] => 101
    [19] => 14
)

Please help

Durgesh
  • 97
  • 3
  • 8
  • You are saying that `$category_ids` contains these values, but the query builder says otherwise, and I'm inclined to belive the query builder, unless you can come up with an example that reproduces the error despite the values being provided. – ndm Jul 04 '19 at 12:12

1 Answers1

3

You can't leave IN operator empty, you must put into it something like NULL. Try to cover the case for an empty Array:

return $q->where(
     ['CategoriesStudents.category_id IN' =>
        count($category_ids) > 0 ?  $category_ids : [null])
]);

Reference: Empty IN clause parameter list in MySQL

Paul Trimor
  • 320
  • 3
  • 15