Has Many Through Relationship Depth

replied 8 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

Last updated 8 years ago.

0

rickshawhobo

replied 8 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.

0

jimgwhit

replied 8 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.

Last updated 5 years ago.

0

staudenmeir

replied 5 years 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]);
    }
}

0

Moderators