Grouping LIKE clause (putting parenthesis around)

[daljit3]

        $this->db->select('id, name, description')
                ->from('myfiles')
                ->where('is_public', 0)
                ->limit($limit, $offset);

        if (isset($query_array['q']) && strlen($query_array['q'])) {
            $this->db->like('name', $query_array['q']);
            $this->db->or_like('description', $query_array['q']);
            $this->db->or_like('others', $query_array['q']);
        }

The above code produces this query ...

SELECT `id` , `name` , `description`
FROM `myfiles`
WHERE `is_public` =0
AND
`name` LIKE '%m%'
OR `description` LIKE '%m%'
OR `others` LIKE '%m%'

But I need it to be like this - Please note the parenthesis around LIKE clauses

SELECT `id` , `name` , `description`
FROM `myfiles`
WHERE `is_public` =0
AND 
(
`name` LIKE '%m%'
OR `description` LIKE '%m%'
OR `others` LIKE '%m%'
)

Is there any way to tell CI to put thos parenthesis? I know there is $this->db->query() but would wait to see if this is possible with above code.

[timw4mail]

In the development version, there are methods to do just that:

http://codeigniter.com/nightly_user_guide/database/query_builder.html#query-grouping

[daljit3]

Thanks - I wasn't aware of that option available but It seems like it's for WHERE clause only? I tried group_start() around LIKE clauses but it failed.

            $this->db->group_start();
            $this->db->like('name', $query_array['q']);
            $this->db->or_like('description', $query_array['q']);
            $this->db->or_like('others', $query_array['q']);
            $this->db->group_end();

[timw4mail]

If you are using a 2.x version of CodeIgniter, I don't think it exists yet.

[daljit3]

I should have mentioned it - I am using 3.0-dev version - The above code works i.e. it adds the parenthesis etc but it's for WHERE clause only ... e.g. when I started the group around LIKE clause (above) it generated query like

SELECT `id` , `name` , `description`
FROM `myfiles`
WHERE `is_public` =0
AND (
)
AND
`name` LIKE '%m%'
OR `description` LIKE '%m%'
OR `others` LIKE '%m%'

so you can see it worked and added AND () but didn't group LIKE clause

[ivantcholakov]

On 3.0-dev version I am trying this:

$test = ci()->db
    ->select('COUNT(id) AS n')
    ->from('products')
    ->where('out_of_stock', 0)
    ->group_start()
    ->like('name', $search)
    ->or_like('description', $search)
    ->group_end()
    ->get_compiled_select()
;
var_dump($test);

And here is my result:

SELECT COUNT(id) AS n
FROM `products`
WHERE `out_of_stock` =  0
AND   (
 )
AND  `name`  LIKE '%test%' ESCAPE '!' 
OR  `description`  LIKE '%test%' ESCAPE '!'

[daljit3]

Yes, I know... it doesn't group the like clause at the moment so I am using $this->db->query() to run my query... but it'll be good to know if this feature is planned or not ...

[narfbg]

Check out the feature/db_qb_aliasing branch and see if it works there.

[ivantcholakov]

I have just tested the feature feature/db_qb_aliasing (EllisLab-CodeIgniter-v2.1.0-3078-gf83c436.zip). Code:

$test = get_instance()->db
    ->select('COUNT(id) AS n')
    ->from('products')
    ->where('out_of_stock', 0)
    ->group_start()
    ->like('name', 'test')
    ->or_like('description', 'test')
    ->group_end()
    ->get_compiled_select()
;
var_dump($test);

And this is the result:

SELECT COUNT(id) AS n
FROM `products`
WHERE `out_of_stock` =0
AND   (
`name` LIKE '%test%' ESCAPE '!' 
OR  `description` LIKE '%test%' ESCAPE '!' 
 )

It works there.

[daparky]

Will feature/db_qb_aliasing get merged into version 3?

[narfbg]

Yes it will, it's actually one of the things blocking the 3.0 release that need to be finished (tested, at this point).

[ivantcholakov]

I think about a suggestion, but I hesitate to open a new ticket. Let us reduce our example:

$count = get_instance()->db
    ->select('COUNT(id) AS n')
    ->from('products')
    ->get()
    ->row('n')
;

var_dump($count);

This way I can access a single value using one statement only. By adding a new method within the class CI_DB_result, this case may be written in a simpler way:

$count = get_instance()->db
    ->select('COUNT(id)')
    ->from('products')
    ->get()
    ->value()
;

var_dump($count);

Here I am interested to get the first value of the row, this happens often. What do you think? Is it worthwhile?

[narfbg]

@ivantcholakov Doesn't this work?

$this->db->select('COUNT(id)')
    ->from('products')
    ->get()
    ->row()
    ->n;

I haven't tested, but by default both result() and row() return objects, so it shouldn't be a problem to access it's properties through chaining as well.

[ivantcholakov]

The value COUNT(id) needs an alias in your example, it works:

$count = get_instance()->db
    ->select('COUNT(id) AS n')
    ->from('products')
    ->get()
    ->row()
    ->n;

Let us see possible examples without aliasing on MySQL:

$count = get_instance()->db
    ->select('COUNT(id)')
    ->from('products')
    ->get()
    ->row('COUNT(id)')         // This column name has been picked by the MySQL server.
;   // This returns string(3) "484" as it is expected.

$count = get_instance()->db
    ->select('count(id)')
    ->from('products')
    ->get()
    ->row('count(id)')         // This column name has been picked by the MySQL server.
;    // This returns string(3) "484" as it is expected.

I am not sure whether other types of database servers would pick the column name in exactly the same way.

Here is an intentionally wrong example, someone might get trapped by a similar one:

$count = get_instance()->db
    ->select('count(id)')     // Lowercase
    ->from('products')
    ->get()
    ->row('COUNT(id)')        // Uppercase
;   // This returns object(stdClass)#40 (1) { ["count(id)"]=> string(3) "484" }, not expected, right?

The suggested way releases the developer to think about the details above while doing his job, and I think it gives some visual comfort:

$count = get_instance()->db
    ->select('COUNT(id)')     // No need for aliasing.
    ->from('products')
    ->get()
    ->value()                 // Just get it.
;

[narfbg]

What you're proposing is a method depending on a query selecting a single value from a single row - that's rarely the case and using an alias is way more easier.

[ivantcholakov]

OK. I don't insist on this.

[narfbg]

Merged.

[diwahyu]

Try this
$test = ci()->db
->select('COUNT(id) AS n')
->from('products')
->where('out_of_stock', 0)
->where("( name LIKE '%".$search."%' OR description LIKE '%".$search."%' AND id != ),'0',true )
->get_compiled_select();

I use "AND id != 0" just to complete the rule of the active query WHERE. It can be replaced freely as long as not interfere with the results of the query. Sorry, my English is bad.