Remote Paging and Sorting using mORMot with ExtJS

warleyalex · 2013-03-26 22:29:19

Recently I was playing with mORMot and ExtJS when I came across a major block, the solution of which I now wish to share through this tip.

While working with ExtJS at client side and JSON objects returned on the server side (here mORMot), I found that all functionalities were working fine except remote filtering. It is quite well-known that mORMot supports sorting as well as paging.

In this mini-video, I've built a RESTful ExtJS CRUD grid with mORMot as REST provider.

Please note:
=========
- the docked pagination toolbar at the bottom of the screen.
- the results are sorted by any field in ascending / descending order.
- the pagination and sorting is being done remotely at server side using mORMot.
- Filtering: has been done LOCALLY at client side.
- Table SampleRecord has 52,500 records

Each time when the next page is being requested or a column is being clicked to sort the grid, it send some parameters to Server, and our server side (mORMot) code should be well enough to handle these request.

For eg:
Start Index is obtained through --> URI parameter "start";
The Result parameter is obtained through --> URI parameter "limit";
Page Number is obtained through --> URI parameter "page";
Sort Column of Grid Header is obtained through --> URI parameter "sort";
Sort Order (ascending/descending) is obtained through --> URI parameter "dir";
Filter is obtained through --> filter is an array of object

filter:[{"property":"name","value":"AName7"},{"property":"time","value":"40438"}]

It would be so cool if mORMot could handle with remote filtering!

Click here to watch the 5 minutes video
http://youtu.be/e4vcglINrko

Last edited by warleyalex (2013-04-02 03:31:27)

eraldo · 2013-03-27 01:31:45

Hi,
Could you provide the sources of this great example?

ab · 2013-03-27 07:37:25

Thanks for the input, and nice video.

It should not be difficult to let the javascript build a "where" parameter to supply at the URI level.
With inlined parameters for better performance (like 'Name=:("Smith"):' - see the SAD pdf.

Then it will perform the filtering as expected.

warleyalex · 2013-03-31 13:37:34

Playing with mORMot and ExtJS to create rich interfaces, now I've got to do an interface using remote filtering with mORMot. Remote filtering with mORMot works just fine but...

at this point I'm stuck and after searching for hours, I'm looking forward to a useful function but disappointed that this is not yet implemented by mORMOt.

In combobox a drop down list when I enter any text characters like 'b'... should display all words starting with 'b'.

So in other words I'm looking for a solution to filter the data only by the entries' first letter, but maybe by using something like in SQL ... LIKE inlined parameters

where=Name LIKE :('Arnauld'%): 
where=Name LIKE :(%'Arnauld'): 
where=Name LIKE :(%'Arnauld'%):

How can I do that? Any ideas?
Tanks a lot in advance

ab · 2013-03-31 14:02:30

You can set the WHERE parameter with such a value:

where=Name LIKE :('Arnauld%'):

Note that the % is within the string, just with usual SQL.

warleyalex · 2013-03-31 23:13:00

ab wrote:

Note that the % is within the string, just with usual SQL.

Request URL:
--------------
http://localhost:8080/root/SampleRecord/?SELECT=*&where=name%20LIKE%20:('ANGELES%'):
Request Method:GET
Status Code:400 Bad Request

QUERY STRING PARAMETERS:
------------------------------
Query String Parametersview URL encoded
SELECT:*
where:(unable to decode value) --> name%20LIKE%20:('ANGELES%'):

I think the problem is the percent ("%") character in the URI as the indicator for encoded octets.

ab · 2013-04-01 07:29:19

The parameters have to be urlencoded as with any http request.

ab · 2013-04-05 19:16:39

I've added a new sample "18 - AJAX ExtJS Grid", which is more or less the code used for the above videos.
See http://synopse.info/fossil/info/27d2daab45

I discovered one issue with the supplied javascript code: adding a new record is not working yet.

I will write some Blog article and update the documentation to introduce ExtJS Grid support.

warleyalex · 2013-04-16 21:54:13

ab wrote:

I discovered one issue with the supplied javascript code: adding a new record is not working yet.

I investigated the problem. When I perform a create operation (POST method), the proxy sends all the fields, including the empty primary ID to the server, which causes an error 400 bad request. Server expects that no primary ID is included for a create operation.

--> if ID=0, mORMot won't auto generate an ID during POST for your request (a bug?). I don't know, but basically, the server-side should ignore this ID if it's empty or ID=0 for CREATE operations).
***
An workaround for this issue is to change the structure of request on client-side, on beforerequest.
Please, make this changes:

"app/view/contact/Edit.js" - lines 67-79, remove 'Create button' and its listener then add a 'Cancel button'

{
  xtype : 'button',
  action : 'cancel',
  iconCls : 'icon-reset',
  text : 'Cancel'
},

"app/view/controller/Contacts.js" - line 82, please add:

	Ext.Ajax.on("beforerequest", function( conn, options, eOpts){
	  if (options.action=='create')
	  {
		var newData = values; 
		delete newData.ID;
		options.jsonData = newData;
	  }
	});

That's all.

Last edited by warleyalex (2013-04-16 21:58:32)

ab · 2013-04-17 05:52:22

Thanks!

Committed in http://synopse.info/fossil/info/d13d479cec

Feng · 2014-09-05 09:06:45

Would like to ask the way at Delphi client how to use the paging mechanism, such as saying at the time the call MultiFieldValues function,thx.

ab · 2014-09-05 15:54:31

Search for "paging" in the documentation ("10.2.2. RESTful mORMot" and the reference to YUI REST syntax) and the source code (TSQLRestServerURIPagingParameters).

The current implementation is not very optimized for huge set of content, since we use the LIMIT clause, which is less efficient than a WHERE orderedfield>lastretrievedfieldvalue kind of paging.
But for small sets, and with SQLite3 internal base, it works well.

Instead of paging, you may just use a "filter" pattern, and limit the number of returned rows - just like a Google search.
Or pre-download the info on client side - see "cache" in the documentation.
Or write a simple dedicated interface-based service on the server side, which would cache the data in memory to let the DB alone.

Feng · 2014-09-05 23:24:41

Thank you very much, I should go to learn more.

Feng · 2014-09-12 16:03:44

For a long time, delphi client mode currently found only own hand-coded Url parameters TSQLRestServerURIPagingParameters configuration, and then achieve the effect of paging by calling TSQLRestClientURI.URI methods. Implementation seems to feel is not correct, I do not know a simple and effective way should we do?
Just want to implement paging data acquisition and display of the UI similar dbgrid.
thanks -_-

ab · 2014-09-12 16:36:41

Please see sample "18 - AJAX ExtJS Grid".

You have custom paging defined on server via:

procedure TForm1.FormCreate(Sender: TObject);
begin
  Model := TSQLModel.Create([TSQLSampleRecord]);
  DB := TSQLRestServerDB.Create(Model,ChangeFileExt(paramstr(0),'.db3'));
  // customize RESTful URI parameters as expected by our ExtJS client 
  DB.URIPagingParameters.StartIndex := 'START=';
  DB.URIPagingParameters.Results := 'LIMIT=';
  DB.URIPagingParameters.SendTotalRowsCountFmt := ',"total":%';
  // initialize and launch the server
  DB.CreateMissingTables;
  Server := TSQLHttpServer.Create('8080',[DB],'+',useHttpApiRegisteringURI);
  Server.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
end;

This simple AJAX sample work as expected, and use the default ExtJS paging.
The SQL generated is correct, for instance (according to the logs):

20140912 18380439  +    TSQLRestServerDB(01EAB430).0049C847 
20140912 18380439 SQL   	TSQLDatabase(01F12AE0) SELECT Count(*) FROM SampleRecord;
20140912 18380439 cache 	TSQLDatabase(01F12AE0) from cache
20140912 18380439 res   	TSQLDatabase(01F12AE0) [{"Count(*)":62}] 
20140912 18380439 SQL   	TSQLDatabase(01F12AE0) SELECT ID,TimeD,Name,Question FROM SampleRecord LIMIT 10 OFFSET 50;
20140912 18380439 cache 	TSQLDatabase(01F12AE0) from cache
20140912 18380439 res   	TSQLDatabase(01F12AE0) [{"ID":51,"TimeD":"2014-05-16","Name":"Yellow Jack","Question":"Gnathanodon speciousus"},{"ID":52,"TimeD":"2014-05-26","Name":"Redtail Surfperch","Question":"Amphistichus rhodoterus"},{"ID":53,"TimeD":"2014-06-05","Name":"White Sea Bass","Question":"Atractoscion nobilis"},{"ID":54,"TimeD":"2014-06-15","Name":"Rock Greenling","Question":"Hexagrammos lagocephalus"},{"ID":55,"TimeD":"2014-06-25","Name":"Senorita","Question":"Oxyjulis californica"},{"ID":56,"TimeD":"2014-07-05","Name":"Warley Alex","Question":"
20140912 18380439 srvr  	TSQLRestServerDB(01EAB430) GET SampleRecord -> 200
20140912 18380439  -    00.013.949

Feng · 2014-09-15 02:34:44

Ajax implementations I've seen, do not know is developed in Delphi clients how to achieve pagination effect? For example, I want to get the data page of a table by calling TSQLRest.MultiFieldValues method returns only All data and interface parameters can be no return to specify a page of data.

I did not customize URIPagingParameters parameters, or is it the default values used by In the code directly call TSQLRestClientURI.URI method can achieve the effect of acquired paged data, Url parameter TSQLRestClientURI.URI is processed by hand, like this "/ Cs006 / c2105recdata /? Select = * & startindex = 0 & results = 30 & sort = RecTime & dir = desc".

In this way is not a problem, or that it is not the correct usage?
In addition, TSQLRest.MultiFieldValues can implement paging control it?

thanks

ab · 2014-09-15 07:11:50

Paging is there mainly for UI components which expect it (like the ExtJS grid).
As I stated, the simple paging using LIMIT + OFFSET clauses is inefficient for huge content.

The mORMot way is to retrieve all data, with an optional filter.
This is how TSQLRest.MultiFieldValues works.

With the filter, you can implement paging in a way much more efficient than LIMIT + OFFSET.
You need to use LIMIT with a WHERE aValue>aLastValue on an ORDERed column.
See http://stackoverflow.com/a/14468878/458259
Here, an indexed on the ORDERed column is mandatory.

Since it would depend on each table, there is no generic way of implementing it.
The current version of TSQLRest.MultiFieldValues is enough.

mORMot Open Source

#1 2013-03-26 22:29:19

Remote Paging and Sorting using mORMot with ExtJS

#2 2013-03-27 01:31:45

Re: Remote Paging and Sorting using mORMot with ExtJS

#3 2013-03-27 07:37:25

Re: Remote Paging and Sorting using mORMot with ExtJS

#4 2013-03-31 13:37:34

Re: Remote Paging and Sorting using mORMot with ExtJS

#5 2013-03-31 14:02:30

Re: Remote Paging and Sorting using mORMot with ExtJS

#6 2013-03-31 23:13:00

Re: Remote Paging and Sorting using mORMot with ExtJS

#7 2013-04-01 07:29:19

Re: Remote Paging and Sorting using mORMot with ExtJS

#8 2013-04-05 19:16:39

Re: Remote Paging and Sorting using mORMot with ExtJS

#9 2013-04-16 21:54:13

Re: Remote Paging and Sorting using mORMot with ExtJS

#10 2013-04-17 05:52:22

Re: Remote Paging and Sorting using mORMot with ExtJS

#11 2014-09-05 09:06:45

Re: Remote Paging and Sorting using mORMot with ExtJS

#12 2014-09-05 15:54:31

Re: Remote Paging and Sorting using mORMot with ExtJS

#13 2014-09-05 23:24:41

Re: Remote Paging and Sorting using mORMot with ExtJS

#14 2014-09-12 16:03:44

Re: Remote Paging and Sorting using mORMot with ExtJS

#15 2014-09-12 16:36:41

Re: Remote Paging and Sorting using mORMot with ExtJS

#16 2014-09-15 02:34:44

Re: Remote Paging and Sorting using mORMot with ExtJS

#17 2014-09-15 07:11:50

Re: Remote Paging and Sorting using mORMot with ExtJS

Board footer