如何查询当前会话用户名下截止日期为今日的任务？

需求 #

获取当前活跃会话用户名下，截止日期为今日的任务列表。

数据表结构 #

task_list 表 #

CREATE TABLE "task_list" (
    "task_id"   INTEGER NOT NULL,
    "task_code" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "description"   TEXT NOT NULL,
    "department_id" INTEGER NOT NULL,
    "employee_id"   INTEGER,
    "status"    INTEGER NOT NULL DEFAULT 1,
    "date_created"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date_updated"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date"  TEXT,
    PRIMARY KEY("task_id" AUTOINCREMENT),
    FOREIGN KEY("employee_id") REFERENCES "employee_list"("employee_id") on DELETE SET NULL,
    FOREIGN KEY("department_id") REFERENCES "department_list"("department_id") on DELETE CASCADE
);

task_assignees 表 #

CREATE TABLE "task_assignees" (
    "task_id"   INTEGER NOT NULL,
    "employee_id"   INTEGER NOT NULL,
    FOREIGN KEY("task_id") REFERENCES "task_list"("task_id") on DELETE CASCADE,
    FOREIGN KEY("employee_id") REFERENCES "employee_list"("employee_id") on DELETE CASCADE
);

employee_list 表 #

CREATE TABLE "employee_list" (
    "employee_id"   INTEGER NOT NULL,
    "employee_code" INTEGER NOT NULL,
    "fullname"  TEXT NOT NULL, -- 原语句笔误，将INTEGER修正为TEXT
    "email" TEXT NOT NULL,
    "password"  TEXT NOT NULL,
    "gender"    TEXT NOT NULL,
    "dob"   DATE NOT NULL,
    "contact"   TEXT NOT NULL,
    "address"   TEXT NOT NULL,
    "department_id" INTEGER NOT NULL,
    "type"  TEXT NOT NULL,
    "status"    INTEGER NOT NULL DEFAULT 1,
    "date_created"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date_updated"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY("department_id") REFERENCES "department_list"("department_id") on DELETE CASCADE,
    PRIMARY KEY("employee_id" AUTOINCREMENT)
);

现有单独查询语句 #

• 查询指定用户名下任务：

SELECT * FROM `task_list` where task_id in (SELECT task_id FROM `task_assignees` where employee_id = '{$_SESSION['employee_id']}') order by strftime('%s',date_created) desc;

• 查询今日截止任务：

SELECT * FROM `task_list` where DATE(`date`) = DATE('now');

合并查询的优化方案 #

你尝试的合并逻辑方向正确，但存在语法细节和安全风险问题，以下是调整后的代码：

原尝试代码 #

<?php 
    $sql = "SELECT * 
            FROM task_list 
            where task_id 
                in (SELECT task_id 
                    FROM task_assignees 
                    where employee_id = '{$_SESSION['employee_id']}'
                    ) 
            AND (DATE(date)=DATE(now))"; 

    $qry = $conn->query($sql); 
    $i = 1; 
    while($row = $qry->fetchArray()): 
?>

优化后的代码（含安全与性能优化） #

<?php
    // 提取会话中的用户ID，避免直接拼接
    $employeeId = $_SESSION['employee_id'];
    
    // 使用JOIN替代IN子查询，提升大数据量下的性能
    // 同时用预处理语句防止SQL注入
    $sql = "SELECT DISTINCT tl.* 
            FROM task_list tl
            JOIN task_assignees ta ON tl.task_id = ta.task_id
            WHERE ta.employee_id = ?
              AND DATE(tl.date) = DATE('now')
            ORDER BY strftime('%s', tl.date_created) DESC";

    $stmt = $conn->prepare($sql);
    $stmt->bindValue(1, $employeeId, SQLITE3_INTEGER);
    $qry = $stmt->execute();
    
    $i = 1;
    while($row = $qry->fetchArray()):
?>

关键调整说明 #

1. 修正日期语法：SQLite中DATE(now)需改为DATE('now')，否则会触发语法错误。
2. 防范SQL注入：使用预处理语句绑定参数，避免直接拼接会话变量到SQL语句中。
3. 性能优化：用JOIN替代IN子查询，在数据量较大时查询效率更高；添加DISTINCT避免重复数据（若存在同一任务多次分配给同一用户的情况）。
4. 保留排序逻辑：继承原查询按创建时间倒序的排序规则。
5. 表结构修正：修正employee_list表中fullname字段的类型错误，确保能正常存储用户姓名。
6. 日期格式要求：确保task_list表的date字段存储的是SQLite可识别的格式（如YYYY-MM-DD），否则DATE()函数无法正确解析截止日期。

内容的提问来源于stack exchange，提问作者Chirag Dixit