How to remove primary key order from the eloquent query.

I am making a custom table for my client and grouping the results based on my needs.

    public static function getEloquentQuery(): Builder
    {
        return parent::getEloquentQuery()
            ->select(
                'destination_pattern',
                DB::raw('COUNT(*) as total_requests'),
                DB::raw('SUM(CASE WHEN duration > 1 THEN 1 ELSE 0 END) as count_duration_gt_1s'),
                DB::raw('SUM(CASE WHEN duration > 5 THEN 1 ELSE 0 END) as count_duration_gt_5s'),
                DB::raw('SUM(CASE WHEN duration > 10 THEN 1 ELSE 0 END) as count_duration_gt_10s')
            )
            ->groupBy('destination_pattern')
            ->orderBy('total_requests', 'DESC');
    }

    public static function getEloquentQuery(): Builder
    {
        return parent::getEloquentQuery()
            ->select(
                'destination_pattern',
                DB::raw('COUNT(*) as total_requests'),
                DB::raw('SUM(CASE WHEN duration > 1 THEN 1 ELSE 0 END) as count_duration_gt_1s'),
                DB::raw('SUM(CASE WHEN duration > 5 THEN 1 ELSE 0 END) as count_duration_gt_5s'),
                DB::raw('SUM(CASE WHEN duration > 10 THEN 1 ELSE 0 END) as count_duration_gt_10s')
            )
            ->groupBy('destination_pattern')
            ->orderBy('total_requests', 'DESC');
    }

Seems like the Filament automatically adds primary key ordering to the query which then causes an sql exception.

ORDER BY
  `total_requests` DESC,
  `monitors`.`id` ASC

ORDER BY
  `total_requests` DESC,
  `monitors`.`id` ASC

Is there a way to remove the default key order by from the query? Whole query:

SELECT
  `destination_pattern`,
  COUNT(*) AS total_requests,
  SUM(
    CASE
      WHEN duration > 1 THEN 1
      ELSE 0
    END
  ) AS count_duration_gt_1s,
  SUM(
    CASE
      WHEN duration > 5 THEN 1
      ELSE 0
    END
  ) AS count_duration_gt_5s,
  SUM(
    CASE
      WHEN duration > 10 THEN 1
      ELSE 0
    END
  ) AS count_duration_gt_10s
FROM
  `monitors`
GROUP BY
  `destination_pattern`
ORDER BY
  `total_requests` DESC,
  `monitors`.`id` ASC
limit
  10 OFFSET 0

SELECT
  `destination_pattern`,
  COUNT(*) AS total_requests,
  SUM(
    CASE
      WHEN duration > 1 THEN 1
      ELSE 0
    END
  ) AS count_duration_gt_1s,
  SUM(
    CASE
      WHEN duration > 5 THEN 1
      ELSE 0
    END
  ) AS count_duration_gt_5s,
  SUM(
    CASE
      WHEN duration > 10 THEN 1
      ELSE 0
    END
  ) AS count_duration_gt_10s
FROM
  `monitors`
GROUP BY
  `destination_pattern`
ORDER BY
  `total_requests` DESC,
  `monitors`.`id` ASC
limit
  10 OFFSET 0

Solution:

Turns out I am just dumb and the getEloquentQuery() method applies to all the pages and I didn't want that. So instead all I needed to do it this: ```php...

Jump to solution

1 Reply

Solution

Bonux•11mo ago

Turns out I am just dumb and the getEloquentQuery() method applies to all the pages and I didn't want that. So instead all I needed to do it this:

  return $table
            ->poll('10s')
            ->defaultSort('total_requests', 'desc')
            ->query(
                MonitorResource::getEloquentQuery()->select(
                    'destination_pattern',
                    DB::raw('COUNT(*) as total_requests'),
                    DB::raw('SUM(CASE WHEN duration < 2 THEN 1 ELSE 0 END) as count_duration_lt_2s'),
                    DB::raw('SUM(CASE WHEN duration < 5 THEN 1 ELSE 0 END) as count_duration_lt_5s'),
                    DB::raw('SUM(CASE WHEN duration < 10 THEN 1 ELSE 0 END) as count_duration_lt_10s'),
                    DB::raw('SUM(CASE WHEN duration > 10 THEN 1 ELSE 0 END) as count_duration_gt_10s'),
                    DB::raw('SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as error_count'),
                )
                    ->groupBy('destination_pattern')
            )

  return $table
            ->poll('10s')
            ->defaultSort('total_requests', 'desc')
            ->query(
                MonitorResource::getEloquentQuery()->select(
                    'destination_pattern',
                    DB::raw('COUNT(*) as total_requests'),
                    DB::raw('SUM(CASE WHEN duration < 2 THEN 1 ELSE 0 END) as count_duration_lt_2s'),
                    DB::raw('SUM(CASE WHEN duration < 5 THEN 1 ELSE 0 END) as count_duration_lt_5s'),
                    DB::raw('SUM(CASE WHEN duration < 10 THEN 1 ELSE 0 END) as count_duration_lt_10s'),
                    DB::raw('SUM(CASE WHEN duration > 10 THEN 1 ELSE 0 END) as count_duration_gt_10s'),
                    DB::raw('SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as error_count'),
                )
                    ->groupBy('destination_pattern')
            )

Set the defaultSort() to my specific key and that removes the sql problem and now the query applies only for the table view. So for now the question is resolved.

Gaming

Programming

How to remove primary key order from the eloquent query.