Jump to content


Search filter returns wrong results when used with or_where clauses

or_where search filter

  • Please log in to reply
1 reply to this topic

#1 jonathanc

jonathanc

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 06 August 2014 - 07:21 AM

So happy to be using the marvellous GroceryCRUD. Thank you.

 

When using the search function with or_where clauses, I believe the SQL query generated is not as expected because of the way the OR and AND clauses are combined.

 

Here is some SQL generated by DB_active_rec...

 

SELECT COUNT(*) AS `numrows`
FROM (`mytable`)
WHERE `myvalue` =  'x'
OR `myvalue` =  'xx'
OR `myvalue` =  'xy'
OR `myvalue` =  'yz'
AND  `mysearch`  LIKE 'searchvalue'

 

But for the filter to work properly we need to bracket the OR clauses together...

 

SELECT COUNT(*) AS `numrows`
FROM (`mytable`)
WHERE (`myvalue` =  'x'
OR `myvalue` =  'xx'
OR `myvalue` =  'xy'
OR `myvalue` =  'yz')
AND  `mysearch`  LIKE 'searchvalue'

 

To achieve this we can change

if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
		{
			$sql .= "\nWHERE ";
		}

		$sql .= implode("\n", $this->ar_where);

To..


if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
        {
            $sql .= "\nWHERE (";

            $sql .= implode("\n", $this->ar_where);

            $sql .= ")";
        } else{

        $sql .= implode("\n", $this->ar_where);

        }

I suspect other clauses may be similarly affected but haven't had time to check yet.

 

Thank you



#2 semsik

semsik

    Member

  • Members
  • PipPip
  • 11 posts

Posted 09 January 2017 - 07:21 AM

I know this is from 2014 but I had the exact same problem with the bootstrap theme, using a or_where clause breaks the search.

I worked around the problem by using the custom query where from codeigniters (3.x) manual like this

$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);

Now search is working again and no need for adjusting the core of GC.

 

Semsik

Codeigniter 3.x, Grocery Crud 1.5.6 Bootstrap v3 Theme


greetz

Semsik

 

using:

codeigniter 3.+ - GC 1.5.7 - Bootstrap theme v3

 






Also tagged with one or more of these keywords: or_where, search, filter

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users