Eloquent Relationship with multiple many-to-many relationships - Laravel

I have table structures like this :

jobs

• id
• name

types

• id
• name

job_type (pivot table)

• id
• job_id
• type_id

tasks

• id
• description

job_type_task (pivot table)

• id
• job_type_id
• task_id

Notes :

1. jobs has many types and vice versa
2. job_type has many tasks and vice versa

My problem is with this table structures, How can I establish a relationship between 'job' and 'tasks' tables to access all tasks associated with a job?

I have tried many solution like using hasMany or hasManyThrough but none is working.

Should i change the table stuctures? or any solution to access tasks via job eloquent model?

there is no stock laravel relation for your case as your relation is not direct but through 2 entities and laravel only natively support 1 level down so you can check this package that might help you achieve what you need https://github.com/staudenmeir/eloquent-has-many-deep

also there was a similar question that might be similar to your use case that helps creating a new custom relation since the stocked relations is not enough Custom Laravel Relations? and also this article explains it nicely https://stitcher.io/blog/laravel-custom-relation-classes

here is the implementation of custom relation in case you need it

<?php

namespace App\Relations;

use App\Models\Job;
use App\Models\Task;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\Relation;

class JobTaskRelation extends Relation
{
    /** @var Task|Builder */
    protected $query;

    /** @var Job */
    protected $parent;

    public function __construct(Job $parent)
    {
        parent::__construct(Task::query(), $parent);
    }

    /**
     * @inheritDoc
     */
    public function addConstraints()
    {
        $this->query->join('job_type_task', 'job_type_task.task_id', '=', 'tasks.id')
            ->join('job_type', 'job_type.id', '=', 'job_type_task.job_type_id');
        if (static::$constraints) {
            $this->query->where('job_type.job_id', '=', $this->parent->getKey());
        }
    }

    /**
     * @inheritDoc
     */
    public function addEagerConstraints(array $models)
    {
        $this->query
            ->whereIn('job_type.job_id', $this->getKeys($models))
            ->with('jobTypes')
            ->select('tasks.*');
    }

    /**
     * @inheritDoc
     */
    public function initRelation(array $models, $relation)
    {
        foreach ($models as $model) {
            $model->setRelation(
                $relation,
                $this->related->newCollection()
            );
        }

        return $models;
    }

    /**
     * @inheritDoc
     */
    public function match(array $models, Collection $results, $relation)
    {
        $results = $results->unique('id');
        if ($results->isEmpty()) {
            return $models;
        }

        foreach ($models as $model) {
            $model->setRelation(
                $relation,
                $results->filter(function (Task $task) use ($model) {
                    return $task->jobTypes->pluck('job_id')->contains($model->id);
                })
            );
        }

        return $models;
    }

    /**
     * @inheritDoc
     */
    public function getResults()
    {
        return $this->query->get();
    }
}

and now you job model will be something like this

<?php

namespace App\Models;

use App\Relations\JobTaskRelation;
use Illuminate\Database\Eloquent\Model;

class Job extends Model
{
    public function types()
    {
        return $this->belongsToMany('App\Models\Type');
    }

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

    public function tasks()
    {
        return new JobTaskRelation($this);
    }
}

and task model will be

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Task extends Model
{
    public function types()
    {
        return $this->belongsToMany('App\Models\Type');
    }

    public function jobTypes()
    {
        return $this->belongsToMany('App\Models\JobType');
    }
}

important note to take care of:

since laravel assumes that when we have a model the table is plural and your scheme define the pivot tables as singular it's important that when you define the pivot models to set $table to the singular table name and you are good to go

now you can use Job::find(1)->tasks or Job::with('task')->get()

you might need to add more functions based on what you need in the future and you will need to maintain it

Using accessor

also another solution that came to my mind did you consider accessor ? you can define an accessor in your job model so what it does the needed queries to fetch the data if you are just interested about reading the data and it will be much simpler like so

    // app/Models/Job.php
    public function tasksData(): Attribute
    {
        return Attribute::make(
            get: function () {
                $tasks = collect();
                foreach ($this->types as $type) {
                    $tasks = $tasks->merge($type->tasks);
                }
                return $tasks->unique('id');
            },
        );
    }

and in case you always wants it you can add it to model appends and that case laravel will evaluate it will loading the data

protected $appends = ['tasksData'];