How to search for all associations in a relationship tree in Laravel?

Question

I’m having a dilemma. I have tables that relate, like,:

internal_clients->Subsidiaries->departments->job_titles->users

I also have their respective models.

My question is:

How do I get all the data associated with users from the top of the chain (internal_clients) ?

I’m trying to follow the Laravel documentation using hasManyThrough.

But the doc only explains how to do it in a string of 3 tables. They teach to place an intermediate table (model) as the second parameter of the hasManyThrough(Classebase::class, Classeintermediaria:class).

However, in my case that has several tables between users and internal_clients, how would I do it ? What would be the intermediate table ?

I would like to make a query that returns which user internal_client, subsidiary, Department and jobTitle (associated with users).

I’m trying to do it this way:

Model Internalclient

public function users()
 {
    return $this->hasManyThrough(User::class, InternalClient::class);
 }

Controller Usercontroller

 public function allRelations($internalClientId)
 {
     $internalClient = InternalClient::find($internalClientId);
       
      $users = $internalClient->users;

      return response()->json($users, 201);
 }

• The Internalclient id arrives in the controller above.

When I access to route, is returned to me the error below:

In short: I wonder if there is a way to get all the data (of all the tables that are in this hierarchical tree) that are associated with the User.

Thank you!

Answer 1

The problem is that you are trying to access multiple levels of relationship using hasManyThrough, which actually only manages to relate through "trough" a single sublevel. See which error p is stating that there is no id fk in the table Internal client, when in fact, for this line you posted internal_clients->subsidiaries->departments->job_titles->users, it seems to me that the relationship (fk) is in job_titles.

With what you posted of information, abstracting the data model mentally, I would say that you get a relationship of type hasManyTrough only in departments using job_title.

Then you really have to go from Join among the various tables that connect from internal_clients to users.

But do so, create a method in the internal_clients model that builds a Scope so you can use:

function scopeUsers($query) {
    return $query->join("subsidiaries", "fk-de-subsidiaries-em-internal-clients", "=", "pk-em-subsidiaries")
                 ->join("departments", "fk-de-departments-em-subsidiaries", "=", "pk-em-departments")
                 ->join("job_titles", "fk-de-job_titles-em-departments", "=", "pk-em-job_titles")
                 ->join("users", "fk-de-users-em-job_titles", "=", "pk-em-job_titles");
}

You may not be 100% adherent to your model because you didn’t post it here.

To check the Joins documentation - https://laravel.com/docs/7.x/queries#joins

To check the scope documentation (Scopes) - https://laravel.com/docs/7.x/eloquent#query-Scopes

Answer 2

I tried to do with with. But the whole chain of relationships returned. Behold:

public function allRelations($internalClientId)
{               
   $internalClients = InternalClient::with('departments.sections.jobTitles.users')->find($internalClientId);
   return response()->json($internalClients , 201);
}

That was the return:

https://paste.laravel.io/b3c80b26-f37f-4e56-9f60-5535379f73d9

So I tried to do with the Joins, according to the other boy’s answer (So I’m gonna take it). Behold:

public function allRelations($internalClientId)
{              
     $internalClients = InternalClient::join('departments', 'departments.internal_client_id','=','internal_client_id')
     ->join('sections', 'sections.department_id','=','departments.id')  
     ->join('job_titles', 'job_titles.section_id','=','sections.id') 
     ->join('users', 'users.job_title_id','=','job_titles.id')
     ->select(                                            
              'users.id AS user_id',
              'users.name AS user_name',
              'users.surname AS user_surname',
              'users.email AS user_email',
              'departments.id AS department_id' ,
              'departments.name AS department_name',
              'sections.id AS section_id', 
              'sections.name AS section_name', 
              'job_titles.id AS job_title_id',  
              'job_titles.name AS job_title_name'                                                  
              )
      ->where('internal_client_id',$internalClientId) 
      ->groupBy('users.id')     
      ->get();    
    
       return response()->json($internalClients, 201);
}

And that was the way out:

[
  {
    "user_id": 1,
    "user_name": "GLEISIANE",
    "user_surname": "ROUCAS",
    "user_email": "[email protected]",
    "department_id": 2,
    "department_name": "FISCAL",
    "section_id": 1,
    "section_name": "CONTABIL - SETOR 1",
    "job_title_id": 1,
    "job_title_name": "AUXILIAR CONTÁBIL"
  },
  {
    "user_id": 2,
    "user_name": "ANDREWS",
    "user_surname": "RIBEIRO",
    "user_email": "[email protected]",
    "department_id": 2,
    "department_name": "FISCAL",
    "section_id": 1,
    "section_name": "CONTABIL - SETOR 1",
    "job_title_id": 1,
    "job_title_name": "AUXILIAR CONTÁBIL"
  }
]

• I wanted to do with the with, I know it can, but unfortunately I could not. So I decided to do it right away with Join so as not to delay the work.