Hello,

I'm trying to get a report and am running into a problem. Part of it is that the database design isn't optimal and I cannot change it at all so please keep that in mind when responding.

GOAL: Search for all all tasks comments based on project ID and created date (based on a starting date range and a closing date range). Display some data from the parent Task record as well as the children attachment records for each row of the table.

PROCESS I THOUGHT WOULD WORK: 1. Search Tasks for id.project 2. Right join tasks-comments 3. Right join attachments

PROBLEM The report shows all tasks, not just the ones within the date range.

Very basic schema attached.

Here's the code:

/Creates datastore tasks_by_project_id; used for displaying tasks and associated comments and files
       $this->src('teamKRS')
       ->query(MySQL::type(DB::table("tasks")
         ->where([
           ['tasks.projects_id',$projID],
           ['tasks.in_trash',NULL],
         ])

 ->leftjoin("tasks_status",'tasks.tasks_status_id','=','tasks_status.id')

 ->innerjoin('tasks_comments', function ($join) {
 $join->on('tasks.id','tasks_comments.tasks_id')
          ->where([
           ['tasks_comments.in_trash',NULL],
           ['tasks_comments.created_at','between',"2020-09-01","2020-09-02"],
         ]);
        })

 ->innerjoin('attachments',function ($join) {
 $join->on('tasks_comments.id','attachments.bind_id')
          ->where('attachments.bind_type','comments');
        })


   ->select('tasks.id')->alias('Task ID')
      ->addselect('tasks.id')->alias('ID')
   ->addSelect('tasks.name')->alias('Task')
   ->addSelect('tasks_status.name')->alias('Status')
      ->addSelect('tasks_comments.created_at')->alias('Updated')
   ->addSelect('attachments.file')->alias('File')

   ->leftjoin('extra_fields_list',function ($join) {
   $join->on('tasks.id','extra_fields_list.bind_id')
            ->where('extra_fields_list.extra_fields_id', '111');
          })
     ->select('extra_fields_list.value')->alias('Description')
      ))


->pipe($this->dataStore("client_tasks_by_project_id"));

I'm not sure if my logic is flawed or the code? Any suggestions greatly appreciated.

Thanks,

Rooze

Note: I hardcoded the date to troubleshoot that the variable wasn't an issue. Otherwise, it is normally a variable set by the search form.

Hi David,

Can you tell me how to view the SQL once I've added ->toMySQL(); to the code? I'm not sure how to call on it to print or echo in the view file and it doesn't show up in the console window. Thanks!