Sql-statment with several Brackets

[eluser]mstdmstd[/eluser]
Hi,
I have a request with several conditions related with OR. In module :

Code:

$array = array('hostel.postal_code' => $filter_state, 'region.name' => $filter_state, 'hostel.town' => $filter_state, 'state.name' => $filter_state);
  $this->db->or_like($array);

Code:

I see sql:
    WHERE `bp_hostel`.`status` =  'A'
    AND `bp_hostel`.`postal_code`  LIKE '%Vic%'
    OR  `bp_region`.`name`  LIKE '%Vic%'
    OR  `bp_hostel`.`town`  LIKE '%Vic%'
    OR  `bp_state`.`name`  LIKE '%Vic%'
    ORDER BY `view_count` desc

I have doubts with the request, I would like to set "(...)" to be sure that my sql is correct, like :

Code:

WHERE `bp_hostel`.`status` =  'A'
    AND ( `bp_hostel`.`postal_code`  LIKE '%Vic%'
    OR  `bp_region`.`name`  LIKE '%Vic%'
    OR  `bp_hostel`.`town`  LIKE '%Vic%'
    OR  `bp_state`.`name`  LIKE '%Vic%' )
    ORDER BY `view_count` desc

I read in doc:

You can wrote your requests by hands:
$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);

But I found this variant as not convinient, as I do not like to write all conditions as 1 string. In this Methods method there are some more sql conditions...
How to make this?

[eluser]TheFuzzy0ne[/eluser]
It's not possible without writing a string for the query in brackets. How else would the active record library know where you want your brackets to be?

You could always write a helper function/method that will take an array and generate the string for you.

[eluser]mstdmstd[/eluser]
[quote author="TheFuzzy0ne" date="1367409290"]write a helper function/method that will take an array and generate the string for you.[/quote]
Please, explain or give link to example how to make it.

[eluser]TheFuzzy0ne[/eluser]
I just pulled this out of the active record class and tweaked it slightly:

./application/core/MY_Model.php

Code:

class MY_Model extends CI_Model {

    function build_where($conditions, $type = 'OR', $escape = TRUE)
    {
        $where = array();
        
        foreach ($conditions as $k => $v)
        {
            echo "$k => $v<br />";
            
            $prefix = (count($where) == 0) ? '' : $type . ' ';

            if (is_null($v) && ! $this->db->_has_operator($k))
            {
                // value appears not to have been set, assign the test to IS NULL
                $k .= ' IS NULL';
            }

            if ( ! is_null($v))
            {
                if ($escape === TRUE)
                {
                    $k = $this->db->_protect_identifiers($k, FALSE, $escape);

                    $v = ' '.$this->db->escape($v);
                }
                
                if ( ! $this->db->_has_operator($k))
                {
                    $k .= ' = ';
                }
            }
            else
            {
                $k = $this->db->_protect_identifiers($k, FALSE, $escape);
            }

            $where[] = $prefix.$k.$v;
        }

        return implode(' ', $where);
    }
}

So from your model method, you'd do something like this:

Code:

$this->db->where('bp_hostel.status',  'A');

$sub_where = array(
    'bp_hostel.postal_code LIKE' => '%Vic%',
    'bp_region.name LIKE' => '%Vic%',
    'bp_hostel.town LIKE' => '%Vic%',
    'bp_state.name LIKE' => '%Vic%',
);
$this->db->where('(' . $this->build_where($sub_where) . ')', NULL, FALSE);
$this->db->order_by('view_count', 'desc');

However, I think it would be just as easy to do this instead:

Code:

$this->db->where('bp_hostel.status',  'A');

$sub_where  = '`bp_hostel`.`postal_code` LIKE "%Vic%" OR ';
$sub_where .= '`bp_region`.`name` LIKE "%Vic%" OR ';
$sub_where .= '`bp_hostel`.`town` LIKE "%Vic%" OR ';
$sub_where .= '`bp_state`.`name` LIKE "%Vic%"';
$this->db->where($sub_where, NULL, FALSE);

Your mileage may vary with the first method, but the second method is a lot more robust. The Active Record class has been designed to help you with simple queries. If you have a complex query, you'd probably be better off just writing the SQL by hand.

Hope this helps.

[eluser]mstdmstd[/eluser]
Thanks!