View query ordering

Hi,

I’m currently trying to implement a view in Akka Serverless where the query adds ordering.
When running the service (locally) i’m getting a parse error on the view query.

ListRequest:

message ListRequest {
  int32 start_from = 1;
  int32 page_size = 2;
  string order_by = 3;
}

The RPC function:

rpc ListUnits(api.ListRequest) returns (api.PaginatedUnitViewResponse) {
    option (akkaserverless.method).view.query = {
      query: "SELECT * AS results, has_more() AS has_next FROM units_list ORDER BY id OFFSET :start_from LIMIT :page_size"
    };
    option (google.api.http) = {
      post: "/v1/units/view"
      body: "*"
    };
  }

It starts failing to parse the query when i added the ORDER BY.
In this case i’ve put the field as a fixed value but would like to have this be dynamic via the order_by value in the ListRequest

is the ORDER BY not supported yet or am I doing something (very) wrong in this case?

Order by is not supported yet, but will be soon.

It will however not be possible to do a dynamic order by selected by a query parameter, it needs to be known up front.

HI Johan,

Thanks for th swift response.

When I would like to support ordering on multiple fields via some way of input, how would the best approach be in such a case in Akka Serverless?

Maybe some way where I could define multiple queries with fixed ordering and use the needed query based upon match/switch case

A view needs to be able to create an optimized index for the query behind the scenes, this index includes sort order, so right now I think the only option is to have multiple views (only one query per view allowed) and choosing between those different views based on sorting from the client calling the view.

1 Like

Hi Johan,

Sorry to bring this back up but I’m still wondering if there is another way as it seems very strange to create so many views just to support filtering and ordering.

As an example, for a Persistent entity that has 15 properties, and we would like to support ordering and filtering on 10 of those 15 properties.
To be able to then support the dynamic filtering and sorting on those properties we would have an exponential amount of view, so to suppor those we would have 10^10 views just to be able to support all the search/filtering and sorting use-cases.

Also then determining which view we want to use in becomes very cumbersome and at this point we lose all the speed we’ve gained from Akka SLS in creating so many query variants and selecting the correct one based upon the input in our Gateway

Thanks in advance,
Gilles

Hi Gilles,

That sounds cumbersome for sure, but I can’t see any other way to achieve that kind of dynamic sorting and filtering with what is Akka Serverless right now.

In the long run we want to be able to allow more than one query in the same view as long as all queries can be supported by the same indexing strategy behind the scenes, maybe that could also allow for more dynamic sorting and filtering but it not something we have currently looked at in more detail.