lundi 20 juin 2016

Laravel 4.2 n+1 without eager loading

I have a table with products

products
------------------------------------------------
id  product_code    purchase_type   quantity
1   106             new             1
2   107             renew           3

and a pricelist for products

price_list
----------------------------------------------------------------------
id  product_code    purchase_type   minimum     maximum     unit_price
1   106             new             1           25          20
2   106             new             26          50          16
3   106             new             51          100         14

When I display the products on a page, I need to display the price with the formula

getPrice(product_code, purchase_type, quantity) for every product

Using

foreach(Product::all() as $product){
    {{ $product->product_code }}
    {{ $product->purchase_type }}
    {{ $product->quantity }}
    {{ PriceList::getPrice($product->product_code, $product->purchase_type, $product->quantity) }} - this one is the n+1 problem
}

PriceList::getPrice() is something like:

public static function getPrice($productCode, $purchaseType, $quantity){
    return PriceList::where('product_code', $productCode)
        ->where('purchase_type', $purchaseType)
        ->where('minimum', '>=', $quantity)
        ->where('maximum', '<=', $quantity)
        ->get()->first()->unit_price;
}

I can't seem to find a more efficient way. And when I display over 1000 products, then it gets really slow. I can't seem to find a way to use eager loading.

Aucun commentaire:

Enregistrer un commentaire