FilamentF
Filament•2y ago
Bonux

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');
    }


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


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
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: 

  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.
Jump to solution
Was this page helpful?