CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordsql-like
Adam
Adam

Reputation: 1975

CodeIgniter active record query with multiple OR LIKE conditions and AND WHERE conditions

I'm looking for a way to use Active Record in CodeIgniter to build my query.

My current code is this:

$this->db->   like('responsible', $user->id);
$this->db->or_like('accountable', $user->id);
$this->db->or_like('consulted',   $user->id);
$this->db->or_like('informed',    $user->id);

// Get the tasks, but only with the correct start or end date
$tasks = $this->db->get_where('level_' . $this->config->item('answer_level'), array('date_end' => $offset, 'ccode' => $user->country));

// Check if tasks exist
if($tasks->num_rows() > 0){
    // Tasks have been found that should have been finished!
    echo $tasks->num_rows() . " tasks found for " . $user->first_name . " that should have been finished!\n";
    $last_month_tasks = $tasks->result();
}

unset($tasks);

Which produces the following SQL:

SELECT *
FROM (`level_3`)
WHERE `date_start` =  -5
AND `ccode` =  'GB'
AND  `responsible`  LIKE '%5%'
OR  `accountable`  LIKE '%5%'
OR  `consulted`  LIKE '%5%'
OR  `informed`  LIKE '%5%'

But I need it to produce this SQL:

SELECT * 
FROM (`level_3`)
WHERE  `date_start` = -5
AND  `ccode` =  'GB'
AND (
`responsible` LIKE  '%5%'
    OR  `accountable` LIKE  '%5%'
    OR  `consulted` LIKE  '%5%'
    OR  `informed` LIKE  '%5%'
)

Upvotes: 2

Views: 701

Answers (2)

mickmackusa
mickmackusa

Reputation: 47894

CodeIgniter ABSOLUTELY DOES support nesting of queries and clauses. In this case, the solution is to encapsulate the set of LIKE conditions in parentheses using group_start() and group_end().

I do want to warn that if the user ids are integers, it is rarely appropriate to use LIKE conditions because this would make the query vulnerable to over-matching substrings of a larger user id. In other words, 12 would match 12, 112, 121, 122, 123, etc. Something about your coding intention is giving me code-smell concerns.

  • or_like() is capable of receiving an associative array where the keys are the columns to be searched and the values are the values to be searched.

  • select('*') does not need to be explicitly written, it is implied when omited.

  • The from() clause and where() conditions can be consolidated into the get_where() call which is an extension to get() which is necessary to actually execute the query. (I see that you are already doing this, I'm just explaining to readers.)

  • Model methods shouldn't be doing any echoing -- they should, at most, return data. Counting the results and validating the data can be done in another layer. Simply return the payload to the calling method.

return $this->db
    ->group_start()
    ->or_like([
        'responsible' => $user->id,
        'accountable' => $user->id,
        'consulted' => $user->id,
        'informed' => $user->id,
    ])
    ->group_end()
    ->get_where(
        'level_' . $this->config->item('answer_level'),
        [
            'date_end' => $offset,
            'ccode' => $user->country,
        ]
    )
    ->result();

Upvotes: 0

Vassilis Barzokas
Vassilis Barzokas

Reputation: 3242

CodeIgniter's ActiveRecord does not support nesting of queries and clauses. You have to do it manually like this:

$this->db->select('*');
$this->db->from('level_' . $this->config->item('answer_level'));
$this->db->where(array('date_end' => $offset, 'ccode' => $user->country));
$this->db->where("(responsible LIKE '%5%' OR accountable LIKE '%5%' OR consulted LIKE '%5%' OR informed LIKE '%5%')");
$tasks = $this->db->get();

Upvotes: 3

Related Questions