I have the following models: CardBoard, User, UserPricingPlans, PricingPlanLimits
Note: Don't mind if there is something wrong with the models code.They are working fine.
CardBoard
class CardBoard extends Model{ public function user(){ return $this->belongsTo('Models\User','id_user'); } } User
class User extends Model{ public function pricingPlans(){ return $this->hasMany('Models\UserPricingPlan','id_user'); } } PricingPlan
class PricingPlan extends Model{ public function limits(){ return $this->hasOne('Models\PricingPlanLimits','id_pricing_plan','id_pricing_plan'); } } PricingPlanLimits
I'll not describe that Model, its not necessary for the problem. But keep in mind that there is an attribute called maxBoards.
The problem is that I only have the CardBoard Model Instance to work on and I want to get the maxBoard attribute from PricingPlanLImits. So I did it like this:
Note: I Already have the CardBoard Model Instance here!
$maxBoard = $cardBoard->user->pricingPlans->last()->limits->maxBoard; return $maxBoard; The code above runs great, but the number of queries generated by this operation is an overhead to me. Eloquent do an SELECT for every Relationship called and I don't want all these data and operations.
{ "query": "select * from `users` where `users`.`id_user` = ? limit 1", "bindings": [ ], "time": 0.96 } { "query": "select * from `users_princing_plan` where `users_princing_plan`.`id_user` = ? and `users_princing_plan`.`id_user` is not null", "bindings": [ ], "time": 0.8 } { "query": "select * from `pricing_plan_limits` where `pricing_plan_limits`.`id_pricing_plan` = ? and `pricing_plan_limits`.`id_pricing_plan` is not null limit 1", "bindings": [ ], "time": 0.88 } Isn't there an way to optmize this and run fewer queries in a Eloquent-Way ?
3 Answers
Answers 1
you can get a data in one query if you use with() method.
for example: CardBoard::with('user.pricingPlans')->get();
so can optimize your query using with method.
Answers 2
Previous comments were not too relevant to this solution...
example
$cardboard->user()->whereHas('pricingPlans', function ($plans) { $plans->selectRaw('price_plan_limits.id, MAX(price_plan_limits.maxBoard) as MB')) ->from('price_plan_limits') ->where('price_plan_limits.id', 'price_plan.id') ->orderBy('MB', 'DESC') })->get(); Answers 3
I usually go in reverse order:
$maxBoard = PricingPlanLimits::whereHas(function($q)use($cardBoard){ $q->whereHas('PricingPlan', function($q1)use($cardBoard){ $q1->whereHas('User', function($q2)use($cardBoard){ $q2->whereHas('CardBoard', function($q3)use($cardBoard){ $q3->where('id', $cardBoard['id']); }); }); // Probably you have to improve this logic // It is meant to select select the last occurrence $q1->orderBy('created_at', 'desc'); $q1->limit(1); }); })->first()['maxBoard']; Totally untested, but this should be the correct approach to achieve your goal in one query.
0 comments:
Post a Comment