Laravel - super long relation, it doesn't feel right

Question

Okay, This is my method:

public function edit(Request $request, $ent, $room, $obj)
{
    $input = $request->except(['_token']);

    Enterprise::where('bedrijfsnaam', $ent)->first()->rooms()->where('name', $room)->first()->objects()->where('name', $obj)->first()->update($input);

    return redirect('/enterprise/'.$ent.'/room/'.$room);
}

As you can see, at Enterprise::where() I have a really long relation, But it doesn't feel right to have such a long relation. Is it just okay to have one this large or is there a better way to do it?

Answer 1

I suggest you figure out a way to view the execution history for all of the SQL statements across your application. This can be an absolute god-send when trying to untangle or optimize your queries.

I'd recommend using https://github.com/barryvdh/laravel-debugbar . It has a lot of other features as well but presents everything very neatly. There are ways to view the history with custom event listeners and such (https://stackoverflow.com/a/27753889/3224736) but the extra effort for debugbar would be worth it, IMO.

---

As for your specific query, I believe it'd compile into four separate queries. Three for each time you call first() and one for the update.

Eloquent does allow defining distant relationships with properties like hasManyThrough (https://laravel.com/docs/5.2/eloquent-relationships#has-many-through) but they can get as messy as manually querying the relationships. I'd give it a try, at least.

The other option is to use joins for the three selects. Something like

Enterprise::where('bedrijfsnaam', $ent)
    ->join('rooms', function ($join) {
        $join->on('enterprise.id', '=', 'rooms.enterprise_id')
             ->where('rooms.name', '=', $room);
    })
    ->join('objects', function ($join) {
        $join->on('rooms.id', '=', 'objects.room_id')
             ->where('objects.name', '=', $obj);
    })->first();

(That's just me guessing at your schema, though. You can modify that as needed).

Using one big join would compile into one query, meaning one round trip to the database. Just be sure you have proper indexes on your columns or the query could get bogged down.

-

Or you can investigate eager loading and lazy eager loading your relationships.

That would at least reduce the apparent complexity of your query. But those are most useful if you anticipate iterating the relationships, rather than just selecting one of them. They wouldn't likely do much to speed up your current query.

---

As complete tangents, I'd recommend investigating two items.

1. Make sure your models define fillable arrays. Without them, passing the entire $input into it, even if you try to manually prune it, can lead to nasty security/integrity holes.

2. See if you can use a route or action instead of a raw redirect. Laravel is built to use MVC through-and-through so you might as well learn how to use it early on, rather than patch all your manual stuff later.