如何在Laravel查询构造器中使用CASE WHEN IN实现粉丝优先排序

问题场景 #

• 数据库包含users表和followers表
• 需求：搜索含指定关键词的用户，同时让当前用户的粉丝排在搜索结果前列
• 已通过子查询获取粉丝ID列表，能写出对应SQL，但转换为Laravel查询构造器时出现参数数量错误

原始SQL语句 #

SELECT id, name, surname, 
CASE WHEN id IN (SELECT followable_id FROM followers WHERE followable_id = $currentUserId -- 此处为变量)  THEN 1
ELSE 0
END AS friend
FROM users
ORDER BY friend
WHERE name = 'keyword';

尝试的错误代码 #

$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);

报错信息 #

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

变量输出 #

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
  ]
]

最终可行代码 #

$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);

内容的提问来源于stack exchange，提问作者Nil Suria