Criteria grouping in active record queries

[eluser]louis w[/eluser]
I was running into some problems with queries coming from the active record db library which have multiple criteria set. Example:

Code:

$this->db->where('status', 'published');
$this->db->like('title', $search);
$this->db->->or_like('text', $search);
$this->db->->or_like('author', $search);

// Query:
// SELECT * FROM (`story`) WHERE `status` = 'published' AND  `title`  LIKE '�ach%' OR  `text`  LIKE '�ach%' OR  `author`  LIKE '�ach%'

Because of the way this query is constructed it would return entries which did not match the status=published statement. This is because it would match against one of the LIKE statements and validate true. To fix this I had to do this:

Code:

$this->db->where('status', 'published');
$this->db->where('(`title` LIKE \'%'.$search.'%\' OR `text` LIKE \'%'.$search.'%\' OR `author` LIKE \'%'.$search.'%\')', NULL, FALSE);

// Query:
// SELECT * FROM (`story`) WHERE (`title` LIKE '�ach%' OR `text` LIKE '�ach%' OR `author` LIKE '�ach%') AND `status` = 'published'

I'm wondering if there is a better way to group criteria then a writing the entire thing out like I have and telling AR not to escape it?

PS: Not sure why it's displaying invalid characters in the code blocks, they are supposed to be % be (without the space)

[eluser]hlz[/eluser]
I'm running into the same issue. Did you find a solution?

[eluser]louis w[/eluser]
Unfortunately, never found an elegant solution and had to write it out longhand like in the first post.

[eluser]hlz[/eluser]
Mmmz... currently i'm looking at this lib

http://www.assembla.com/wiki/show/IgnitedRecord

but it seems way overkill for what I'm trying to accomplish. Writing it out is probably the best and fastest solution.

[eluser]louis w[/eluser]
Ignited Record is an ORM. This is very cool and can help to streamline your interaction with the database but unless you are looking for all of what it brings to the table, it could be overkill.

[eluser]hlz[/eluser]
Well, it is an interesting solution, but at this moment it would require a serious amount of code rewriting to implement it properly in my application. Just attaching it to solve this 'minor' problem i'm facing is overkill.

[eluser]daparky[/eluser]
I also had the same issue and had to write queries like in the first post.

[eluser]Nick_MyShuitings[/eluser]
Same topic as this thread: http://ellislab.com/forums/viewthread/169520/

There was a modified version of AR, if I can't find it I'll prolly just write it up again... added two methods, something like "open_group" and "close_group", doesn't do much of anything except add an opening and closing parenthesis which lets you write those types of complex queries using AR syntax.

PM if interested, since I'll prolly forget to repost here.

[eluser]Nick_MyShuitings[/eluser]
Third request today and I was bored at lunch so:

http://ellislab.com/forums/viewthread/177531/