custom order ignores SQL ORDER BY code

Question

I'm trying to use custom_order to present an arbitrary initial order.

# GET /sequences
def index
@sequences_grid = initialize_grid(Sequence,
  enable_export_to_csv: false,
              per_page: 10,
          custom_order: {
   'sequences.username' => "CASE WHEN username LIKE '#{current_user.username}' THEN 0 ELSE 1 END, username"
                        },
                  name: 'seq_g1')
end

The idea is to have the current user's records percolate to the top of the list.

The user guide states that the "keys are fully qualified names of database columns, and values the required chunks of SQL to use in the ORDER BY clause".

The SQL code works as expected when run in a database editor but the grid doesn't show the required order.

The logs are silent on any wice_grid issue and the reported requests are:

10:10:35 web.1  |   Sequence Load (0.3ms)  SELECT distinct username FROM "sequences"  ORDER BY username asc
10:10:35 web.1  |   Sequence Load (0.3ms)  SELECT distinct classtype FROM "sequences"  ORDER BY classtype asc
10:10:35 web.1  |   Sequence Load (0.2ms)  SELECT distinct description FROM "sequences"  ORDER BY description asc
10:10:35 web.1  |   Sequence Load (0.2ms)  SELECT distinct sequencenumber FROM "sequences"  ORDER BY sequencenumber asc
10:10:35 web.1  |   Sequence Load (0.2ms)  SELECT distinct target FROM "sequences"  ORDER BY target asc
10:10:35 web.1  |   Sequence Load (0.2ms)  SELECT distinct indicator FROM "sequences"  ORDER BY indicator asc

I suspect that the SQL code may be mangled in some way but before I dig deeper I thought someone may have bumped into a similar issue?

Regards, Tom.

Answer 1

After a bit of monkeying around I found the SQL was OK but you need to specify order as well as custom_order.

The user guide provides an example that would appear therefore to be a bit misleading...

@hosts_grid = initialize_grid(Host,
  custom_order: {
 'hosts.ip_address' => 'INET_ATON(hosts.ip_address)'
                })

but the examples provide...

@status_grid1 = initialize_grid(Status,
  order: 'statuses.name',
  custom_order: {
    'statuses.name' => 'length( ? )'
  }
)

So the following did what I required...

# GET /sequences
def index
@sequences_grid = initialize_grid(Sequence,
  enable_export_to_csv: false,
              per_page: 10,
                 order: 'sequences.username',
          custom_order: { 'sequences.username' => "CASE WHEN sequences.username LIKE '#{current_user.username}' THEN 0 ELSE 1 END, username, updated_at" },
       order_direction: 'desc',  
                  name: 'seq_g1')
end

... i.e. it created a list where the records of the current user are listed first in descending order by updated_at, followed by the records of other users.