getting result based on other table

I have 3 tables

reports(id,title,department_id)

tasks(id, title, status, created_at)

task_relationship(id,task_id,report_id,type)

I want to select result for below conditions

1- I want to select all tasks with status=0

2- department_id = 2

3- type = 2

Department_id is in reports and their relation is saved in task_relationship table.

Can it be achieved by eloquent?

Best Answer

Level 104

I believe this should work; not tested:

Task::where('status', 0)->whereHas('reports', function ($builder) {
    $builder->where('department_id', 2)
    ->wherePivot('type', 2);
})->get();

Level 2

@tykus : which type of relationship should i create in models for them?

Level 104

You would have belongsToMany relationships defined on the Task and Report models:

_Is the pivot table really named task_relationship_?

// Report.php
public function tasks()
{
    return $this->belongsToMany(Task::class, 'task_relationship')->withPivot('type');
}

// Task.php
public function reports()
{
    return $this->belongsToMany(Report::class, 'task_relationship')->withPivot('type');
}

Level 2

@tykus Models & tables exact names are below

1- Task = tasks (table name)

2- Report = work_reports (table name)

3- TopicTaskRelationship = topic_task_relationship (table name)

Level 104

You have a Model for the Pivot table? You can use the using method:

return $this->belongsToMany(Report::class, 'task_relationship')->using(TopicTaskRelationship::class);

I don't know that you really need the Pivot Model however.

Please or to participate in this conversation.