Active Records and complex queries

[eluser]Shella[/eluser]
Hi,
I'm using active records but I can't manage the bracket (parenthesis) when the query is composed.

My example is this.

Active record:

Code:

$this->db->select('*');
$this->db->where('friends.active', 1);
$this->db->where('friends.user_id', $user);
$this->db->or_where('friends.friend_id', $user);
$this->db->where('friends.active', 1);

and this is going to compose this:

Code:

SELECT * FROM (`friends`) WHERE `friends`.`active` = 1 AND `friends`.`user_id` = '8' OR `friends`.`friend_id` = '8' AND `friends`.`active` = 1

but the result I seek should be something like:

Code:

SELECT * FROM ( `friends` ) WHERE ( `friends`.`active` = 1 AND `friends`.`user_id` = '8' ) OR ( `friends`.`friend_id` = '8' AND `friends`.`active` = 1 )

..as you see the difference stand on the brackets.

The only workaround I found is to use directly $query = $this->db->query() with NOT implementing the Active Records.

Is this the only way? And Is this right in your opinion?

[eluser]DarkManX[/eluser]
AND has a higher priority then OR, so your posted queries would return the same oO

[eluser]Shella[/eluser]
I see, so this does mean that I can't use active records for thi kind of query?

[eluser]CroNiX[/eluser]
Not in this version. Supposedly they have new query grouping methods in the upcoming CI 3 to group where statements like you are wanting.

Although, you can just use a string for where and include everything how you want it without passing it a variable...

[eluser]Shella[/eluser]
I see, thanks for your answers. I appreciated.

[eluser]DarkManX[/eluser]
But still - your both mysql-statements would return the same set of data.

Code:

A && B || C && D = (A && B) || (C && D)

What do you want to achieve?

[eluser]Aken[/eluser]
If you want to do two where groups, you can do something like this:

Code:

$this->db->where('(column = value AND column = value)');
$this->db->or_where('(column = value AND column = value)');

You might need to specify the third parameter of where() and or_where() as FALSE to prevent backtick escaping, if you run into issues with that.

That's the best you're going to get with the current version. Although there's nothing wrong with writing the query out yourself and using $this->db->query() if you want (so long as you don't need to support multiple DB types).

[eluser]valuk[/eluser]
Or you can use this

$this->db->where('(1', ' 1', FALSE);
$this->db->where('1', ' 1)', FALSE);

This is little trick that I read it somewhere.

e.g.

$this->db->select('*');
$this->db->where('(1', ' 1', FALSE);
$this->db->where('friends.active', 1);
$this->db->where('friends.user_id', $user);
$this->db->where('1', ' 1)', FALSE);
$this->db->or_where('(1', ' 1', FALSE);
$this->db->where('friends.friend_id', $user);
$this->db->where('friends.active', 1);
$this->db->where('1', ' 1)', FALSE);

[eluser]Shella[/eluser]
[quote author="Aken" date="1345708534"]If you want to do two where groups, you can do something like this:

Code:

$this->db->where('(column = value AND column = value)');
$this->db->or_where('(column = value AND column = value)');

You might need to specify the third parameter of where() and or_where() as FALSE to prevent backtick escaping, if you run into issues with that.

That's the best you're going to get with the current version. Although there's nothing wrong with writing the query out yourself and using $this->db->query() if you want (so long as you don't need to support multiple DB types).[/quote]

Wow, I did not know that it was possible to use this sintax into the where active record. I mean I was sure that only one kind of var was allowed.

Thanks, this is very usefull tips

[eluser]Aken[/eluser]
It's documented in the where() section of the AR user guide page. Maybe take some time and just read it all - probably find a handful of things you didn't know that will help.