jQuery Grid Plugin - jqGridForum

Topic RSS

Bug for pager

23/04/2009
11:34

foo

Member

Members

Forum Posts: 5

Member Since:
23/04/2009

Offline

When changing the number of rows per page, the grid does not jump back to show a page having some rows, but becomes blank as no rows fit in the page.

Example:

With records = 143.

nPage = 1, nPerPage = 50, shows rows 1-50 (correct)

nPage = 3, nPerPage = 50, shows rows 101-143 (correct)

nPage = 3, nPerPage = 100, shows no rows (technically correct, usably incorrect)

What should have happened, IMHO:

1. When the data fetched shows that there are no rows for the requested nPage and nPerPage

, AND

2. records shows that there are rows that can be fit into a previous page

, the component should requery with either:

A) The lowest page number that ALL the records can fit into

, OR

B) The highest (next) page that can hold at least one row

You're doing a stellar job with this component, btw, and I'll nag my employer for a contribution to your continous efforts, and try and contribute with a Coldfusion custom-tag.

25/04/2009
05:15

tony

Sofia, Bulgaria

Moderator

Members

Moderators

Forum Posts: 7721

Member Since:
30/10/2007

Offline

Hello,

Will see what I can do, but I think that this logic should be implemented at server.

Regards

Tony

For professional UI suites for Java Script and PHP visit us at our commercial products site - guriddo.net - by the very same guys that created jqGrid.

25/04/2009
14:27

foo

Member

Members

Forum Posts: 5

Member Since:
23/04/2009

Offline

You are correct.

The “PAGE” returned from the server, needs to be adjusted to:

total = npages
, page = (npages >= arguments.page ? arguments.page : npages)

Also the query on the server needs to be adjusted, to either pick rows in the range of the page and rows requested, or the number of rows requested.

I'll have to think abit on how to make the rows queried for, actually line up with the pages for the requested number of rows per page, whilst keeping the query as optimal as possible.

Thanks for your help.

25/04/2009
15:31

foo

Member

Members

Forum Posts: 5

Member Since:
23/04/2009

Offline

Well that was a laugh and a half.

To do actual pagination on the database-level, as opposed to table-scan, you have to do some acrobatics.

For SQL Server:

select top (
        case
            when @total_rows % (@requested_rows * @requested_page) - @requested_rows > 0 then @total_rows % (@requested_rows * @requested_page) - @requested_rows
            else @requested_rows
        end
    ) *
from
    (
        select *
        from
            (
                select top (@requested_rows) row_number () over (ORDER BY some_column desc) as nrow
                    , other_columns
                from some_tables
            ) as sub
        where 0 = 0
            and
            (
                (
                    nrow between ((@requested_page - 1) * @requested_rows + 1)
                    and (@requested_page * @requested_rows)
                )
                or nrow < (@requested_rows * @requested_page)
            )
    ) as dub
order by nrow asc

26/04/2009
06:21

tony

Sofia, Bulgaria

Moderator

Members

Moderators

Forum Posts: 7721

Member Since:
30/10/2007

Offline

Hello,

I do not think that it is so complicated. Look at demo server.php (PHP variant)

Here I do

$page = $_REQUEST['page']; // get the requested page

$limit = $_REQUEST['rows']; // get how many rows we want to have into the grid

then get the total records

$result = mysql_query("HERE THE SQL");

$row = mysql_fetch_array($result,MYSQL_ASSOC); // fetch data

$count = $row['count']; // at end the count

if( $count >0 ) {
$total_pages = ceil($count/$limit); // how many total pages we should
} else {
$total_pages = 0;
}

And the simple logic

if ($page > $total_pages) $page=$total_pages; // HERE WE ARE

Regards

Tony

For professional UI suites for Java Script and PHP visit us at our commercial products site - guriddo.net - by the very same guys that created jqGrid.

26/04/2009
13:09

foo

Member

Members

Forum Posts: 5

Member Since:
23/04/2009

Offline

Yes, your solution fetches all rows to the application-server, then paginates them there before sending the records to the client.

I'm talking about fetching only the n records from page m, from the database-server, as quickly from page 1 as from page 10 million.

I'll explain better what I mean later.

EDIT: I had a look at it, and although MySQL has a LIMIT-clause, there is nothing which indicates to me that it doesn't scan the rows.

As described here: http://www.mysqlperformanceblo.....imization/

"Also note - number of records scanned to provide result set will be very dynamic based on particular constant and other factors.
For example for our dating example if we use only (last_online) index and look for people from USA we likely will find 10 people pretty quickly, if the country is small or simply there are few members from the country, ie Slovenia - same kind of search might need to scan 1000s times more rows to provide result set."

My work-around is to create a table holding the order-fields-for-first-row-in-each-page, and use that together with indexes.

Indexes do not alone provide an optimal solution in the case of pagination.

26/04/2009
14:56

tony

Sofia, Bulgaria

Moderator

Members

Moderators

Forum Posts: 7721

Member Since:
30/10/2007

Offline

Hello,

To continue with this

$limit = $_REQUEST['rows']; // the limit of rows

$start = $limit*$page - $limit; // the page from previous post

$SQL = "SELECT ... LIMIT ".$start." , ".$limit; 😉 - not a milion here

Hope that MSSQL have such feature

Regards

For professional UI suites for Java Script and PHP visit us at our commercial products site - guriddo.net - by the very same guys that created jqGrid.

26/04/2009
14:59

foo

Member

Members

Forum Posts: 5

Member Since:
23/04/2009

Offline

No it does not, but please see my edited post.

I'll provide a working example soon as I've finalized it.

EDIT: What I am saying is that whilst you are getting i.e. 10 records back, the SQL engine has to actually consider rows * page records in order to provide you with these 10 records.

Unless you say “give me 10 records back fullfilling my order by criteria, starting from a row which fullfills the criteria for the first row in the page that I have found and stored to help optimize this query”.

Also when I say "table" I mean a recordset returned to the application-server, which it can use to query the database, if the filter and (multicolumn) order hasn't changed.

28/04/2009
08:01

tony

Sofia, Bulgaria

Moderator

Members

Moderators

Forum Posts: 7721

Member Since:
30/10/2007

Offline

Hello,

Thanks for the link. That is the reason that I say that this should be done server side - diffrent SQL serveres - diffrent behaviours, diffrent optimizations.

Regards

Tony

For professional UI suites for Java Script and PHP visit us at our commercial products site - guriddo.net - by the very same guys that created jqGrid.