[FIXED] "IN" and "NOT IN" in CakePHP3

Issue

There is an example in the Cookbook:

$query = $cities->find()
   ->where(function ($exp, $q) {
      return $exp->notIn('country_id', ['AFG', 'USA', 'EST']);
});

In SQL this should be aequivalent to: WHERE country_id NOT IN ('AFG', 'USA', 'EST')

Now, I'm trying to use a variable here. Sadly, this won't work:

$query = $cities->find()
   ->where(function ($exp, $q, $variable) {
      return $exp->notIn('country_id', $variable);
});

Any ideas?

Solution

I always find the easiest way to use IN and NOT IN is as follows in CakePHP

$query = $cities->find()
   ->where(['country_id IN' => $variable])

More information on auto generating in clauses is at the following link in the book. You can also use it to cast values to the type of the column automatically. To me it is more readable as well.

https://book.cakephp.org/3.0/en/orm/query-builder.html#automatically-creating-in-clauses

Answered By - KaffineAddict