this is part of my UserResource.php in my Laravel app that returns info for the current…
- User
this is part of my UserResource.php in my Laravel app that returns info for the current user:
$userProducts = $this->productOrders() ->join('products', 'product_orders.product_id', '=', 'products.id') ->where('product_orders.status', 'Paid') ->whereNotNull('product_orders.product_id') ->with(['product.game']); $mpUserProducts = clone $userProducts; $userProducts = $userProducts->where('products.consumable', false); $userDisabledProducts = []; foreach ($userProducts->get() as $userProduct) { if ($userProduct->not_allowed_products) { $userDisabledProducts = array_merge($userDisabledProducts, json_decode($userProduct->not_allowed_products)); } } $data['disabled_products'] = array_map('intval', array_unique($userDisabledProducts)); $purchasedProducts = $userProducts->select('product_orders.product_id')->groupBy('product_id')->get(); $data['purchased_products'] = UserPurchasedProductsResource::collection($purchasedProducts)->toArray($request); $hasRemainingProducts = $userProducts->where('products.slug', '!=', 'game1') ->where(function ($query) { $query->where('product_orders.currency', '!=', 'DC') ->orWhereNull('product_orders.currency'); }) ->exists(); $mpHasRemainingProducts = $mpUserProducts->where('products.slug', '!=', 'game1') ->where(function ($query) { $query->where('product_orders.currency', '!=', 'DC') ->orWhereNull('product_orders.currency'); }) ->exists(); $data['has_purchased_products'] = $hasRemainingProducts; $data['mp_has_purchased_products'] = $mpHasRemainingProducts;can this part be optimized?
- Assistant
Yes – the fragment is doing 5 SQL round-trips and a fair bit of PHP post-processing while it can be reduced to ONE query and pure-collection work.
The main idea is:• Pull every order you need once.
• Do the “disabled / purchased / has-remaining” calculations in PHP using the Collection helpers (they’re way faster than multiple extra queries).
• Remove theclone,groupBy, and the repeatedexists()queries.// 1. Get every paid order (and its product) only once $orders = $this->productOrders() ->select( 'product_orders.product_id', 'product_orders.currency', 'product_orders.not_allowed_products', 'products.slug', 'products.consumable' ) ->join('products', 'product_orders.product_id', '=', 'products.id') ->where('product_orders.status', 'Paid') ->whereNotNull('product_orders.product_id') ->with('product.game') ->get(); // <── ONE query // 2. Work only with non-consumables when needed $nonConsumables = $orders->where('consumable', false); /* ---------- Disabled products ---------- */ $data['disabled_products'] = $nonConsumables ->pluck('not_allowed_products') // ["[1,2]", null, ...] ->filter() // remove nulls ->flatMap(fn ($json) => json_decode($json, true)) // [1,2,3,4] ->unique() ->map(fn ($id) => (int) $id) ->values() ->all(); /* ---------- Purchased products ---------- */ $uniqueProductIds = $nonConsumables->pluck('product_id')->unique(); $data['purchased_products'] = UserPurchasedProductsResource::collection( Product::whereIn('id', $uniqueProductIds)->get() )->toArray($request); // SECOND (cheap) query // (remove if you already have the // resource data in the $orders) /* ---------- “Remaining” flags ---------- */ $remainingCheck = fn ($order) => $order->slug !== 'game1' && ($order->currency !== 'DC' || is_null($order->currency)); $data['has_purchased_products'] = $nonConsumables->firstWhere($remainingCheck) !== null; $data['mp_has_purchased_products'] = $orders->firstWhere($remainingCheck) !== null;What changed / why it’s faster
- ONE heavy query instead of ( )
•
get()for disabled list
•select … groupBy … get()for purchased list
•exists()for$hasRemainingProducts
•exists()for$mpHasRemainingProducts - No
clone/mutable query builders – you work with an in-memory Collection. - Collection helpers (
pluck,flatMap,firstWhere) are written in C and are very fast compared with repeatedly going to MySQL. - Simpler, more expressive code.
Extra polish (optional)
• Wrap the “paid non-null product orders” piece into a model scope (
scopePaid()etc.).
• Put the “not-allowed → disabled array” logic into an accessor or dedicated service.
• Add a composite index on(status, product_id)inproduct_ordersif you hit this path a lot. - ONE heavy query instead of ( )
•
- User
UserPurchasedProductsResource requires a collection of Product objects.
- Assistant
Sure – if the resource needs a collection of Product models we can reuse the products we already fetched instead of running an extra query.
Below is a version that
• hits the DB only once (for the paid orders + their product)
• does the rest in-memory with Collection helpers
• hands a Collection of unique Product models toUserPurchasedProductsResource// ------------------------------------------------------ // 1. Get every *paid* order for the user (+ its product) // ------------------------------------------------------ $orders = $this->productOrders() // relation from User model ->where('status', 'Paid') ->whereNotNull('product_id') ->with(['product' => fn ($q) => $q->with('game')]) // eager-load product & its game ->get(); // ONE query // ------------------------------------------------------ // 2. Split out the non-consumable orders once // ------------------------------------------------------ $nonConsumableOrders = $orders->filter( fn ($order) => !$order->product->consumable ); // ------------------------------------------------------ // 3. Disabled products list // ------------------------------------------------------ $data['disabled_products'] = $nonConsumableOrders ->pluck('not_allowed_products') // ["[1,2]", null, ...] ->filter() // drop nulls ->flatMap(fn ($json) => json_decode($json, true)) // [1,2,3,4] ->unique() ->map(fn ($id) => (int) $id) ->values() ->all(); // [1,2,3,4] // ------------------------------------------------------ // 4. Purchased products ➜ must be Product objects // ------------------------------------------------------ $purchasedProducts = $nonConsumableOrders ->pluck('product') // Collection of Product models ->unique('id') // de-duplicate by PK ->values(); // re-index $data['purchased_products'] = UserPurchasedProductsResource::collection($purchasedProducts) ->toArray($request); // ------------------------------------------------------ // 5. “Has remaining products” flags // ------------------------------------------------------ $remainingRule = fn ($order) => $order->product->slug !== 'game1' && ($order->currency !== 'DC' || is_null($order->currency)); $data['has_purchased_products'] = $nonConsumableOrders->contains($remainingRule); $data['mp_has_purchased_products'] = $orders->contains($remainingRule);What changed / why it’s better
- Single SQL query – all subsequent work is in PHP Collections.
- No
clone, no repeatedexists()calls. pluck('product')gives you actualProductmodels, so the resource receives exactly what it expects.- Readable, testable, and easy to extend (e.g., put the JSON-decode bit in a small helper if you feel like it).
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.