CODEWITHSUNDEEP
phpcodeigniteractiverecordsql-likelogical-grouping
Pradyumn Shrivastava
Pradyumn Shrivastava

Reputation: 409

How to use like, or_like and get_where together in Codeigniter 3

I'm trying to search with a keyword but only in the rows where business_type is manufacturer but it's not working and it's getting all the rows. This is the method in my model:

public function search($keyword) {
    $this->db->like('category',$keyword);
    $this->db->or_like('keyword',$keyword);
    $this->db->or_like('products_deals_with',$keyword);
    $this->db->or_like('buisness_name',$keyword);
    $params['conditions'] = array(
        'business_type' => 'manufacturer'
    );
    $query = $this->db->get_where('business_listings', $params['conditions']);
    return $query->result_array();
}

Generated query is:

SELECT * FROM `business_listings`
WHERE `category` LIKE '%%' ESCAPE '!'
OR `keyword` LIKE '%%' ESCAPE '!'
OR `products_deals_with`LIKE '%%' ESCAPE '!'
OR `buisness_name` LIKE '%%' ESCAPE '!'
AND `business_type` = 'manufacturer'

Upvotes: 3

Views: 15766

Answers (3)

mickmackusa
mickmackusa

Reputation: 48070

In one of my projects (running CI 3.1.11), I noticed the following wrapper methods for the protected _like() method in the query builder class:

  • like()
  • not_like()
  • or_like()
  • or_not_like()

All of these wrapper methods have the exact same method signature:

mixed  $field,
string $match = '',
string $side = 'both',
?bool  $escape = null

See some online examples of the query building method structures at rhivent/master_codeigniter2 and sanchit237/codeigniter-3.1.11

Pay particular attention to the first parameter -- $field is mixed (not merely a string). Actually, the parameter could be more explicitly/meaningfully typed as string|array. The starting portion of the internal method body looks like this:

protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '', $escape = NULL)
{
    if (!is_array($field))
    {
        $field = array($field => $match);
    }

This means that if the first parameter is an array (an associative array is expected), it will be iterated to build a dynamic number of LIKE conditions. In such a case, the second parameter is completely ignored.

If additional conditions will be involved, grouping of the OR conjugated LIKE conditions is necessary.

This discovery reveals that searching for rows which contain a nominated string in one of any nominated columns can be done with the following minimized script:

public function search(string $keyword): array
{
    return $this->db
        ->group_start()
        ->or_like(
              array_fill_keys(
                  ['category', 'keyword', 'products_deals_with', 'buisness_name'],
                  $keyword
              )
        )
        ->group_end()
        ->get_where(
              'business_listings',
              ['business_type' => 'manufacturer']
        )
        ->result_array();
}

The built SQL (quoting may vary based on your db dialect / configuration; I've adjusted the formatting for readability):

SELECT *
FROM "business_listings"
WHERE (
    "category" LIKE '%foo%' ESCAPE '!'
    OR "keyword" LIKE '%foo%' ESCAPE '!'
    OR "products_deals_with" LIKE '%foo%' ESCAPE '!'
    OR "buisness_name" LIKE '%foo%' ESCAPE '!'
)
AND "business_type" = 'manufacturer'

  • If there were no additional WHERE conditions, then the group_start() and group_end() calls could be omitted.

  • There is no need for a select() call because when omitted, the default rendering is SELECT *.

  • There is no from() call because the table declaration is made as the first parameter of get_where().

  • Due to the impossibility of duplicate keys in one level of an array, I assume the only way to use a column more than once would be "trickery" like adding irrelevant parentheses.

    [
    'category' => 'foo', 
    '(category)' => 'bar',
    '((category))' => 'buzz'
]

  • The _like() method is capable of suitably omitting identifier quoting when the column/key declaration is something like REPLACE(category, '''', '')

  • In my CI project (PDO & PostGres), there are dialect-specific customizations to implement ILIKE comparisons for case-insensitive matching -- these calls often require the appending of a collation after each non-deterministic column (COLLATE "en-AU-x-icu").
    "{$prefix} {$k} {$not} ILIKE {$v} COLLATE \"en-AU-x-icu\""

Upvotes: 0

Ranjeet Bisht
Ranjeet Bisht

Reputation: 11

Just add $this->db->group_start(); and $this->db->group_end();

$this->db->group_start();
$this->db->like('category',$keyword);
$this->db->or_like('keyword',$keyword);
$this->db->or_like('products_deals_with',$keyword);
$this->db->or_like('buisness_name',$keyword);
$this->db->group_end();

$params['conditions'] = array(
    'business_type' => 'manufacturer'
);

$query = $this->db->get_where('business_listings', $params['conditions']);
return $query->result_array();

Upvotes: 1

Pradyumn Shrivastava
Pradyumn Shrivastava

Reputation: 409

I've found the solution. I've to use $this->db->group_start(); and $this->db->group_end();

public function search($keyword) {

    $this->db->select('*');
    $this->db->where("business_type = 'manufacturer'");
    $this->db->group_start();
    $this->db->like('category',$keyword);
    $this->db->or_like('keyword',$keyword);
    $this->db->or_like('products_deals_with',$keyword);
    $this->db->or_like('buisness_name',$keyword);
    $this->db->group_end();
    $query = $this->db->get('business_listings');
    // echo $this->db->last_query();
    return $query->result_array();

}

Generated Query:

SELECT * FROM `business_listings`
WHERE `business_type` = 'manufacturer'
AND (
`category` LIKE '%%' ESCAPE '!'
OR `keyword` LIKE '%%' ESCAPE '!'
OR `products_deals_with` LIKE '%%' ESCAPE '!'
OR `buisness_name` LIKE '%%' ESCAPE '!' )

Upvotes: 16

Related Questions