Back

Has Many Through Relationship Depth


Hey Guys,

I am just trying to understand the depth of the has many through relationship as well as if you want to go beyond how would you do this

Lets say you have a the following Models

  • Company
  • Branch
  • User

These are their defining relationships

  • Company hasMany Branches
  • Branch belongsTo Company
  • Branch hasMany Users
  • User belongsTo Branch

So here you have two one to many relationships... However because in actual sense the users while they belong to a particular branch all the users are part of the company essentially

here you would have a hasManyThrough so the company has many users through its branches and you would like to call the users for the company like so

$company->branch/branches->users();

while i understand that the hasManyThrough allows for this I wanted to know what would happen if you have a 3th or maybe a 4th level like so

$company->branch/branches->users->tasks();

or

$company->branch/branches->users->projects->tasks();

Ideally it would be awesome if the hasManyThrough would accomodate more depth but i think it only allows for that original 2 step depth from the company into the users through the branch

Does anyone know if it supports beyond this or can someone advise me on the best practice on how to allow for more depth like in the example above.

Thanks Guys

elena-kolevska replied 3 years ago Solution

Hi Xenit,

When we build real-world apps there are moments when we should maybe pause our academical thinking for a while. We all know about normalized databases and how our data should be structured, but is having relationships that deep really optimal for the long run and for the way that data is going to be used? Imagine the joins if you'd have to select multiple (a lot of) users that satisfy some criteria...

That's why I choose to store the company_id on the users table itself, and if a user can only belong to one branch, I store the office_id there too. Then I only have a pivot company_offices table...

So the tradeoff's not that bad here... you have one extra integer field - company_id (that never changes, btw) that will save you quite a lot of DB power cause you'll just have to say " where company_id = X"

In the end it all depends on the app's model and how's it being used.

Now for your question.. I didn't try it, but you should be able to chain endlessly, as long as you keep in mind that relationships are defined on a model, not on collections, so this won't work:

->users->projects->tasks();

There's something really cool that would work, though: eager loading with nested relationships:

$company->branch/branches->users()->with('projects.tasks')->get();

Hope that helps

Elena

rickshawhobo replied 3 years ago

I could be wrong but I don't think Eloquent hasManyThrough will support that kind of relationship depth. While I do agree you can go overboard with normalizing, there is some value in proper normalization. In your example I can see a valid use case for normalizing. Also imagine this example: Users have many threads which have many messages which have many likes. In your example it might be better to use a scope on the Task model to find all Tasks for all users in a certain branch for example. You can do that by nesting whereHas in the scope.

jimgwhit replied 3 years ago

elena-kolevska said:

When we build real-world apps there are moments when we should maybe pause our academical thinking for a while. We all know about normalized databases and how our data should be structured, but is having relationships that deep really optimal for the long run and for the way that data is going to be used? Imagine the joins if you'd have to select multiple (a lot of) users that satisfy some criteria...

That's why I choose to store the company_id on the users table itself, and if a user can only belong to one branch, I store the office_id there too. Then I only have a pivot company_offices table...

So the tradeoff's not that bad here... you have one extra integer field - company_id (that never changes, btw) that will save you quite a lot of DB power cause you'll just have to say " where company_id = X"

In the end it all depends on the app's model and how's it being used.

Now for your question.. I didn't try it, but you should be able to chain endlessly, as long as you keep in mind that relationships are defined on a model, not on collections, so this won't work:

->users->projects->tasks();

There's something really cool that would work, though: eager loading with nested relationships:

$company->branch/branches->users()->with('projects.tasks')->get();

Hope that helps

Elena

You sound like someone who has actually written real world applications. I myself wrote complete logistics software, where there were multi pickups and multi drops for a truck. And this was some complex software with driver payroll, accounts receivable, accounts payable, truck dispatch, etc. I never had to write some of these queries I see floating around on this forum. There are times where 3 individual queries are better than a hasmany through. There are times to use queries like that, but they should be avoided if possible. I believe in the keep it simple principle.

I believe some of these folks get wowed by the queries you can write in eloquent, but probably come back to the real world when they have to start writing some real world applications.
A simple example where I think 2 queries are better than a hasmany query:
Showing an owner then their pets: controller code to get a petowner and their pets using 2 queries:

public function ownerpet() {
            $ownerid = $_GET['ownerid'];
            $data['owners']=Powner::find($ownerid);
            $data['pets'] = DB::table('pets')
                        ->where('ownerid', '=', $ownerid)
                        ->orderBy('petname', 'asc')->get();

            return View::make('owner/ownerpet')->with('data',$data['owners'])->with('data2',$data['pets']);
        }

View showing owner first then listing of that owners pets:

<html>
<head>
</head>
<body>


<?php
echo $data->oname;  // the petowner
echo "<br>";
echo "======================";
echo "<br>";
foreach ($data2 as $pet){
    echo $pet->petid."   ".$pet->petname."<br>";  //pets owned by petowner

}    
?>
</body>
</html>

Some eloquent queries are easy to write, but they become a mess trying to display results in the view.

Jonas Staudenmeir replied 2 months ago

I created a HasManyThrough relationship with unlimited levels: Repository on GitHub

After the installation, you can use it like this:

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

    public function tasks() {
        return $this->hasManyDeep(Task::class, [Branch::class, User::class, Project::class]);
    }
}

Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us