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';
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 @aynber answer, I wrote the following code:
$orderedMachingUsers = DB::table('users')
->whereIn('id', $matchingUsersId)->select(
[
'*',
DB::raw('CASE WHEN id IN ? THEN 1 ELSE 0 END AS friend', [$friendsId])]
)
->orderByRaw('friend')
->get();
It seems close, but I'm still geting a "SQLSTATE[42000]: Syntax error or access violation: 1064" error.
CodePudding user response:
You need to pass the case statement in with a raw clause
DB::table('users')
->where('name', 'keyword')->select(
[
'id',
'name',
'surname',
DB::raw('CASE WHEN id IN (SELECT followable_id FROM followers WHERE followable_id = ?) THEN 1 ELSE 0 END AS friend', [$curentUserId])]
)
->orderByRaw('friend')
->get();
CodePudding user response:
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)->select(
[
'*',
DB::raw('CASE WHEN id IN ('. $placeholders .') THEN 1 ELSE 0 END AS friend', [$friendsId])]
)
->orderByRaw('friend')
->get();