使用CodeIgniter get_where来链接'和'以及'或'语句

Question

I am trying to access data in a local database (on my vm), and I have to use CodeIgniter's query building classes to get the data. I have this query which I have figured out in sql:

select message, created
from logs
where username = 'user'
    and (
        created > '1487695796'
        and created < '1487782196'
        )
    and (
        message = 'login failure'
        or message = 'login success'
        or message = 'log out'
        )
order by created asc

My biggest question is how can I chain the 'and's and 'or's in a get_where statement in CodeIgniter? I have looked and saw I could put things in an array for the 'WHERE' portion, but I haven't seen how I can place things in for the 'or's (since everything in the array is an 'and'. I MUST use get_where (can't do 'get->where'), so if there is a way to do it, please let me know!

Thank you for taking the time to read my question!

Answer 1

Since Codeigniter 3.0 the query builder class supports Query grouping

from the docs:

$this->db->select('*')->from('my_table')
        ->group_start()
                ->where('a', 'a')
                ->or_group_start()
                        ->where('b', 'b')
                        ->where('c', 'c')
                ->group_end()
        ->group_end()
        ->where('d', 'd')
->get();

// Generates:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

Edit: in your example you would use:

$this->db->select('message, created')->from('logs')
        ->where('username', 'user')
        ->group_start()
                ->where('created >', '1487695796')
                ->where('created <', '1487782196')
        ->group_end()
        ->group_start()
                ->where('message', 'login failure')
                ->or_where('message', 'login success')
                ->or_where('message', 'log out')
        ->group_end()               
        ->order_by('created', 'ASC')  
->get(); 

or

$this->db->select('message, created')
        ->group_start()
                ->where('created >', '1487695796')
                ->where('created <', '1487782196')
        ->group_end()
        ->group_start()
                ->where('message', 'login failure')
                ->or_where('message', 'login success')
                ->or_where('message', 'log out')
        ->group_end()               
        ->order_by('created', 'ASC')  
->get_where('logs', array('username' => 'user') );

// both generate:
//SELECT `message`, `created` 
//FROM `logs` 
//WHERE `username` = 'user' 
//AND ( `created` > '1487695796' AND `created` < '1487782196' ) 
//AND ( `message` = 'login failure' OR `message` = 'login success' OR `message` = 'log out' ) 
//ORDER BY `created` ASC

to check if the Codeigniter generated query matches your SQL, you can use:

echo $this->db->last_query(); // echos last query string