How to use CASE WHEN IN in Laravel database query builder

I have users table and a followers table. I’m doing a search to get all the users with a specific keyword, but I want the ones which are followers of the current user to appear first. I can get all the followers id’s in a subquery, so my idea was to do something like this:

SELECT id, name, surname, 
CASE WHEN id IN (SELECT followable_id FROM followers WHERE followable_id = $currentUserId --this is a variable)  THEN 1
ELSE 0
END AS friend
FROM users
ORDER BY friend
WHERE name = 'keyword';

​x
 
SELECT id, name, surname, CASE WHEN id IN (SELECT followable_id FROM followers WHERE followable_id = $currentUserId --this is a variable)  THEN 1ELSE 0END AS friendFROM usersORDER BY friendWHERE name = 'keyword';​

I’ve been trying to get this in my Laravel controller, but I can’t find the way. How could I use this SQL select in my Laravel controller? How could translate this SQL code to the Laravel Database Query Builder?

What I have already tried:

Following @Bassel Hossam answer, I wrote the following code:

$placeholders = str_repeat ('?,',  count($friendsId) - 1) . '?';
    $orderedMachingUsers = User::whereIn('id', $matchingUsersId)->select(
    [
        '*', 
        DB::raw('CASE WHEN id IN ('. $placeholders .') THEN 1 ELSE 0 END AS friend', $friendsId)]
)
->orderByRaw('friend')
->cursorPaginate(9);

 
$placeholders = str_repeat ('?,',  count($friendsId) - 1) . '?';    $orderedMachingUsers = User::whereIn('id', $matchingUsersId)->select(    [        '*',         DB::raw('CASE WHEN id IN ('. $placeholders .') THEN 1 ELSE 0 END AS friend', $friendsId)])->orderByRaw('friend')->cursorPaginate(9);​

It seems very very close, but I’m still geting a “SQLSTATE[HY093]: Invalid parameter number” error. The generated SQL is the following:

SQLSTATE[HY093]: Invalid parameter number
select
  *,
  CASE
    WHEN id IN (3, 4, 7, 8, 9, 10, 111, 112, 113, 114) THEN 1
    ELSE 0
  END AS friend
from
  `users`
where
  `id` in (
    115,
    116,
    117,
    118,
    119,
    120,
    121,
    122,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?
  )
order by
  friend
limit
  10

 
SQLSTATE[HY093]: Invalid parameter numberselect  *,  CASE    WHEN id IN (3, 4, 7, 8, 9, 10, 111, 112, 113, 114) THEN 1    ELSE 0  END AS friendfrom  `users`where  `id` in (    115,    116,    117,    118,    119,    120,    121,    122,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?  )order by  friendlimit  10​

It seems that somehow it’s always adding the same number of question marks than the number of question marks generated in the $placeholders variable. What I can’t understand is why its generating it in an array that I think that should only be related to $matchingUsersId.

dd($matchingUsersId, $friendsId) is returning the following:

 array:7 [▼
  0 => array:1 [▼
    "id" => 3
  ]
  1 => array:1 [▼
    "id" => 4
  ]
  2 => array:1 [▼
    "id" => 7
  ]
  3 => array:1 [▼
    "id" => 8
  ]
  4 => array:1 [▼
    "id" => 9
  ]
  5 => array:1 [▼
    "id" => 10
  ]
  6 => array:1 [▼
    "id" => 111
  ]
]

array:5 [▼
  0 => array:1 [▼
    "id" => 3
  ]
  1 => array:1 [▼
    "id" => 113
  ]
  2 => array:1 [▼
    "id" => 112
  ]
  3 => array:1 [▼
    "id" => 114
  ]
  4 => array:1 [▼
    "id" => 115
  ]
]

 
 array:7 [▼  0 => array:1 [▼    "id" => 3  ]  1 => array:1 [▼    "id" => 4  ]  2 => array:1 [▼    "id" => 7  ]  3 => array:1 [▼    "id" => 8  ]  4 => array:1 [▼    "id" => 9  ]  5 => array:1 [▼    "id" => 10  ]  6 => array:1 [▼    "id" => 111  ]]​array:5 [▼  0 => array:1 [▼    "id" => 3  ]  1 => array:1 [▼    "id" => 113  ]  2 => array:1 [▼    "id" => 112  ]  3 => array:1 [▼    "id" => 114  ]  4 => array:1 [▼    "id" => 115  ]]​

Finall working code based on @Bassel Hossam answer:

$placeholder = str_repeat ('?,',  count($friendsId) - 1) . '?';
        $orderedMachingUsers = User::
        whereIn('id', $matchingUsersId)->selectRaw(
            '*, CASE WHEN id IN ('. $placeholder .') THEN 1 ELSE 0 END AS friend', $friendsId)
    ->orderByRaw('friend DESC')
    ->simplePaginate(9);

 
$placeholder = str_repeat ('?,',  count($friendsId) - 1) . '?';        $orderedMachingUsers = User::        whereIn('id', $matchingUsersId)->selectRaw(            '*, CASE WHEN id IN ('. $placeholder .') THEN 1 ELSE 0 END AS friend', $friendsId)    ->orderByRaw('friend DESC')    ->simplePaginate(9);​

Answer

the syntax for in condition should be like CASE WHEN id IN (?,?,?) THEN so you need to add ? with the count of $friendsId

so you should use the following

$placeholders = str_repeat ('?,',  count($friendsId) - 1) . '?';
$orderedMachingUsers = DB::table('users')
    ->whereIn('id', $matchingUsersId)->selectRaw(
            '*, CASE WHEN id IN ('. $placeholder .') THEN 1 ELSE 0 END AS friend', $friendsId)
    ->orderByRaw('friend')
    ->get();

 
$placeholders = str_repeat ('?,',  count($friendsId) - 1) . '?';$orderedMachingUsers = DB::table('users')    ->whereIn('id', $matchingUsersId)->selectRaw(            '*, CASE WHEN id IN ('. $placeholder .') THEN 1 ELSE 0 END AS friend', $friendsId)    ->orderByRaw('friend')    ->get();​

Advertisement

Answer