Hi,
after upgrading to MySQL5 the overview did not work anymore

SELECT ta.*,project_name, project_id, project_color_identifier,tp.task_pinned
FROM `projects` as pr,`tasks` as ta,`user_tasks` as ut
LEFT JOIN `user_task_pin` AS tp ON tp.task_id = ta.task_id
AND tp.user_id = 2
WHERE ut.task_id = ta.task_id
AND ut.user_id = '2'
AND ( ta.task_percent_complete < 100 or ta.task_percent_complete is null)
AND ta.task_status = '0'
AND pr.project_id = ta.task_project
AND project_active = 1
AND project_status != 4
AND task_dynamic = 0
AND ta.task_end_date != ''
AND ta.task_end_date != '0000-00-00 00:00:00'
GROUP BY ta.task_id
ORDER BY ta.task_end_date,task_priority DESC resulted in an unknown column 'ta.task_id' in 'on clause'

With the following changes in /modules/tasks/todo.php it works for me again:
Search for:
$q->addQuery('ta.*');
$q->addQuery('project_name, project_id, project_color_identifier');
$q->addQuery('tp.task_pinned');
$q->addTable('projects', 'pr');
$q->addTable('tasks', 'ta');
$q->addTable('user_tasks', 'ut');
$q->leftJoin('user_task_pin', 'tp', 'tp.task_id = ta.task_id and tp.user_id = ' . $user_id);
$q->addWhere('ut.task_id = ta.task_id');
$q->addWhere("ut.user_id = '$user_id'");
$q->addWhere('( ta.task_percent_complete < 100 or ta.task_percent_complete is null)');
$q->addWhere("ta.task_status = '0'");
$q->addWhere("pr.project_id = ta.task_project");

Replace with:
$q->addQuery('ta.*');
$q->addQuery('project_name, project_id, project_color_identifier');
$q->addQuery('tp.task_pinned');
$q->addTable('projects', 'pr');
$q->innerJoin('tasks', 'ta', 'pr.project_id = ta.task_project');
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = ta.task_id');
$q->leftJoin('user_task_pin', 'tp', 'tp.task_id = ta.task_id and tp.user_id = ' . $user_id);
$q->addWhere("ut.user_id = '$user_id'");
$q->addWhere('( ta.task_percent_complete < 100 or ta.task_percent_complete is null)');
$q->addWhere("ta.task_status = '0'");

Now the SQL-Statement is ok with MySQL 5.0.15

this work for me:

change
$tsql = "SELECT $select FROM $from $join WHERE $where" .
by
$tsql = "SELECT $select FROM ($from) $join WHERE $where" .
in file tasks.php

Bye.

not just tasks.php

MySQL 5 is not at the moment supported by dotProject, and things will break. The FROM $from part of SQL is most often the case, and a lot of it is scattered outside of DBQuery abstract.

this work for me:

change
$tsql = "SELECT $select FROM $from $join WHERE $where" .
by
$tsql = "SELECT $select FROM ($from) $join WHERE $where" .
in file tasks.php

Bye.




Hi,
This worked swell for me too.

Thank you.