Full Text Search for projects

[rafaelqg]

Just sharing a function I have implemented and may be useful for more users.

Giving a project Id and a text to search, it is going to look in all tasks and taks log its occurrence, and will return information to build a nice interface over its that. Who has interest, jut need to consume from the favorite front end to consume dotproject data.

`

  function projectFullTextSearch($text, $projectId){
    
    $text = addslashes(strtolower($text));
    $projectId = addslashes($projectId);
    $query = new DBQuery();
    $query->addQuery("'project_task' as 'type', task_id as id, task_name as name, task_description as description);
    $query->addTable("tasks", "t");
    $query->addWhere("lcase(task_name) like '%" . $text . "%' or  lcase(task_description) like '%" . $text . "%'");
    $query->addWhere("task_project = $projectId");
    $sql = $query->prepare();
    $tasks = db_loadList($sql);

    $query = new DBQuery();
    $query->addQuery("'task_log' as 'type', tl.task_log_id as id, tl.task_log_name as name, tl.task_log_description as description,");
    $query->addTable("task_log", "tl");
    $query->addWhere("lcase(tl.task_log_name) like '%" . $text . "%' or  lcase(tl.task_log_description) like '%" . $text . "%'");
    $query->addJoin("tasks", "t", "t.task_id = tl.task_log_task", "inner");
    $query->addWhere("t.task_project = $projectId");
    $sql = $query->prepare();
    $tasks_logs = db_loadList($sql);
    return array_merge($tasks, $tasks_logs);
}`

Regards
Rafael