Subject Re: [firebird-support] Find grid page containing record
Author Tim Ward
Post date 2015-10-07T08:36:50Z
Thanks, hadn't thought of the COUNT(*). This still means visiting every record of course, but at least on a good day most of them are being done entirely within the database engine. On a bad day however this might not gain anything if the user chooses to sort by something useless and unselective (you might say they then deserve the delay they get, but we're trying not to build too many more of these into the system).

Plus autogenerating the code for the comparison could be "interesting" particularly in cases where some of the fields in the ORDER BY (under the control of the user, remember) are ASC and some DESC?

I think we'll have to continue to think about it!

On 06/10/2015 21:50, setysvar setysvar@... [firebird-support] wrote:
 

Den 06.10.2015 17:38, skrev Tim Ward tdw@... [firebird-support]:
> Given that a query needed to return data for a page of a grid is of the form
>
> SELECT FIRST 25 SKIP <some multiple of 25>
> <some table>.ID (and some other fields of human-readable data)
> FROM < <some table> plus tables as needed for other fields in the
> SELECT, WHERE and ORDER BY clauses>
> WHERE <something, may be complex expressions involving several fields>
> ORDER BY <something else, may be several fields>
>
> how can I find out what <some multiple of 25> is for a given <some
> table>.ID?
>
> (Without using any features that are only in Firebird 3, which are
> needed for the solutions I've found so far. Surely this is not a rare
> thing to want to be able to do?)
>
> Scenario: Data is displayed in a grid in the user interface, with 25
> records per page. The user gets to specify the filters (WHERE) and
> sorting (ORDER BY) in the user interface, ie these change outside my
> control (and in consequence appropriate tables get pulled into the FROM
> clause as necessary by the query generation code).
>
> The wanted operation is that the user can say "show me the record with
> ID such-and-such" (by doing something in some other part of the UI), and
> the grid will display the correct page of data (and then scroll as
> necessary and highlight the wanted record, which obviously we'll have to
> do in the UI). In, that is, a sane amount of time - fetching hundreds of
> pages of data to the grid sequentially until the right record appears is
> not a reasonable solution! ID may or may not be the primary key, but we
> can use an indexed unique field if that helps.

I think this requires several steps.

1) Fetch the ORDER BY fields of the record of interest
2) SELECT COUNT(*) where fields < ORDER BY fields of the record of interest (or > if ORDER BY ... DESC)
(this gets you the number of records you can safely skip)
3) Fetch one or more pages of data until the right record appears

This somewhat tedious process is required since your ORDER BY may not start with your ID field. 3 is still required since you have limited control of the ORDER BY chosen by the user and cannot guarantee how many duplicates there may be (ORDER BY SURNAME may not find Tim Ward on the first page of the Ward's).

Sorry for not knowing of any quicker and simpler solution. Unfortunately, the desire of a flexible and powerful interface often means that the developer has to do a fair bit of coding.

HTH,
Set



-- 
Tim Ward