Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Optimize aggregate queries

I have a query like $products = Product::query()->where(...).... I want to read the maximum dimensions for my double range sliders something like:

    $dimensionLimits = [
        'width' => [
            'min' => $products->min('width'),
            'max' => $products->max('width')
        ],
        'height' => [
            'min' => $products->min('height'),
            'max' => $products->max('height')
        ],
        'depth' => [
            'min' => $products->min('depth'),
            'max' => $products->max('depth')
        ],
    ];

My problem that this is 6 relative slow queries. Is there a way to optimize this into a single query? I know it is possible by adding raw SQL, but is there a way to do it with Eloquent generated queries?

Tags:

2

Answers


  1. 0

    To do it in raw SQL, use:

    Product::query()->selectRaw('MIN(width) as min_width, MAX(width) as max_width, MIN(height) as min_height, MAX(height) as max_height, MIN(depth) as min_depth, MAX(depth) as max_depth')->get();
    Login or Signup to reply.
  2. 0

    The main problem that I cannot reuse the $products variable this way.
    Later I use $products->paginate() but with the raw query it gives bad
    results

    In this scenario you can do something like this to get both pagination() and aggregation results.

    Step1 :- Fetch paginated records

    $products = Product::query()->where(...);
$paginated_product = $products->paginate();

    Step 2:- Fetch aggregated result-set

    $maxMinDimensions = $products->selectRaw('MIN(width) as min_width, MAX(width) as max_width, MIN(height) as min_height, MAX(height) as max_height, MIN(depth) as min_depth, MAX(depth) as max_depth')->first();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search