[SOLVED] Codeigniter Database Where with multiple clauses and another where

Codeigniter Database Where with multiple clauses and another where

So I admit the title is a bit, confusing, I apologize cause I have no idea what to make it, so open to suggestions there. Anyway on to the actual question. I have a query, works fine for the most part, however I am still yielding some wrong results. Understandably so as well. As that is how this is forming the query. To many ands and to many ors loosely strung together. What I need to do is wrap a set of the where clauses in ( ) then have an and then the remaining set of clauses in another ( ) I have no idea if that makes sense to anyone.

Basically if I were hand typing the query outright it'd look something like..

select * from my_table
where (cond1 = this and cond2 = that) 
and (cond2 != something or cond3 > other)

Not really sure how to accomplish that using codeigniter's active record. What I currently have is.

$this->db->select('user_event_invite.*, game_bridge.*, user_event.*, user_event.location as bizfo')
->from('user_event')
->join('game_bridge', 'user_event.gID = game_bridge.gID')
->join('user_event_invite', 'user_event_invite.eID = user_event.eID')
->where('user_event_invite.friendID', $mID)
->where('user_event.isactive', 0)
->where('user_event_invite.isactive', 0)
->where('user_event.ends >=', $this->genfunc->unixToMySQL(time()))
->where('user_event.ends !=', '0000-00-00 00:00:00')
->or_where('yes > ', 0)
->or_where('no > ', 0)
->or_where('maybe > ', 0);

but what I am ultimately wanting is all these specific clauses in one group..

->where('user_event_invite.friendID', $mID)
->where('user_event.isactive', 0)
->where('user_event_invite.isactive', 0)
->where('user_event.ends >=', $this->genfunc->unixToMySQL(time()))
->where('user_event.ends !=', '0000-00-00 00:00:00')

then combine that group with and AND to this other group.

->or_where('yes > ', 0)
->or_where('no > ', 0)
->or_where('maybe > ', 0);

Solution

So, CodeIgniter doesn't support "wrapping" your OR and AND SQL statements, not using its abstraction layer at least. Mixing OR and AND statements is not recommended by CodeIgniter.

You can try understanding the query that your executing with $this->db->last_query()

You can also try this sub-query library to see if it can help you out: http://labs.nticompassinc.com/CodeIgniter-Subqueries/

Or you may just have to use some RAW SQL in your case.