best way to retrive a collection from -many to many- connected to -one to many- relation (laravel7)

Question

you can see a part of my database in the image below:

the goal of this design was to able the admins send alerts to users filtered by center and field(dynamically). if you think this is a bad design tell me (please say why and how should I improve my design).

now if I want to get the alerts for a user I should do this:

    $userAlerts = collect();

    auth()->user()->branch()->first()->groups()->get()->each(function ($item, $key) use ($userAlerts) {
        $item->alerts()->get()->each(function ($item, $key) use ($userAlerts) {
            $userAlerts->push($item);
        });
    });

this code is ridiculous and I don't want to do it in this way.

instead i want to do something like this:

    $alerts = auth()->user()->branch()->groups()->alerts() // i want this // method 1

or

    $alerts = auth()->user()->alerts() //or maybe this // method 2

can I do something like this without changing the database? (method 1 or 2).

Answer 1

You can use laravel's default Has Many Through on branch model to pick associated alerts

class Branch extends Model
{

    public function alerts()
    {
        return $this->hasManyThrough(
            'App\Models\Alert',
            'App\Models\Group',
            'branch_id', // Foreign key on alert table...
            'group_id', // Foreign key on group table...
            'id', // Local key on branch table...
            'id' // Local key on group table...
        );
    }

}

Uisng this way you can query user alerts as

$alerts = auth()->user()->branch()->alerts()

Or you can use third party package eloquent-has-many-deep which provides an ease to extend hasManyThrough on multiple related models

class User extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function alerts()
    {
        return $this->hasManyDeep(
            'App\Models\Alert',
            ['App\Models\Branch', 'App\Models\Group'], // Intermediate models, beginning at the far parent (User).
            [
               'user_id', // Foreign key on the "branch" table.
               'branch_id',    // Foreign key on the "group" table.
               'group_id'     // Foreign key on the "alert" table.
            ],
            [
              'id', // Local key on the "user" table.
              'id', // Local key on the "branch" table.
              'id'  // Local key on the "group" table.
            ]
        );
    }
}

And you can directly get user alerts as

$alerts = auth()->user()->alerts()

Lastly you can define alerts relation in user model by adding some joins to directly pull related alerts

class User extends Model
{
  
    public function alerts()
    {
        return $this->belongsTO('App\Branch', 'branch_id')
            ->join('branch_group', 'branches.id', '=', 'branch_group.branch_id')
            ->join('alerts', 'branch_group.group_id', '=', 'alerts.group_id')
            ->select('alerts.*');
    }

}