CODEWITHSUNDEEP
laravellaravel-5eloquent
Igor Morse
Igor Morse

Reputation: 687

Eloquent Relationships Optimize Query

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 ?

Upvotes: 10

Views: 1641

Answers (5)

Kevin Bui
Kevin Bui

Reputation: 3045

Typically it is totally fine to achieve the result with 3 queries, a query like that normally take 10ms. But each of your queries is taking nearly 1 second, which is way too long. I don't know the reason why.

You are also able to achieve the same result with a single query though.

Your naming is a bit unconventional. I use a more popular naming convention, hopefully you can apply to your case.

class CardBoard extends Model
{
   protected $table = 'card_boards';

   public function user()
   {
      return $this->belongsTo(User::class,'user_id');
   }
}

class User extends Model
{
   protected $table = 'users';

   public function pricingPlans()
   {
      return $this->hasMany(UserPricingPlan::class,'user_id');
   }
}

class PricingPlan extends Model
{
   protected $table = 'pricing_plans';


   // This is a one to one relationship so I use a singular form.
   public function limit()
   {
      return $this->hasOne(PricingPlanLimit::class,'pricing_plan_id');
   }
}

class PricingPlanLimit extends Model
{
    protected $table = "pricing_plan_limits";
}

The query to get the result:

$carboardId = 100;

$latestPricingPlanSubQuery = PricingPlan::select('pricing_plans.*', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->groupBy('user_id');

$carboard = Carboard::select('card_boards.*', 'pricing_plan_limits.max_board')
    ->join('users', 'cardboards.user_id', '=', 'users.id')
    ->joinSub($latestPricingPlans, 'latest_pricing_plans', function ($join){
        $join->on('users.id', '=', 'latest_pricing_plans.user_id');
    })
    ->join('pricing_plan_limits', 'latest_pricing_plans.id', '=', 'pricing_plan_limits.pricing_plan_id')
->find($cardboardId);

The key thing is to have a sub query getting only the latest pricing plans for each user.

Upvotes: 0

ivanhoe
ivanhoe

Reputation: 4753

You probably could reduce the number of calls by using hasManyThrough relation (See:https://laravel.com/docs/5.4/eloquent-relationships#has-many-through).

In that case you'd have something like

class CardBoard extends Model{

   public function userPricingPlans(){

      return $this->hasManyThrough('Models\UserPricingPlan', 'Models\User', 'id_user', 'id_user');
   }
}

And then you could call it like this:

$maxBoard = $cardBoard->userPricingPlans->last()->limits->maxBoard;

To have it all in a single query you'd need fluent and real SQL joins, can't be done with eloquent (but then you'll miss all of ORM fun)

Upvotes: 0

clod986
clod986

Reputation: 2647

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.

Upvotes: 0

user320487
user320487

Reputation:

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();

Upvotes: 0

Shailesh Ladumor
Shailesh Ladumor

Reputation: 7242

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.

Upvotes: 1

Related Questions