'Laravel Eloquent optimize request with many relationships
I have actually a DB structure with a dashboard_users table with several morphedByMany relationship with retailers, brands, private_groups tables, etc (there are several models which are morphed to this relation). These relationships are handled through a roleables table. So my dashboardUser model has these functions :
public function retailer()
{
return $this->morphedByMany(Retailer::class, 'roleable')
->withPivot('role_id')
->withTimestamps();
}
public function brand()
{
return $this->morphedByMany(Brand::class, 'roleable')
->withPivot('role_id')
->withTimestamps();
}
public function privateGroup()
{
return $this->morphedByMany(PrivateGroup::class, 'roleable')
->withPivot('role_id')
->withTimestamps();
}
Some dashboardUsers can have a relation with one or many Retailer, but also one or many Brand, or with some PrivateGroup. There is no rule here, a DashboardUser can have only one relation but also a hundred. In my dashboard I have a screen where I need to manage all these relations and show which user has which relation. Actually to retrieve all my DashboardUsers I'm making this simple Eloquent request :
$aUsersList = DashboardUser::with(['brand', 'retailerBrand', 'retailer', 'retailerGroup', 'privateGroup'])
->get()
->unique('uuid')
->all();
which is working fine... except that now that I have around 3k rows on each table the request takes more than 10 seconds to be executed.
I'm wondering how I could optimize this request to receive the same results.
I'm thinking of making several leftJoins this way but I'm stuck with 2 problems:
$aUsersList = DashboardUser::select("dashboard_users.*",
"retailers.uuid", "retailers.name", "retailers.postal", "roleables.role_id",
"brands.uuid", "brands.name",
"private_groups.uuid", "private_groups.name"
)
->leftJoin('roleables', function($query){
$query->on("dashboard_users.uuid", 'roleables.dashboard_user_uuid');
})
->leftJoin('retailers', function($query){
$query->on("retailers.uuid", 'roleables.roleable_id');
})
->leftJoin('brands', function($query){
$query->on("brands.uuid", 'roleables.roleable_id')
;
})
->leftJoin('private_groups', function($query){
$query->on("private_groups.uuid", 'roleables.roleable_id')
;
})
->get()
->all()
;
This request is much more quicker to be executed but it's not returning all the data (it seems to return only dashboardUsers with retailer relationship). And also I fear I'll be loosing my relations this way (all the fields are aggregated in a single object). So is there a way to rehydrate my model with it's relations?
Last but not least I'm using Lumen V6 (so pretty close to Laravel 6).
Thank you for pushing me in the right direction.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
