Add WHERE clause condition starting with CASE using CodeIgniter's query builder methods

Question

I am using active records to access the database. However, an additional feature requires me to use standard SQL query. Is it possible to use both active records and standard SQL query in the same query? Eg:

$this->db->select(...)
         ->from(...)
         -> .....
         ->query(WHERE CASE ..........);

In the standard SQL query, I want to use a WHERE clause with CASE. Is this possible without rewriting the entire query in standard SQL? The active record query is very complex.

Least

Answer 1

As far as I know, you can't combine standard SQL and active record like you're trying to do.

However, you can write your WHERE string manually if you want, and then submit that to the ActiveRecord class. I'm not entirely sure if that's what you're looking for as I have never even used CASE, but it sounds like it might be what you're looking for? Let me know!

$where = "name='Joe' AND status='boss' OR status='active'";

$this->db->where($where);

Crespi

Answer 2

You can absolutely craft custom WHERE clauses without having the rendered SQL corrupted by automated quoting/escaping.

The third parameter of a where() method call is a nullable boolean value which defaults to true. If set to false, then no automated quoting/escaping will be applied.

For example, if you wanted the following WHERE clause:

WHERE CASE `foo` WHEN CURRENT_DATE - 1 THEN 'foo' END IS NOT NULL

then you could turn off escaping, and manually add identifier and string quoting as desired with the following method call.

$this->db->where(
    'CASE ' . $this->db->escape_identifiers('foo') . ' WHEN CURRENT_DATE - 1 THEN ' . $this->db->escape('foo') . ' END',
    'IS NOT NULL',
    false
);

or using interpolation instead of concatenation:

$this->db->where(
    sprintf(
        'CASE %s WHEN CURRENT_DATE - 1 THEN %s END',
        $this->db->escape_identifiers('foo'),
        $this->db->escape('foo')
    ),
    'IS NOT NULL',
    false
);

Privileged