How i can get price range faster way (min,max) from products table

here is my code.


      $attributes = Product::where('status', ProductStatus::ACTIVE);
      if ($request->category_id) {
         $attributes =  $attributes->where('category_id', $request->category_id);
      }
      if (!$this->isAdmin()) {
         $attributes =  $attributes->whereIn('category_id', $this->UserCategoryAccess()['accessable_categories']);
      }
      $attributes = $attributes->select(DB::raw('MIN(' . 'shipping_cost' . ') as minimumCost'), DB::raw('MAX(' . 'shipping_cost' . ') as maximumCost'))->first();
      $increment = (intval(floor($attributes->minimumCost)) == intval(ceil($attributes->maximumCost))) ? 1 : 0;
      $costRange = ['minimumCost' => intval(floor($attributes->minimumCost)), 'maximumCost' => intval(ceil($attributes->maximumCost)) + $increment];

LaryAI

3 years ago

Level 58

The current code is already optimized and there is not much room for improvement. However, one suggestion would be to add an index on the shipping_cost column to speed up the MIN and MAX calculations.

Another suggestion would be to use Laravel's query builder pluck method instead of select and first to retrieve only the minimum and maximum values. This can be done as follows:

$attributes = Product::where('status', ProductStatus::ACTIVE);
if ($request->category_id) {
    $attributes = $attributes->where('category_id', $request->category_id);
}
if (!$this->isAdmin()) {
    $attributes = $attributes->whereIn('category_id', $this->UserCategoryAccess()['accessable_categories']);
}
$costRange = [
    'minimumCost' => $attributes->pluck(DB::raw('MIN(shipping_cost)')),
    'maximumCost' => $attributes->pluck(DB::raw('MAX(shipping_cost)')),
];

This will retrieve only the minimum and maximum values without the need for the floor, ceil, and increment calculations.

Please or to participate in this conversation.