orderLogic code no longer working

[malle-pietje]

Hi, here's some custom column ordering logic that worked fine with Backpack 5 but now no longer works after the upgrade to version 6.

 'orderLogic' => function ($query, $column, $columnDirection) {
    return $query->leftJoin('sales_invoices', 'customers.id', '=', 'sales_invoices.customer_id')
        ->select('customers.*', DB::raw('SUM(sales_invoices.euro_total_price_excl_tax) as sum_sales'))
        ->groupBy('customers.id')
        ->orderBy('sum_sales', $columnDirection);
}

This now throws this error:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'

SELECT
  count(*) AS total_rows
FROM
  (
    SELECT
      *,
      SUM(sales_invoices.euro_total_price_excl_tax) AS sum_sales
    FROM
      `customers`
      LEFT JOIN `sales_invoices` ON `customers`.`id` = `sales_invoices`.`customer_id`
    GROUP BY
      `customers`.`id`
  ) AS `customers_aggregator`

Any clues on where I need to look to address this? The error just doesn't make sense to me.

[pxpm]

I am not sure how it worked on previous version, we didn't change nothing there from v5 to v6.
My guess is that the GROUP BY is breaking it, as when you group it by something, you don't have the correspondent list of rows for datatables, you will have the list inside the said "groups". So you will be feeding groups to datatable, not rows.

That's my guess, can you try it ?

Cheers

[malle-pietje]

The GROUP BY works fine in a MySQL client and produces rows and columns as you would expect. Will dive a bit further.

[malle-pietje]

The sub-query is fine when executed against the database. The outer query for total_rows breaks it.

[o15a3d4l11s2]

I think the problem is that after the JOIN you have the id column twice - once from the customers and second time from the sales_invoices. Maybe instead of selecting * just select the columns that you need in order to get sum_sales or use table aliases.

[malle-pietje]

@o15a3d4l11s2 Thanks, yes I was thinking of that as well but somehow the query is always picking up * in the SELECT.

I have now figured out a much easier, full Eloquent query, approach:

return $query->withSum('sales_invoices', 'euro_total_price_excl_tax')
    ->orderBy('sales_invoices_sum_euro_total_price_excl_tax', $columnDirection);

Works like a charm.