How do this in Laravel using the Query Builder? (Subquery WHERE NOT IN)-CodePudding

SELECT DISTINCT
tasks.id,
tasks.category_id,
(CASE WHEN (SELECT task_id FROM completed_user_tasks WHERE user_id = 1 AND task_id = tasks.id) THEN true ELSE false END) AS completed
  FROM tasks
  WHERE tasks.id NOT IN
  (
    SELECT task_id FROM skipped_user_tasks WHERE user_id = 1
    UNION
    SELECT task_id FROM shown_user_tasks WHERE user_id = 1
    UNION
    SELECT task_id FROM current_user_tasks WHERE user_id = 1
  );

This query only returns tasks that are not in the tables: skipped_user_tasks, shown_user_tasks, and current_user_tasks. In addition, the "completed" field is added during the selection: if the task is in the completed_user_tasks table, it will return true, otherwise it will be false.

CodePudding user response：

Maybe you can try this and see if it works,

$check_events = DB::table('tasks')
              ->selectRaw('SELECT DISTINCT tasks.id, tasks.category_id, (CASE WHEN (SELECT task_id FROM completed_user_tasks WHERE user_id = 1 AND task_id = tasks.id) THEN true ELSE false END) AS completed')
              ->whereRaw('tasks.id NOT IN (SELECT task_id FROM skipped_user_tasks WHERE user_id = 1 UNION SELECT task_id FROM shown_user_tasks WHERE user_id = 1 UNIONSELECT task_id FROM current_user_tasks WHERE user_id = 1)')
              ->get();