Troubleshooting task retrieval query in a Kanban board application

Question

I'm utilizing a Kanban board with the MetaMagik plugin and aiming to compare/subtract between the creation date and the closing date.

I've constructed a query for this purpose, but it doesn't function correctly when integrated into the model. I suspect the issue might be related to the join operation.

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();
}

I've delineated my code with hyphens for clarity, showcasing the various tests I've conducted. However, they have yet to yield the desired results. I intended to display all tasks within a specific date range, but only one task appears. I've also included comments on the join operations I've attempted. The query functions flawlessly when executed in the database. Upon execution and exportation to a CSV file, only one row appears, despite the expectation for more. I suspect that the join operation might be the root cause of the discrepancy in the returned results.