Building an array for a custom widget

Hello, trying for two days now to solve this.

//[Order]
- id
- payment_status
public function items()
{
  return $this->hasMany(OrderItem::class);
}
//[OrderItem]
- id
- order_id
- product_id
- quantity
- price
public function order()
{
  return $this->belongsTo(Order::class);
}
public function product()
{
  return $this->belongsTo(Product::class, 'product_id');
}
//[Product]
- id
- format_id
- theme_id 
public function items()
{
  return $this->hasMany(OrderItem::class);
}

//[Order]
- id
- payment_status
public function items()
{
  return $this->hasMany(OrderItem::class);
}
//[OrderItem]
- id
- order_id
- product_id
- quantity
- price
public function order()
{
  return $this->belongsTo(Order::class);
}
public function product()
{
  return $this->belongsTo(Product::class, 'product_id');
}
//[Product]
- id
- format_id
- theme_id 
public function items()
{
  return $this->hasMany(OrderItem::class);
}

   
 protected function getViewData(): array
    {
        $orders = Order::select('id')->where('payment_status', 1)
            ->with(['items' => function ($query) {
                $query->select('order_id', 'product_id');
                },
                'items.product' => function ($query) {
                    $query->select('format_id');
                }
            ])
            ->get()
            ->groupBy('format_id')
            ->toArray();

        return [
            'orders' => $orders
          ];
    }

   
 protected function getViewData(): array
    {
        $orders = Order::select('id')->where('payment_status', 1)
            ->with(['items' => function ($query) {
                $query->select('order_id', 'product_id');
                },
                'items.product' => function ($query) {
                    $query->select('format_id');
                }
            ])
            ->get()
            ->groupBy('format_id')
            ->toArray();

        return [
            'orders' => $orders
          ];
    }

What I need is to sum the quantity of items for orders with payment_status=1 and group them by format and build the widget array like this [format][sum(quantity)]

9 Replies

Dan Harrin•2y ago

please ask in the laravel server, since this is just a question about producing an array

PastekoOP•2y ago

ok, sorry

Dan Harrin•2y ago

no need to be sorry you will just probably get a quicker answer there

toeknee•2y ago

You'll need to use DB::Raw to sum as a select opposed to summing the whole query.

PastekoOP•2y ago

Thanks, it helped me a lot. I'm close to something. Started from OrderItem model this time.

$orders = OrderItem::selectRaw('product_id, SUM(quantity) as total_products')
            ->whereHas('order', function ($query) {
                $query->where('payment_status', 0);
            })
            ->with(['product' => function ($query) {
                $query->select('id', 'format_id');
                },
                'product.format' => function ($query) {
                    $query->select('id', 'name');
                }
            ])
            ->groupBy('product_id')
            ->get()
            ->groupBy('product.format.name')
            ->toArray();

$orders = OrderItem::selectRaw('product_id, SUM(quantity) as total_products')
            ->whereHas('order', function ($query) {
                $query->where('payment_status', 0);
            })
            ->with(['product' => function ($query) {
                $query->select('id', 'format_id');
                },
                'product.format' => function ($query) {
                    $query->select('id', 'name');
                }
            ])
            ->groupBy('product_id')
            ->get()
            ->groupBy('product.format.name')
            ->toArray();

PastekoOP•2y ago

Now I have my formats array.

PastekoOP•2y ago

Is it possible to sum(total_products) for each format (A4,A5) before the ->toArray?

toeknee•2y ago

No, you can run a map or a loop. Try:

$orders = OrderItem::selectRaw('product_id, SUM(quantity) as total_products')
            ->whereHas('order', function ($query) {
                $query->where('payment_status', 0);
            })
            ->with(['product' => function ($query) {
                $query->select('id', 'format_id');
                },
                'product.format' => function ($query) {
                    $query->select('id', 'name');
                }
            ])
            ->groupBy('product_id')
            ->get()
            ->groupBy('product.format.name');

$totalPerFormat = $orders->map(function ($formatGroup, $formatName) {
    return $formatGroup->sum('total_products');
})->toArray();

$orders = OrderItem::selectRaw('product_id, SUM(quantity) as total_products')
            ->whereHas('order', function ($query) {
                $query->where('payment_status', 0);
            })
            ->with(['product' => function ($query) {
                $query->select('id', 'format_id');
                },
                'product.format' => function ($query) {
                    $query->select('id', 'name');
                }
            ])
            ->groupBy('product_id')
            ->get()
            ->groupBy('product.format.name');

$totalPerFormat = $orders->map(function ($formatGroup, $formatName) {
    return $formatGroup->sum('total_products');
})->toArray();

PastekoOP•2y ago

Thanks, I'll try that later, for now I have used array_sum(array_column($order_f, 'total_products')) in blade.

Gaming

Programming

Building an array for a custom widget