Laravel WithSum / WithCount Relationships not bringing results

Question

I am trying to make a query using Laravel eloquent but at the moment I have not had good results.

My query is about the scope of relationships in Laravel. We have two tables:

• table 1 : orders
• table 2 : products in orders (depends on table 1)

We have a relationship in the model.

public function products()
{
    return $this->hasMany(OrderProduct::class);
}

OrderProduct (detail of products in orders) has the following fields:

• id
• order_id
• product_id
• qty
• line_total

What we are trying to achieve is a query that returns the sum of line_total when the product_id is 139.

We tried the following options without success in the controller:

1. $orderspaid = Order::with('products')
       ->where('customer_id', '=', Auth::id())
       ->where('status', '=', 'completed')
       ->withSum ('products','line_total')
       ->where('product_id', '=', '139')
       ->get();
   

   Error: Column not found: 1054 Unknown column 'product_id'

2. $orderspaid = Order::withCount(['products as orderproducts' => function($query) {
       $query->where('orderproducts.product_id', '=', 139)
             ->select(DB::raw('sum(line_total)'));
   }])->get();
   

   But with no success.

My main question is, it is possible to use sum(line_total) or withSum('products','line_total') to directly sum the amount of money that a particular product_id have?.

Additional Info: Tinker information displaying the relationship between orders and orderproducts.

Answer 1

You can try this one. I don't have those tables ready to test so I could be wrong

So basicly, the method being tried is that products with wanted id will be preloaded, in this case, it's 139. When withSum is called on products table, it will use eagerly products that have been specified beforehand.

$product_id = 139;
$orderspaid = Order::with(['products' => function ($query) use ($product_id) {
    $query->where(`products.id`, $product_id);
}])
    ->where('customer_id', '=', Auth::id())
    ->where('status', '=', 'completed')
    ->withSum('products', 'line_total')
    ->get();
dd($orderspaid);

Tell me if that works for you.