Laravel raw query parameters for IN clause

Question

I've using Laravel DB::select() for raw query, facing issue while passing parameters to IN clause.

1st Query:

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

2nd Query:

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

Also tried:

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

and

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

and

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

2nd query always return empty result.

Answer 1

Correct query using in () with strings is:

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

So you need to add both quotes and commas.

In a simple way it is:

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

But, when you meet ' as a part of name - your query will be broken, so it is better to use prepared statements. Something like this should work:

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