CODEWITHSUNDEEP
phpmysqlcodeignitercodeigniter-4codeigniter-query-builder
be_mpty
be_mpty

Reputation: 33

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:

Live Server

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()

Upvotes: 1

Views: 765

Answers (1)

steven7mwesigwa
steven7mwesigwa

Reputation: 6720

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.

Upvotes: 1

Related Questions