Implement IF statement in SQL query in Laravel5 eloquent

Question

I want to use this query into my laravel application, but I don't know how to write the IF part!

SELECT    
`users`.* ,    
IF(`friends`.`user_id` = '2' , TRUE , FALSE) AS `user_friend`         
FROM `users`    
LEFT JOIN `friends` ON `friends`.`friend_id` = `users`.`id`  AND `friends`.`user_id` = '2'    
WHERE `users`.`id` != '2'

SteD · Accepted Answer · 2016-12-14 03:50:33Z

4

You can use DB::raw.

User::select(DB::raw('users.* , IF(friends.user_id = '2' , TRUE , FALSE) AS user_friend'))
->join('friends', 'friends.friend_id', '=', 'user.user_id')
->where('friends.user_id, 2)
->where('user.id', '!=', 2);

Updated:

You can have a look at Advanced Join Clauses

->join('friends', function ($join) {
        $join->on('friends', 'friends.friend_id', '=', 'user.user_id')
             ->where('friends.user_id', 2);


    })

edited Dec 14, 2016 at 3:50

answered Dec 14, 2016 at 3:06

SteD

14.1k13 gold badges69 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Na30m Over a year ago

Hi, Thanks for the reply! I have 2 conditions in my letfjoin.. how can I write my both conditions?

SteD Over a year ago

You can actually put it in the where statement after the joins, code updated.

Na30m Over a year ago

The condition should be in the leftjoin , it doesn't show the right result when I place it in where

AddWeb Solution Pvt Ltd · Accepted Answer · 2016-12-14 06:40:55Z

1

Try below full query:

User::select(DB::raw("users.* , IF(friends.user_id = '2' , TRUE , FALSE) AS user_friend"))  
->join('friends', function ($join) {  
  $join->on('friends', 'friends.friend_id', '=', 'user.user_id')  
  ->where('friends.user_id', 2);  
});

This is the version after correction from @SteD answer. Hope this work for you!

answered Dec 14, 2016 at 6:40

AddWeb Solution Pvt Ltd

21.8k5 gold badges30 silver badges61 bronze badges

Collectives™ on Stack Overflow

Implement IF statement in SQL query in Laravel5 eloquent

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related