Where AND oR WHERE ??

[eluser]fuji2009[/eluser]
Hello guys i have a problem: how do that :

Code:

SELECT `user1_id` FROM (`relationship`) WHERE (`user2_id` = '2' AND `type` = 'friend' AND `valide` = '1') OR (`usr2_id` = '2' AND `type` = 'coeur' AND `valide` = '1')

I try this but this return me :

Code:

$where = array('user2_id'=>$user_login_id , 'type'=>'friend' , 'valide'=>1);
        $this->db->where($where);
        $where2 = array('user2_id'=>$user_login_id , 'type'=>'coeur' , 'valide'=>1);
        $this->db->or_where($where2);

this return :

Code:

SELECT `user1_id` FROM (`relationship`) WHERE `user2_id` = '2' AND `type` = 'friend' AND `valide` = 1 OR `user2_id` = '2' OR `type` = 'coeur' OR `valide` = 1

it s not same

Thanks you

[eluser]Cristian Gilè[/eluser]

Code:

$query = $this->db
               ->select('user1_id')
               ->where('user2_id', '2')
               ->where('type', 'friend')
               ->or_where('type', 'coeur')
               ->where('valide', '1')
               ->get('relationship');

For complex query (nested where clause) AR is not always the best solution.


Cridstian Gilè

[eluser]fuji2009[/eluser]
Hum not understand can you write me the answer correct for do my exact query plz thank you

[eluser]Cristian Gilè[/eluser]
Try the query above and let me know if it works.


Cristian Gilè

[eluser]fuji2009[/eluser]
sry not work , this query do :

Code:

$this->db->where('usr2_id',$user_login_id);
        $this->db->where('valide',1);
        $this->db->where('type','coeur');
        $this->db->or_where('type','friend');

Code:

SELECT `user1_id`
FROM (
`relationship`
)
WHERE `user2_id` = '2'
AND `valide` =1
AND `type` = 'coeur'
OR `type` = 'friend'
LIMIT 0 , 30

whereas i want :

Code:

SELECT `user1_id`
FROM (
`relationship`
)
WHERE `user2_id` = '2'
AND `valide` =1
AND
( `type` = 'coeur' OR `type` = 'friend' )
LIMIT 0 , 30

Thank you for your help

[eluser]Cristian Gilè[/eluser]
In this case, without parentheses:

Code:

SELECT `user1_id`
FROM (
`relationship`
)
WHERE `user2_id` = '2'
AND `valide` =1
AND `type` = 'coeur'
OR `type` = 'friend'
LIMIT 0 , 30

or with parentheses:

Code:

SELECT `user1_id`
FROM (
`relationship`
)
WHERE `user2_id` = '2'
AND `valide` =1
AND
( `type` = 'coeur' OR `type` = 'friend' )
LIMIT 0 , 30

the result is the same. The query I proposed is valid. What's wrong?


Cristian Gilè

[eluser]fuji2009[/eluser]
The request select :

SELECT `user1_id`
FROM (
`relationship`
)
WHERE `user2_id` = '2'
AND `valide` =1
AND `type` = 'coeur'

it s good but the 2


select user1_id where type='friend'

not valide and user2_id = 2

Thank you

[eluser]cahva[/eluser]
Is this what you're looking for?:

Code:

$query = $this->db
    ->select('user1_id')
    ->where('user2_id', $user_login_id)
    ->where('valide', 1)
    ->where("(type='coeur' OR type='friend')",NULL,FALSE)
    ->get('relationship');

That will run the sql:

Code:

SELECT `user1_id` FROM (`relationship`) WHERE `user2_id` = 2 AND `valide` = 1 AND (type='coeur' OR type='friend')

[eluser]zool2005[/eluser]
[quote author="cahva" date="1296142936"]Is this what you're looking for?:

Code:

->where("(type='coeur' OR type='friend')",NULL,FALSE)

[/quote]

I was having a similar problem with my application. The first where statement was being ignored when calling OR_WHERE in the same query.
It was the parentheses to enclose the OR statements which fixed it for me.