Join Question - KoolReport

Rooze McKelvey commented on May 2, 2019

I can't figure out how to use the querybuilder for a specific need.

We have three tables I'm working with: Tasks Tasks_Comments: Related to Tasks ['tasks.id','tasks_comments.tasks_id'] Attachments: Related to Tasks_Comments ['tasks_comments.id','attachments.bind_id']

I want to list all tasks and then also list information from the attachments table. Since attachments does not have the project it, only the comments id, I'm doing a joinon comments and then one from comments to attachments:

 ->query(MySQL::type(
         DB::table("tasks")->where('tasks.projects_id',$projID)
 ->leftjoin('tasks_comments', function ($join) {
 $join->on('tasks.id','tasks_comments.tasks_id') ;
        })
 ->leftjoin('attachments',function ($join) {
 $join->on('tasks_comments.id','attachments.bind_id')
          ->where('attachments.bind_type','comments');
        })
   ->select('tasks.id')->alias('Task ID')
   ->addSelect('tasks.name')->alias('Task')
   ->addSelect('tasks.description')->alias('Description')
   ->addSelect('attachments.file')->alias('File')
      ))

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

The problem is that I'm getting one task record per matching comment record even if there is no attachment record match. And I only need one task record per matching attachment.

How can I omit any related comments records that do not have a related attachment record?

Hope this makes some semblance of sense.

TIA,

Rooze

David Winterburn commented on May 7, 2019

Hi Rooze,

To omit rows with empty attachment please pipe through our Map process like this:

->query(...)
->pipe(new \koolreport\processes\Map([

    "{value}" => function($row) {
        if (empty($row["File"])) return null; //omit this row
        else return $row; //forward this row
    }

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

This is a general idea, You may want to change the column "File" or fix my typo. Please test this approach and let us know if it helps your issue. Thanks!

Rooze McKelvey commented on May 8, 2019

Thank you. I see how that works. It also made it clear that the way our database is structured (which is out of my control) makes this more challenging and I'm going to need to come up with a different way of addressing this. Here's why:

I want all tasks that match the project id to show up at least once.

I also want to show any files that are attached to the comments for each task. However, not every comment has an attachment. And not every Task has a comment yet.

If I eliminate the row based on whether there is a file, then I also lose the tasks that do not have comments yet or that only have comments without attachments. Which are generally all tasks that have not yet been started.

UGH.

Thanks for your help. I'm going to need to work with this a bit more.

David Winterburn commented on May 9, 2019

Hi Rooze,

Yours is an intriguing task. If your data has rows with the following fields: "task", "comment" and "file" which could be null or not, I'm sure you can figure out a rule among those three to omit the rows that don't fit your requirement. Let us know if you need any further information. Thanks!

KoolReport's Forum

Join Question #848

Build Your Excellent Data Report