Filtering, sorting and paginating data

jvillasantegomez · 2014-01-23 17:02:29

Hello all

Working with mormot i want to filter my data on the server, I'm not using the ORM or anything however, the sql stuff it's not the problem, the problem is that i don't find an easy way to pass to an Interface service this data structure por sorting, filtering and pagination:

Here's the JSONi want to pass to mormot from the client

{
  // this is for pagination
  "startrow": 0,
  "endrow": 10,
  // this is for sorting
  "sortcolumn": "somecolumn",
  "sortorder": "asc",
  // this is for filtering (it is an array of objects)
  [
    {"property": "someProperty", "operator": "LIKE", "value": "someValue"},
    {"property": "someProperty", "operator": "<=", "value": "someValue"}
  ]
}

I want to pass this json to an interface service and have the json decoded on the server. Does anyone knows how to do this?

It could be a better JSON like this, but I suppose that mormot would have a hard time parsing it:

{
  "pagination": {
    "startrow": 0,
    "endrow": 10,
  },
  "sorting": {
    "sortcolumn": "somecolumn",
    "sortorder": "asc",
  },
  "filtering": [
    {"property": "someProperty", "operator": "LIKE", "value": "someValue"},
    {"property": "someProperty", "operator": "<=", "value": "someValue"}
  ]
}

And then I want an easy way to parse that and get access to the parsed JSON from delphi

Regards...

Last edited by jvillasantegomez (2014-01-23 19:20:24)

ab · 2014-01-23 20:31:36

I suppose the easiest is to use a record variable.
See http://blog.synopse.info/post/2014/01/0 … d-services

Ensure you got the latest unstable version, to have this feature and all fixes.

jvillasantegomez · 2014-01-23 20:40:45

It seems like a good addition, but I don't really want to pass my filter params on the url. We're writing big services here and the url scheme will allways have the limitation of the number of characters you can send. This is, after all, an internal api so we can get away with passing the parameters with POST. But, how do I deserialize that JSON (passed as a POST parameter) on the server side?

JSONDecode, JSONToObject, any other way?

I think that something like this would be very much appreciated:
https://github.com/thomaserlang/delphi-json

best regards and thks...

Last edited by jvillasantegomez (2014-01-23 20:58:27)

ab · 2014-01-23 21:26:02

Oups...
I posted the wrong link...
http://blog.synopse.info/post/2013/12/1 … ialization

You can define your record like such:

TMyRecord = packed record
  pagination: record startrow, endrow: integer; end;
  sorting: record sortcolumn, sortorder: RawUTF8; end;
  filtering: array of record property, operator, value: RawUTF8; end;
end;

_TMyRecord = 'pagination{startrow,endrow integer} sorting{sortcolumn,sortorder RawUTF8} filtering[property,operator,value RawUTF8]';

And note that we just added a new variant-based feature in SynCommons.pas.
It is able to store any document (i.e. a tree of objects) into a variant, then serialize/unserialize it with JSON.
Or BSON - thanks to the new SynMongoDB unit.
The JSON syntax is also enhanced, with MongoDB extensions - see http://docs.mongodb.org/manual/referenc … nded-json/

Take a look at TTestLowLevelTypes._TDocVariant and TTestLowLevelTypes._BSON tests methods in SynSelfTests.pas.
(ensure you got the latest version)

This is much more advanced than any JSON library around.
And I suspect with the best speed of all.

Those TDocVariant should pass over interface based services, or you can use method-based service to send the JSON content directly.

jvillasantegomez · 2014-01-24 14:56:11

It works great... Thks for the quick response.

One question thought. Do you think that records are better/faster than TCollectionItem/TInterfacedCollection for deserializing JSON objects?

Best regards...

ab · 2014-01-24 15:12:56

From my experiment, records and dynamic arrays are much easier to define and work with than regular collections.
Much less code to write.

If you need some additional features to your dynamic array, take a look at our TDynArray wrapper (in SynCommons.pas), which add a lot of methods to access any dynamic array.

mORMot Open Source

#1 2014-01-23 17:02:29

Filtering, sorting and paginating data

#2 2014-01-23 20:31:36

Re: Filtering, sorting and paginating data

#3 2014-01-23 20:40:45

Re: Filtering, sorting and paginating data

#4 2014-01-23 21:26:02

Re: Filtering, sorting and paginating data

#5 2014-01-24 14:56:11

Re: Filtering, sorting and paginating data

#6 2014-01-24 15:12:56

Re: Filtering, sorting and paginating data

Board footer