Using sum() in selectSub

Hey!

I have a rather large SQL query. A part of it is as follows:

InvoiceRow::toBase()
     ->select('user.id', 'user.username', 'warehouse.id as warehouse_id', 'warehouse.title')
     ->selectRaw("SUM((
             SELECT
                 row_net FROM invoice_row AS sub_row
             WHERE
                 invoice_row.id = sub_row.id
                 AND invoice_date BETWEEN ?
                 AND ? + INTERVAL 10 DAY)) AS first", [$dateRange->startDate, $dateRange->startDate])
    ->leftJoin('warehouse', 'warehouse.id', '=', 'sales_invoice.warehouse_id')
    ->leftJoin('product', 'product.id', '=', 'sales_invoice_row.product_id')
    ->leftJoin('user', 'user.id', '=', 'sales_invoice.sales_agent_id')
    ->get();

The question is. Is it possible to use selectSub() to refactor this code? The problem i have is that the raw query is wrapped in SUM() statement and i can't find anything in the Laravel docs or API docs to help me with this. Of course this is just an illustrated part of the query.

tisuchi

3 years ago

Level 70

@rrrrando Have you tried this?

 ->selectSub(function ($query) use ($dateRange) {
         $query->from('invoice_row AS sub_row')
             ->whereColumn('invoice_row.id', 'sub_row.id')
             ->whereBetween('invoice_date', [$dateRange->startDate, $dateRange->startDate->copy()->addDays(10)])
             ->sum('sub_row.row_net');
     }, 'first')

But, keep it mind that in some cases, the selectSub method may not provide the desired level of control over the subquery's behavior, or it may not be possible to achieve the desired query logic using the selectSub method alone. In such cases, using DB::raw can be a useful alternative as it allows you to specify the subquery as a raw expression, giving you full control over its behavior.

1 like

rrrrando

3 years ago

Level 10

@tisuchi thanks for the answer, but this doesn't also work. The SUM() part get lost. I think it's because sum() in Laravel also get the result from DB. Looks like the best way is to keep it in raw.

Larry the AI also proposed something like this:

 ->selectSub(function ($query) use ($dateRange) {
         $query->selectRaw('SUM("sub_row.row_net")')
             ->from('invoice_row AS sub_row')
             ->whereColumn('invoice_row.id', 'sub_row.id')
             ->whereBetween('invoice_date', [$dateRange->startDate, $dateRange->startDate->copy()->addDays(10)])
     }, 'first')

But that version only sums one line from the result.

Please or to participate in this conversation.