CodeIgniter 4: How to write conditional statements using the query builder chain. I.e., if...then

The following returns a mysqli_sql_exception error in the live server. I have traced the error from the following block of code:

public function getByOPCR($opcr_id, $kpi_ids)
{
return $this->select('opcr_success_indicators.*, kpis.name, kpis.datatype')
   ->join('kpis', 'opcr_success_indicators.kpi_id = kpis.id', 'left')
   ->where('opcr_success_indicators.opcr_id', $opcr_id)
   ->whereIn('opcr_success_indicators.kpi_id', $kpi_ids)
   ->findAll();
}    

where
$opcr_id is a number and
$kpi_ids is an array

Everything works fine in my localhost, but the server returns the following:
> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5

I'm using CodeIgniter 4 with the following server information:

Localhost:

• Apache 2.4.46
• MariaDB 10.4.14
• PHP 7.4.9 (VC15 X86 64bit thread safe) + PEAR

Live Server

• PHP 7.4
• MySQL 8

I've been spending quite a lot of time figuring out what's wrong in the live server. Your help is greatly appreciated 🤧

I managed to get the lastQuery used and it returns a valid syntax:

SELECT `opcr_success_indicators`.*, `kpis`.`name`, `kpis`.`datatype`
FROM `opcr_success_indicators`
LEFT JOIN `kpis` ON `opcr_success_indicators`.`kpi_id` = `kpis`.`id`
WHERE `opcr_success_indicators.opcr_id` = '1'
AND `opcr_success_indicators.kpi_id` IN ('1','2','3')

I tried logging d($kpi_ids[0]) it returns an undefined offset 0.

Prior to it, the following returns a valid array.

d($kpi_ids);
d(gettype($kpi_ids));
dd(array_keys($kpi_ids));

It all breaks when the query is executed: ->findAll() or get()->getResult()

Explanation

This is so because when the actual argument $kpi_ids is an empty array, the query builder function:

->whereIn('opcr_success_indicators.kpi_id', $kpi_ids)

produces ...WHERE opcr_success_indicators.kpi_id IN () , and this is incorrect syntax in MySQL or MariaDB.

Solution

To protect yourself from this, you need to use conditional statements.

Solution Before CodeIgniter Version 4.3.0:

public function getByOPCR($opcr_id, $kpi_ids)
{
    $builder = $this->select('opcr_success_indicators.*, kpis.name, kpis.datatype')
        ->join('kpis', 'opcr_success_indicators.kpi_id = kpis.id', 'left')
        ->where('opcr_success_indicators.opcr_id', $opcr_id);

    if (!empty($kpi_ids)) {
        $builder->whereIn('opcr_success_indicators.kpi_id', $kpi_ids);
    }

    return $builder->findAll();
}

Solution After CodeIgniter Version 4.3.0:

Note: empty arrays are falsy in PHP. php.net: Converting to boolean

public function getByOPCR($opcr_id, $kpi_ids)
{
    return $this->select('opcr_success_indicators.*, kpis.name, kpis.datatype')
        ->join('kpis', 'opcr_success_indicators.kpi_id = kpis.id', 'left')
        ->where('opcr_success_indicators.opcr_id', $opcr_id)
        ->when($kpi_ids, static function ($query, $kpi_ids) {
            $query->whereIn('opcr_success_indicators.kpi_id', $kpi_ids);
        })
        ->findAll();
}

Conditional Statements

$builder->when()

> This allows modifying the query based on a condition without breaking
> out of the query builder chain. The first parameter is the condition,
> and it should evaluate to a boolean. The second parameter is a
> callable that will be ran when the condition is true.
>
> Since the condition is evaluated as true, the callable will be
> called. The value set in the condition will be passed as the second
> parameter to the callable so it can be used in the query.