Add parenthesis to a Kohana ORM's Where clauses

ORM in Kohana is incredibly handy at database query gathering. It's also is great at extending your results, so it is a huge upgrade over regular SQL queries. However, for more advanced queries, ORM isn't very transparent on how to get what you are looking for.

Let's say you want to get a user with a variety of usernames and has logged in either less than 10 times or more than 20 times. In regular SQL I'd do something like:


SELECT * FROM `users` WHERE (`username` = 'Username1' OR `username` = 'Username2') AND (`logged_in_times` 20);


So how would we do this in ORM? If you are looking through Kohana documentation you might come up with:


$user = ORM::factory('users')
->where('username','=','Username1')
->or_where('username','=','Username2')
->where('logged_in_times','<','10')
->or_where('logged_in_times','>','20')
->find_all();


This would give you the query we would be looking for, EXCEPT it does not include the parenthesis that are needed for order of operations. Kohana documentation keeps the cure for this somewhat hidden but I was able to find it. We need the where_open() and where_close() functions to add the needed parenthesis.


$user = ORM::factory('users')
->where_open()
->where('username','=','Username1')
->or_where('username','=','Username2')
->where_close()
->where_open()
->where('logged_in_times','or_where('logged_in_times','>','20')
->where_close()
->find_all();


Of course we could have used an IN() function with an array of valid usernames, but the logged_in_times amount needs the parenthesis in order to correctly get the results we need.