Very Irritating Problem

[eluser]Developer13[/eluser]
Somebody please help me out here... my brain is just fried on this issue!

Let's say I want to generate the following query via CI's active record:

SELECT * FROM table WHERE last_name = 'Terry' AND (first_name = 'Alex' OR first_name = 'Jesse')

How in the world do I indicate that I want the AND and the OR separated by parenthesis?

Of course the actual query is much larger and more involved than the example and I'd like to stick with CI's active record if possible.

Any ideas would be greatly appreciated!

[eluser]Armchair Samurai[/eluser]
AFAIK, CI's AR doesn't have this functionality yet, but you could get away with using a WHERE IN for your query

Code:

$this->db->where('last_name', 'Terry');
$this->db->where_in('first_name', array('Alex', 'Jesse'));
$query = $this->db->get('table');

[eluser]Developer13[/eluser]
Except the problem is that I don't want to compare to a list as WHERE IN does. Maybe my example wasn't the right one to use for my problem, and actually, the core of the problem lies within the fact that CI 1.6.1 adds backticks to reserved keywords within a WHERE statement.

What I actually need to do is this:

$this->db->where("MATCH (field1,field2,field3) AGAINST ('value')");

But CI adds backticks around the reserved MATCH keyword which crashes the query.

[eluser]esra[/eluser]
Have not used CI's Active Record in a while, but could you assign a where for the OR clause to a variable on the line before the AND clause, and then add the variable to the AND clause. I'm assuming that where processes the where clauses in order of precedence.

[eluser]Armchair Samurai[/eluser]
[quote author="Developer13" date="1207009304"]What I actually need to do is this:

$this->db->where("MATCH (field1,field2,field3) AGAINST ('value')");

But CI adds backticks around the reserved MATCH keyword which crashes the query.[/quote]
Get rid of the spaces between the keywords and the brackets (i.e. MATCH(field1, field2, field3) AGAINST('value')) and you're good to go.

[eluser]Derek Allard[/eluser]
throw a "FALSE" in there.

Code:

$this->db->where("MATCH (field1, field2, field3) AGAINST (’value’)", NULL, FALSE);

This will prevent AR from "protecting" your fields with backticks.

[eluser]Armchair Samurai[/eluser]
That's not mentioned in the User Guide at all for $this->db->where() (although it is noted for $this->db->select()). Another undocumented feature has bounded out of the mists?

[eluser]Derek Allard[/eluser]
Did it? Jeez, sorry, I could have swore I had that documented, but clearly I did not.

Tell you what, I'll document it right away - if you tell me where you think it'd make the most sense.

[eluser]Armchair Samurai[/eluser]
If it was me, I'd just tack it on the end of the custom string section:

Custom string:

You can write your own clauses manually:

Code:

$where = "name='Joe' AND status='boss' OR status='active'";

$this->db->where($where);

If you write your own custom string, $this->db->where() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field names with backticks.

Code:

$this->db->where('MATCH (first_name, last_name) AGAINST (`wiggum`)', FALSE);

[eluser]Derek Allard[/eluser]
Just a note that I've (a) fixed my example above; (b) documented this under db->where() in the docs. Thanks all!