HasManyThrough with SUM

Posted 2 years ago by Clement_Te

Hi,

I am trying to perform a request where I have the following models:

  • Orders (orders)
  • Sub-orders (consumer_orders) with a belongsTo relation with Orders
  • Products (products)
  • Sub-orders-Products with the quantity attribute (consumer_orders_products)
  • Categories (categories) with a hasMany relation with Products

I want to be able, from the Order model, to fetch all the unique products (present in the sub-orders) with the summed quantity for each.

The request I am expecting is something like:

select products.*, sum(quantity), categories.*
from products
join consumer_orders_products on consumer_orders_products.product_id=products.id
join consumer_orders on consumer_orders.id=consumer_orders_products.consumer_order_id
and consumer_orders.order_id = $this->id
join categories on categories.id=products.category_id 
group by consumer_orders_products.product_id

What would be the best way to perform this from the Order model with Eloquent?

I started with something like that:

$this
            ->selectRaw('products.*, categories.*, SUM(quantity) as quantity')
            ->join('consumer_orders', function ($join) use ($id) {
                $join->on('consumer_orders.order_id', '=', 'orders.id')
                    ->where('consumer_orders.order_id', $id);
            })
            ->join('consumer_orders_products', 'consumer_orders_products.consumer_order_id', '=', 'consumer_orders.id')
            ->join('products', 'consumer_orders_products.product_id', '=', 'products.id')
            ->join('categories', 'products.category_id', '=', 'categories.id')
            ->groupBy('consumer_orders_products.product_id')
            ->get();

Although, the name attribute of Categories messes with the one from Products and it gives me a Order model and I would like to be able to do something like:

foreach ($results as $result) {
    // $result->product->name
    // $result->product->category->name
    // $result->quantity
    // $result->consumer_order
}

Thank you for your help.

Kind regards,

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.