Kanban（MetaMagik插件）任务日期计算查询异常排查

问题背景 #

使用搭载MetaMagik插件的Kanban看板，需要计算任务创建日期与关闭日期的差值。编写的查询集成到模型后仅返回指定日期范围内的单条任务，但相同逻辑在数据库中单独执行完全正常，怀疑是JOIN操作或子查询写法导致问题，相关代码如下：

protected function getTasks($project_id, $from, $to)
{
    if (!is_numeric($from)) {
        $from = $this->dateParser->removeTimeFromTimestamp($this->dateParser->getTimestamp($from));
    }

    if (!is_numeric($to)) {
        $to = $this->dateParser->removeTimeFromTimestamp(strtotime('+1 day', $this->dateParser->getTimestamp($to)));
    }

    return $this->db->table(TaskModel::TABLE)
        ->columns(

            TaskModel::TABLE.'.id',
            SwimlaneModel::TABLE.'.name AS swimlane_name',
            CategoryModel::TABLE.'.name AS category_name',

            '(SELECT DISTINCT '.MetadataExtendModel::TABLE.'.value FROM '.MetadataExtendModel::TABLE.' WHERE '.MetadataExtendModel::TABLE.'.task_id='.TaskModel::TABLE.'.id and '.MetadataExtendModel::TABLE.'.name="Type" ) AS prueba',
            '(SELECT DISTINCT '.MetadataExtendModel::TABLE.'.value FROM '.MetadataExtendModel::TABLE.' WHERE '.MetadataExtendModel::TABLE.'.task_id='.TaskModel::TABLE.'.id and '.MetadataExtendModel::TABLE.'.name="Identifyer" ) AS prueba2',
            '(SELECT DISTINCT '.MetadataExtendModel::TABLE.'.value FROM '.MetadataExtendModel::TABLE.' WHERE '.MetadataExtendModel::TABLE.'.task_id='.TaskModel::TABLE.'.id and '.MetadataExtendModel::TABLE.'.name="Area" ) AS prueba3',
            '(SELECT DISTINCT '.MetadataExtendModel::TABLE.'.value FROM '.MetadataExtendModel::TABLE.' WHERE '.MetadataExtendModel::TABLE.'.task_id='.TaskModel::TABLE.'.id and '.MetadataExtendModel::TABLE.'.name="Reference" ) AS prueba13',
            '(SELECT DISTINCT '.MetadataExtendModel::TABLE.'.value FROM '.MetadataExtendModel::TABLE.' WHERE '.MetadataExtendModel::TABLE.'.task_id='.TaskModel::TABLE.'.id and '.MetadataExtendModel::TABLE.'.name="Score_Before" ) AS prueba4',

            ColumnModel::TABLE.'.title AS estado',
            'uc.name AS creator_name',
            UserModel::TABLE.'.name AS assignee_name',
            TaskModel::TABLE.'.date_creation',
            TaskModel::TABLE.'.title',
            TaskModel::TABLE.'.description',
            '(SELECT DISTINCT '.MetadataExtendModel::TABLE.'.value FROM '.MetadataExtendModel::TABLE.' WHERE '.MetadataExtendModel::TABLE.'.task_id='.TaskModel::TABLE.'.id and '.MetadataExtendModel::TABLE.'.name="Root_Cause" ) AS causa',

//--------------------------------QUERY NOT WORK-------------------------------------------

            '(SELECT CASE WHEN (max(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Closed_Date" then '.MetadataExtendModel::TABLE.'.value END)) = "" THEN DATEDIFF(CURDATE(), FROM_UNIXTIME('.TaskModel::TABLE.'.date_creation, "%Y-%m-%d"))
                ELSE DATEDIFF(max(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Closed_Date" then '.MetadataExtendModel::TABLE.'.value END),  FROM_UNIXTIME('.TaskModel::TABLE.'.date_creation, "%Y-%m-%d"))
                END AS OPEN_DAYS)',


        // '(SELECT CASE WHEN (max(CASE WHEN '.MetadataExtendModel::TABLE. '.name = "Closed_Date" THEN '.MetadataExtendModel::TABLE. '.value END)) = ""
        // THEN DATEDIFF(CURDATE(), FROM_UNIXTIME('.TaskModel::TABLE . '.date_creation, "%Y-%m-%d"))
        // ELSE DATEDIFF(max(CASE WHEN '.MetadataExtendModel::TABLE . '.name = "Closed_Date" THEN '.MetadataExtendModel::TABLE . '.value END),  FROM_UNIXTIME('.TaskModel::TABLE . '.date_creation, "%Y-%m-%d"))
        // END AS OPEN_DAYS FROM '.TaskModel::TABLE . ' left join '.MetadataExtendModel::TABLE .' on '.TaskModel::TABLE . '.id = '.MetadataExtendModel::TABLE .'.task_id 
        // group by '.TaskModel::TABLE . '.id)',

        )
        ->join(UserModel::TABLE, 'id', 'owner_id', TaskModel::TABLE)
        ->left(UserModel::TABLE, 'uc', 'id', TaskModel::TABLE, 'creator_id')
        //->join(TaskModel::TABLE, 'id', 'task_id', MetadataExtendModel::TABLE)
        //->left(MetadataExtendModel::TABLE, 'tk', 'task_id', TaskModel::TABLE, 'id')
        ->join(CategoryModel::TABLE, 'id', 'category_id', TaskModel::TABLE)
        ->join(ColumnModel::TABLE, 'id', 'column_id', TaskModel::TABLE)
        ->join(SwimlaneModel::TABLE, 'id', 'swimlane_id', TaskModel::TABLE)
        ->join(ProjectModel::TABLE, 'id', 'project_id', TaskModel::TABLE)
        ->gte(TaskModel::TABLE.'.date_creation', $from)
        ->lte(TaskModel::TABLE.'.date_creation', $to)
        ->eq(TaskModel::TABLE.'.project_id', $project_id)
        ->asc(TaskModel::TABLE.'.id')
        ->findAll();
}

问题原因 #

当前计算OPEN_DAYS的标量子查询存在逻辑错误：

• 子查询未指定FROM子句，也未通过task_id关联到外层的任务表，导致max()函数是对整个MetadataExtend表的全局聚合，而非针对单个任务的聚合。
• 这种全局聚合会返回单一结果，数据库执行时会将外层所有任务行与这个单一结果匹配，最终导致整个查询仅返回一行数据。

修复方案 #

将OPEN_DAYS的子查询修改为针对单个任务的聚合查询，确保只统计当前任务的Closed_Date数据。有两种可行方式：

方式一：修正标量子查询，添加任务关联过滤 #

将OPEN_DAYS的子句改为限定当前任务ID，同时添加FROM子句：

'(SELECT CASE WHEN MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Closed_Date" THEN '.MetadataExtendModel::TABLE.'.value END) = "" 
    THEN DATEDIFF(CURDATE(), FROM_UNIXTIME('.TaskModel::TABLE.'.date_creation, "%Y-%m-%d"))
    ELSE DATEDIFF(MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Closed_Date" THEN '.MetadataExtendModel::TABLE.'.value END), FROM_UNIXTIME('.TaskModel::TABLE.'.date_creation, "%Y-%m-%d"))
    END AS OPEN_DAYS 
 FROM '.MetadataExtendModel::TABLE.' 
 WHERE '.MetadataExtendModel::TABLE.'.task_id = '.TaskModel::TABLE.'.id)'

方式二：改用LEFT JOIN + GROUP BY（推荐） #

避免多个标量子查询带来的性能问题，直接LEFT JOINMetadataExtend表并按任务ID分组，统一计算所需的元数据字段：

protected function getTasks($project_id, $from, $to)
{
    if (!is_numeric($from)) {
        $from = $this->dateParser->removeTimeFromTimestamp($this->dateParser->getTimestamp($from));
    }

    if (!is_numeric($to)) {
        $to = $this->dateParser->removeTimeFromTimestamp(strtotime('+1 day', $this->dateParser->getTimestamp($to)));
    }

    return $this->db->table(TaskModel::TABLE)
        ->columns(
            TaskModel::TABLE.'.id',
            SwimlaneModel::TABLE.'.name AS swimlane_name',
            CategoryModel::TABLE.'.name AS category_name',
            // 用聚合函数替代多个标量子查询
            'MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Type" THEN '.MetadataExtendModel::TABLE.'.value END) AS prueba',
            'MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Identifyer" THEN '.MetadataExtendModel::TABLE.'.value END) AS prueba2',
            'MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Area" THEN '.MetadataExtendModel::TABLE.'.value END) AS prueba3',
            'MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Reference" THEN '.MetadataExtendModel::TABLE.'.value END) AS prueba13',
            'MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Score_Before" THEN '.MetadataExtendModel::TABLE.'.value END) AS prueba4',
            'MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Root_Cause" THEN '.MetadataExtendModel::TABLE.'.value END) AS causa',
            ColumnModel::TABLE.'.title AS estado',
            'uc.name AS creator_name',
            UserModel::TABLE.'.name AS assignee_name',
            TaskModel::TABLE.'.date_creation',
            TaskModel::TABLE.'.title',
            TaskModel::TABLE.'.description',
            // 计算OPEN_DAYS
            'CASE WHEN MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Closed_Date" THEN '.MetadataExtendModel::TABLE.'.value END) = "" 
                THEN DATEDIFF(CURDATE(), FROM_UNIXTIME('.TaskModel::TABLE.'.date_creation, "%Y-%m-%d"))
                ELSE DATEDIFF(MAX(CASE WHEN '.MetadataExtendModel::TABLE.'.name = "Closed_Date" THEN '.MetadataExtendModel::TABLE.'.value END), FROM_UNIXTIME('.TaskModel::TABLE.'.date_creation, "%Y-%m-%d"))
                END AS OPEN_DAYS'
        )
        ->join(UserModel::TABLE, 'id', 'owner_id', TaskModel::TABLE)
        ->left(UserModel::TABLE, 'uc', 'id', TaskModel::TABLE, 'creator_id')
        // 添加LEFT JOIN关联元数据表
        ->left(MetadataExtendModel::TABLE, null, 'task_id', TaskModel::TABLE, 'id')
        ->join(CategoryModel::TABLE, 'id', 'category_id', TaskModel::TABLE)
        ->join(ColumnModel::TABLE, 'id', 'column_id', TaskModel::TABLE)
        ->join(SwimlaneModel::TABLE, 'id', 'swimlane_id', TaskModel::TABLE)
        ->join(ProjectModel::TABLE, 'id', 'project_id', TaskModel::TABLE)
        ->gte(TaskModel::TABLE.'.date_creation', $from)
        ->lte(TaskModel::TABLE.'.date_creation', $to)
        ->eq(TaskModel::TABLE.'.project_id', $project_id)
        // 按任务ID分组，确保聚合正确
        ->groupBy(TaskModel::TABLE.'.id')
        ->asc(TaskModel::TABLE.'.id')
        ->findAll();
}

说明 #

方式二推荐使用，因为多个标量子查询会导致数据库对每个任务执行一次子查询，性能远不如单次JOIN+GROUP BY的方式，同时也能避免类似的聚合逻辑错误。

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