How to join 2 tables in Laravel?

Question

I have two models a projects and a tasks. On the task model I have a fK project_id, and 2 integer fields to enter duration in mins and hours. I want to click on the project, get its id and then look into the task table and calculate the hours and mins for that project based on all the tasks that have the fk of the project_id.

Here is what I've tried

Project - Model

public static function getTotalHoursInProgressProject() {
    $project =Project::all();
    $projectHrsProgress = Task::where('project_id', '=', 'project.id')->where('board_column_id', '=' ,'6')->sum('duration_hrs');
    $taskHours =  $projectHrsProgress * 60;
    $projectMinProgress = Task::where('project_id', '=', 'project.id')->where('board_column_id', '=' ,'6')->sum('duration_min');
    $finalHrsInprogress = ($taskHours + $projectMinProgress )*0.016667;
    // $finalHrsInprogressTotal = (float) $finalHrsInprogress;

    return number_format((float)$finalHrsInprogress, 2, '.', '');;
  }

In the controller

public function show($id)

{

    $this->project = Project::findOrFail($id);
    $this->categories = TaskCategory::all();
    $this->taskInProgress = Project::getTotalHoursInProgressProject();


    return view('admin.projects.tasks.show', $this->data);
}

In the view

 <div class="col-md-3">
                                            <div class="white-box p-t-10 p-b-10 bg-warning">
                                                <h3 class="box-title text-white">In Progress Hours</h3>
                                                <ul class="list-inline two-part">
                                                    <li><i class="icon-layers text-white"></i></li>
                                                    <li class="text-right"><span id="" class="counter text-white">{{ $taskInProgress }}</span></li>
                                                </ul>
                                            </div>
                                        </div>

Here is the error I'm getting

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'duration_min' in 'field list' (SQL: select sum(duration_min) as aggregate from tasks where project_id = project.id and board_column_id = 6 and tasks.company_id = 1)

Answer 1

You can create laravel has relation in your Project model then you can access the associated data in query.

You can also get the total time by creating two function in model like below.

In Project Model:

public function Task()
 {  
   return $this->hasMany('App\Task');    
}

public function TotalTaskHour()
{  
    return $this->hasMany('App\Task')
        ->selectRaw('SUM(duration_hrs) as total_hrs')
        ->groupBy('project_id');    
}

public function TotalTaskMinute()
    {  
        return $this->hasMany('App\Task')
            ->selectRaw('SUM(duration_min) as total_mins')
            ->groupBy('project_id');    
    }

Now in your controller access associated data like this.

In controller :

public function show($id)
{

    $project = Project::with('TotalTaskHour')->with('TotalTaskMinute')->with('Task')->findOrFail($id);
    $totalHour = $project->TotalTaskHour->total_hrs;
    $totalHoour = $project->TotalTaskHour->total_mins;

   $taskInProgress = $project->getTotalHoursInProgressProject();

    dd($project); 
}

Make sure you have also created task model.

Answer 2

Use one to one relationship between two models Project & Task

public function show($id)
{

    $project = Project::with('tasks')->findOrFail($id);
    $taskInProgress = $project->getTotalHoursInProgressProject();


    return view('admin.projects.tasks.show',compact('taskInProgress'));
}