JQGrid, Datatable or similar solution: Search, Pagination and Sort a HTML-Table

[ProfessionalIT]

Hi Friends,

I'm developing a page with a list of records in a "html-table". In
this "html-table" I need some features: search on this list,
pagination the records and sort of columns.

Searching in the web I found two very interesting solutions: JQGrid
(http://www.trirand.com/blog/) and JQuery Datatable (http://
www.datatables.net).

These two solutions are excellent and very easy to configure /
deploy when the pagination and the search is performed only at the
client-side of your app, in this case, all records are loaded in the
html-table in the first access of page, then these frameworks make all
the paging, sorting and searching in the client-side (but all records
are in this rendered page).

This behavior have a problem: the time spent to load the page when
the list has many records. I have a app made with Web2Py deployed in
AppEngine where when I access the 'Customers List Page'(that have 400
records) I wait 2 or 3 minutes to complete the 'load' of this page.

As developer, in the first moment you think: "Well, let's go
implement the pagination, sorting and searching in the 'server-side'
and in the docs page of this two solutions you have same samples with
the 'server-side processing' but this samples are in PHP and I don't
know PHP.

If anyone has a solution(or similiar solution) for this type of
problem and would like to help I would be extremely grateful.

I have developed a solution (that have same (many) bugs) without this
frameworks: http://professionalitsge.appspot.com/formas_pagamento
(need a google account to access)

This project is a small ERP made with web.py and running in GAE -
Google App Engine and in a next moment I'll share the code.

any idea or suggestion ?

Thanks for all help.

best regards,
Leandro.

[Aydın ŞEN]

I used jqGrid with webpy, it is easy to implement.

When you build jqGrid (in js) you ll define rows and pages info and when pagination links clicked jqGrid sends "row" and "post" data to your post method (which you defined). After that you just build sql something like "select first row skip page*row columns from table" and than return data.

I suggest to send data as json object. 

2010/7/15 ProfessionalIT <lsev...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "web.py" group.
To post to this group, send email to we...@googlegroups.com.
To unsubscribe from this group, send email to webpy+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/webpy?hl=en.

--
Aydın Şen

[ProfessionalIT]

Aydın ŞEN,

Can you send me a example code / project ?

Thank you very much !

Leandro.

[Aydın ŞEN]

2010/7/15 ProfessionalIT <lsev...@gmail.com>

Aydın ŞEN,

Can you send me a example code / project ?

HTML:

<table id="grid"></table>

<div id="pagergrid"></div>

JS:

            jQuery("#grid").jqGrid({

                    url:'your_data_loader_url',

                    datatype: 'json',

                    mtype: 'POST',

                    colNames:["Name", "Surname"],

                    colModel :[ {name:'name', index:'name'},

                                {name:'surname', index:'surname'}],

                    rowNum:10, // how many rows per page

                    scroll: true,

                    rowList:[10,20,30,40],

                    pager: jQuery('#pagergrid'),

                    

                    sortname: 'name',

                    viewrecords: true,

                    sortorder: "asc",

                    

                    editurl:"if the row is editable edited data will send this url",

                    caption: "Users", //Grid Name

                    height: 300,

                    width: 480

                    }).navGrid("#pagergrid", //pager options, look at the docs for details

                        {search:false,refresh:false},

                        {},//Edit Options

                        {},//Add Options

                        {}, //Del Options

                        {} //search options

                    );

WEBPY:

i = web.input(rows = "10", page ="1")

#this sql is for firebird, it can change for other db servers

sql = "select first %s skip %s id, name, surname from table" % (i['rows'], ((int(i['page']) - 1) * int(i['rows'])))

#execute sql and store it in data

d = { 'rows' : [] }

#important: data count in cell = column count in js (you have defined when you build jqGrid)

for k in data:

    d['rows'].append({'id':i[0], 'cell':[i[1],i[2]]})

return json.dumps(dict)

This is the main structure of client and server side. I hope it helps.

--
Aydın Şen

[Aydın ŞEN]

15 Temmuz 2010 17:49 tarihinde Aydın ŞEN <adige...@gmail.com> yazdı:

for k in data:

    d['rows'].append({'id':i[0], 'cell':[i[1],i[2]]})

it looks like "i" but actually it is "k" :)

--
Aydın Şen

[ProfessionalIT]

Aydın ŞEN,

I have two questions:

1 - When you click in the header of a column it re-make the query
to re-order the the results ?, because I don't look this in your code.
2 - When you make a search it re-make the query to re-order the the
results ? I don't look this too.

> *i = web.input(rows = "10", page ="1")*
> *#this sql is for firebird, it can change for other db servers*
> *sql = "select first %s skip %s id, name, surname from table" % (i['rows'],
> ((int(i['page']) - 1) * int(i['rows'])))*
> *#execute sql and store it in data*

Thank you very much.

Leandro.

[ProfessionalIT]

Aydın ŞEN,

A question:

- When you click in the a hearder column or make a search it re-
make the query to re-order the the results ? because I don't look this
in your example code.

> *i = web.input(rows = "10", page ="1")*
> *#this sql is for firebird, it can change for other db servers*
> *sql = "select first %s skip %s id, name, surname from table" % (i['rows'],
> ((int(i['page']) - 1) * int(i['rows'])))*
> *#execute sql and store it in data*
> *

Thank you very much !.

-- Leandro.

[Aydın ŞEN]

2010/7/15 ProfessionalIT <lsev...@gmail.com>

Aydın ŞEN,

 A question:

   - When you click in the a hearder column or make a search it re-
make the query to re-order the the results ? because I don't look this
in your example code.

I didn't hanle all data that sent from jqGrid, if you check documentation [1] "Loading data --> Json data" section you ll see all data received in post.

i['sidx'] #get index row - i.e. user click to sort

i['sord'] #get the direction

When user search in grid jqGrid sends searching column name and serach keyword with post data. In previous example if user search name column, you ll receive something like in your post data

i['name'] = "aydin"

after that build your sql how ever you want.

[1]: http://www.trirand.com/blog/jqgrid/jqgrid.html

--
Aydın Şen