Custom ordering of query results

Vision: 

There are several queries that return large number of result items. We have pagination for most of them, but have no way to customize the ordering of entries. The custom ordering would be helpful and in some cases could exclude the need to do any pagination (example: check latest transactions).

Functional details: 

Once we add something to our protocol, we must stick to it until the next major release. So the solution should be open to extension and further improvements. The proposed method consists in adding the following message to the queries protocol, that then can be used to customize the ordering of any query. Specific fields can be added to the enum when needed. Irrelevant field must cause an error. Ordering is defined as a sequence of fields and directions, in order of decreasing priority, like in SQL.

message Ordering {
  message FieldOrdering {
    enum Field {
      created_time = 0;
      hash = 1;
    }
    Field field = 1;
    enum Direction {
      ascending = 0;
      descending = 1;
    }
    Direction direction = 2;
  }
  repeated FieldOrdering sequence = 1;
}

Then we can add it to pagination messages:

 message TxPaginationMeta {
   uint32 page_size = 1;
   oneof opt_first_tx_hash {
     string first_tx_hash = 2;
   }
+  Ordering ordering = 3;
 }

Then in the query executor we add a method that takes the Ordering  value and the available fields and creates an ORDER BY  statement or an error.

Environmental objectives: None

Changelog 

Research strategy

Documentation effort

QA activity

Tasks

Template data

Priorities MUST SHOULD COULD WON'T

Change request type FEATURE BUG FIX

Status BACKLOG WORK IN PROGRESS DONE