0

I have Models Product, ProductVariant, and Department

Department:

id | name | ....

Product:

id | name | sku | department_id | ....

ProductVariant:

id | product_id | quantity | ....

And all associated with each other like:

  • Relationship products: Department hasMany Products
  • Relationship department: Product belongsTo Department
  • Relationship variants: Product hasMany ProductVariants
  • Relationship product: Product belongsTo Product

Everything works as expected between relations over Eloquent calls

Now, using Eloquent I'm trying to retrieve a collection of following columns:

product.id | product.name | product.variant_count | product.stock | department.name

By product.stock I mean: $product->variants->sum('quantity'), but I'm having hard time getting SUM inside with() method

What I've tried so far:

 $products = Product::select('id', 'name', 'sku', 'department_id') //gives product.name, sku, etc ->withCount('variants') //gives product.variants_count ->with(['variants' => function($query) { $query->select('id', 'product_id', 'quantity'); //gives variants->each.quantity }]) ->with(['department' => function($query) { $query->select('id', 'name'); //gives department.name }]);

This code gives something like this:

[ { "id": "2", "name": "Letv LU50609 Earphone - Grey", "sku": "PT-00002", "department_id": "2", "variants_count": "1", "variants": [ { "id": "2", "product_id": "2", "quantity": "35" } ], "department": { "id": "2", "name": "Phones & Tabs Accessories" } }, { "id": "3", "name": "MI In-Ear Headphones Basic 3.5mm HSEJ03JY", "sku": "PT-00003", "department_id": "2", "variants_count": "5", "variants": [ { "id": "3", "product_id": "3", "quantity": "9" }, { "id": "4", "product_id": "3", "quantity": "9" }, { "id": "5", "product_id": "3", "quantity": "10" }, { "id": "6", "product_id": "3", "quantity": "7" }, { "id": "7", "product_id": "3", "quantity": "7" } ], "department": { "id": "2", "name": "Phones & Tabs Accessories" } } ]

But what I want to achieve is:

[ { "id": "2", "name": "Letv LU50609 Earphone - Grey", "sku": "PT-00002", "variants_count": "1", "stock": "35", "department": "name": "Phones & Tabs Accessories" }, { "id": "3", "name": "MI In-Ear Headphones Basic 3.5mm HSEJ03JY", "sku": "PT-00003", "variants_count": "5", "stock": "42", "department": "name": "Phones & Tabs Accessories" } ]

How can I achieve this???

Improve this question

4 Answers 4

Reset to default
1

Option 1

You could map() the collection before return it:

 $products = Product::select('id', 'name', 'sku', 'department_id') ->withCount('variants') ->with(['variants', 'department']) ->get() ->map(function ($product){ return [ 'id' => $product->id, 'name' => $product->name, 'sku' => $product->sku, 'variants_count' => $product->variants_count, 'stock' => $product->variants->sum('quantity'), 'department' => $product->department->name ]; });

Option 2

Using API Resources. Let me know if you need help in this aspect.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

this map() method is exactly what I'm looking for .. thanks alot .. and yes get() method is required for this to work
@MuhammadShuja glad to help.
This solution will load all related models into memory and sum in-memory instead of in-database. Performance-wise, this is no good solution at all. The query builder solutions are actually better.
1

What about using the query builder something like this:

DB::table('products as product') ->select([ 'product.id', 'product.name', DB::raw('count(pv.id) as variant_count'), DB::raw('sum(pv.quantity) as stock'), 'department.name' ]) ->join('department', 'product.department_id', '=', 'department.id') ->join('product_variants as pv', 'product.id', '=', 'pv.id') ->get();

Not sure if this will work exactly like this, but it should give you a path.

Improve this answer

2 Comments

I see, but can't I achieve it by Eloquent only? tried your solution as well but having multiple issues .. may be because am not good with QueryBuilder
with eloquent will be much more complex I think, it is more readable and close to raw like this. What issues are you facing, can you share?
0

you can access your required result with this query by checking your database tables 

 SELECT products.*, SUM(variants.available) AS stock, COUNT(variants.id) as count, department.name as department FROM products LEFT JOIN variants ON products.id = variants.product_id LEFT JOIN department ON products.department_id= department.id GROUP BY products.id
Improve this answer

Comments

0

What you are looking for can be achieved in many ways. The ideal solution will build the sum within the database for best performance. To achieve so, you can use a custom query together with the Laravel query builder as already explained by @nakov or you use a little exploit of the Eloquent relationship system:

$products = Product::query() ->join('departments', 'departments.id', '=', 'products.department_id) ->withCount('variants as variant_count') ->withCount(['variants as stock' => function ($query) { $query->selectRaw('SUM(quantity)'); // this may look weird but works }) ->select([ 'products.id', 'products.name', 'departments.name as department', ]) ->get();
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.