php - IN 子句的 Laravel 原始查询参数

Question

我使用 Laravel DB::select() 进行原始查询，在将参数传递给 IN 子句时遇到问题。

第一个查询:

$team = DB::table('TeamUserLinks')
             ->orderBy('User_Name', 'asc')
             ->lists('User_Name');

第二个查询:

$user_tasks = DB::select("SELECT usr_first_name,usr_last_name,username,
    (SELECT COUNT(*) FROM task_assignee_user t WHERE t.user_id = u.id AND t.task_status = 1) as status_open,
    (SELECT COUNT(*) FROM task_assignee_user t WHERE t.user_id = u.id AND t.task_status = 1 AND t.task_due_date < CURRENT_DATE) as overdue, 
    FROM user u where usr_initials in(" . $team . ")");

也试过:

"FROM user u where usr_initials in(?)",array($team));

和

$team = implode($team) //Not working while passing after implode

和

usr_initials in('" . $team . "')");//Not working with quotes 

第二个查询总是返回空结果。

Answer 1

使用带字符串的 in () 的正确查询是:

WHERE field IN ('String1', 'String2', 'String3')

因此您需要同时添加引号 和逗号。

简单来说就是:

 "... FROM user u where usr_initials in('" . implode("', ' ", $team . "')");

但是，当您遇到 ' 作为名称的一部分时 - 您的查询将被破坏，因此最好使用准备好的语句。这样的事情应该有效:

// here we create array of `?` of a size of team.
$marks = array_fill(0, sizeof($team), '?');
// imploding it later will give us a string `?, ?, ?`
// later every `?` will be replaced with a value of `$team`

$user_tasks = DB::select(
    "SELECT usr_first_name,usr_last_name,username,
        (SELECT COUNT(*) FROM task_assignee_user t WHERE t.user_id = u.id AND t.task_status = 1) as status_open,
        (SELECT COUNT(*) FROM task_assignee_user t WHERE t.user_id = u.id AND t.task_status = 1 AND t.task_due_date < CURRENT_DATE) as overdue, 
        FROM user u where usr_initials in(" . implode(',', $marks) . ")", 
    $team
);