Retrieve distant relation through has-many-through for many-to-many relation in Laravel

1
votes

I have the following models in my application

  • User
  • Group
  • Task

which have the following relationships

  • User and Group have a many-to-many relationship
  • Task and Group have a many-to-many relationship

So basically a user can belong to more than one group and each group can have more than one task.

Following is the table structure.

users

  • id
  • name

groups

  • id
  • name

tasks

  • id
  • name

group_user

  • id
  • group_id (foreign key with groups table)
  • user_id (foreign key with users table)

group_tasks

  • id
  • group_id (foreign key with groups table)
  • task_id (foreign key with tasks table)

Now I want to retrieve all the tasks for the user.

I tried the following approaches and both didn't work.

Approach 1

  • $user->groups() gives the list of groups for a user
  • $group->tasks() gives the list of tasks for a group

So I tried

$user->groups()->tasks() but it didn't work.

Approach 2

I tried Has Many Through by adding this to my User model

public function tasks()
{
    return $this->hasManyThrough(Task::class, Group::class);
}

but even that didn't work. The following is the error that I am getting

QueryException in Connection.php line 713:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'groups.user_id' in 'field list' (SQL: select `tasks`.*, `groups`.`user_id` from `tasks` inner join `groups` on `groups`.`id` = `tasks`.`group_id` where `groups`.`user_id` = 1)

My guess is that this is happening because it is expecting one-to-many relationship, but I have a many-to-many relationship.

So is there a way to retrieve it without getting all groups and then looping through them?

2
phplaraveleloquentlaravel-5.2
You can try this User::with('groups.tasks') – z3r0ck
@z3r0ck Sorry not sure if I understand your suggestion. Can you please explain it a bit more? I have the User object for which I need to retrieve the tasks. – Sudar
Okay so in that case you can do this $user->load('groups.tasks'); – z3r0ck
@z3r0ck That will only give me an instance of the User model. How can I access tasks from it? I just checked and that doesn't have any attributes for tasks. – Sudar
This one is tricky... You can't use directly has many through because you have two pivot table... I guess you could do it with Collections and all but that's not very efficient if you have large dataset. Try looking for "Laravel has many through pivot table" on the web. – PeterPan666

2 Answers

7
votes
User Model

public function groups()
{
    return $this->belongsToMany('App\Group');
}

Group Model

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

Task Model

public function groups()
{
    return $this->belongsToMany('App\Group');
}

Retrieving all tasks for a user.

$user = User::find(1);

$user->load('groups.tasks');

$tasks = $user->groups->pluck('tasks')->collapse();
1
votes

You can also take a look at the extension of the HasManyThrough here: https://github.com/staudenmeir/eloquent-has-many-deep

It helps you to retrieve many sub-levels of your relationships. In your case, it would be User -> belongsToMany(Groups) -> blongsToMany (Tasks) just add your method to the user model like:

public function tasks()
    {
        return $this->hasManyDeep(
            'App\Task',['App\Group']
        );
    }