Kohana 3 ORM - grouping where conditions with parentheses

Question

I'm trying to run a query through the ORM like this:

   SELECT * from table where (fname like 'string%' or lname like 'string%') 
AND (fname like 'string2%' or lname like 'string2%');

Here's what i have so far:

$results = ORM::factory('profiles');
foreach ($strings as $string) {
    $result->where('fname', 'like', "$string%");
    $result->or_where('lname', 'like', "$string%");
}

But this doesn't account for the parentheses. Any ideas?

Tonsillotomy

If you stumble upon this problem in Kohana 2, see my answer here: https://mcmap.net/q/1769573/-how-can-i-mix-or-and-and-in-orm-queries – Guild

Answer 1

Found the answer.

It's done with Kohana's where_open() and where_close() methods.

Tonsillotomy

Sorry DexterW, I inadvertently downvoted your answer and now it won't let me undo the downvote... – Vernonvernor

Answer 2

It works fine for me .

ORM code sample

$musicslist = ORM::factory('user_music')
            ->where_open()
            ->where('title', 'like', '%' . $search . '%')
            ->or_where('album', 'like', '%' . $search . '%')
            ->or_where('artist', 'like', '%' . $search . '%')
            ->where_close()
            ->and_where('app_userid','=', $userid)
            ->find_all();

it will create SQL query

SELECT `user_musics`.* FROM `user_musics` WHERE (`title` LIKE '%as%' OR `album` LIKE '%as%' OR `artist` LIKE '%as%') AND `app_userid` = '21'

Ascariasis

Answer 3

Couldn't get code formatting to work in the comment - just thought I'd add a simple example to the answer in case anyone else comes across it:

$query = DB::select()
           ->from('some_table')
           ->where_open()
           ->where('column_one', '=', 1)
           ->or_where('column_two', '=', 2)
           ->where_close();

would produce the following SQL:

SELECT * FROM some_table
WHERE (column_one = 1 OR column_two = 2);

Vernonvernor