Re: [firebird-support] Find grid page containing record

[Tim Ward t...@telensa.com [firebird-support]]

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 setys...@gmail.com [firebird-support] wrote:


Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com [firebird-support]:
> Given that a query needed to return data for a page of a grid is of 
the form

>
> SELECT FIRST 25 SKIP 
> .ID (and some other fields of human-readable data)
> FROM <  plus tables as needed for other fields in the
> SELECT, WHERE and ORDER BY clauses>
> WHERE 
> ORDER BY 
>
> how can I find out what  is for a given  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

RE: [firebird-support] Find grid page containing record

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

Granted, I had the luxury of designing my UX from scratch and then implementing 
that system-wide.

 

But yes you need to make your paging work. BTW, what front-end do you use? 
Winforms/Web?

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 07 October 2015 11:48 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Find grid page containing record

 

  

There is I'm afraid something of a difference between "the UX we might want" 
and "the UI we've got to which we're trying to add particular features".

On 07/10/2015 10:40, 'Louis van Alphen' lo...@nucleo.co.za 
<mailto:lo...@nucleo.co.za>  [firebird-support] wrote:

  

I have taken the approach that it is undesirable to present users with pages 
and pages of data and having the user have to page until he finds what he 
wants. It’s not a great UX. 

My view is to rather give the user powerful search facility to very quickly get 
to the data he wants. So I have adopted a google-like search function where, as 
the users types in a search box, the resultset is filtered according to the 
search specification. The search specification includes the following 
attributes:

- Search text (the text the user enters)

- MatchType (anywhere, exact, startswith, endswith) Exact will generate a where 
clause in the form Column=@parameter, and anywhere will generate a where clause 
like Column=%param% etc

- Case Sensitive

- MaxRows to return. This defines the max nuber of matching rows to return

- EmptySearchAction (Empty or MaxRows) this defines what is returned when the 
search field is empty. Empty means an empty resultset is returned like google. 
MaxRows means the first MaxRows are returned

- List of columns to search on

This search is done server-side by dynamically generated SQL and the result set 
is returned for display. Some searches are quite resources intensive of the 
user chooses columns and sort orders on columns without proper indexes. Most 
front-end grids incl HTML grids do support ordering client side, but if you 
want to order server side i.e. last 50 rows, then you will pay the price

But it depends on the type of UX you want for your users and the use case at 
hand. The traditional paging may be appropriate but maybe some new way as well.

From: firebird-support@yahoogroups.com 
<mailto:firebird-support@yahoogroups.com>  
[mailto:firebird-support@yahoogroups.com] 
Sent: 07 October 2015 10:37 AM
To: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> 
Subject: Re: [firebird-support] Find grid page containing record

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 setys...@gmail.com <mailto:setys...@gmail.com>   
<mailto:setys...@gmail.com> <mailto:setys...@gmail.com> [firebird-support] 
wrote:

Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com <mailto:t...@telensa.com> 
  <mailto:t...@telensa.com> <mailto:t...@telensa.com> [firebird-support]:
> Given that a query needed to return data for a page of a grid is of the form
>
> SELECT FIRST 25 SKIP 
> .ID (and some other fields of human-readable data)
> FROM <  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 
>
> how can I find out what  is for a given  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 

RE: [firebird-support] Find grid page containing record

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I have taken the approach that it is undesirable to present users with pages 
and pages of data and having the user have to page until he finds what he 
wants. It’s not a great UX. 

 

My view is to rather give the user powerful search facility to very quickly get 
to the data he wants. So I have adopted a google-like search function where, as 
the users types in a search box, the resultset is filtered according to the 
search specification. The search specification includes the following 
attributes:

-  Search text (the text the user enters)

-  MatchType (anywhere, exact, startswith, endswith) Exact will 
generate a where clause in the form Column=@parameter, and anywhere will 
generate a where clause like Column=%param% etc

-  Case Sensitive

-  MaxRows to return. This defines the max nuber of matching rows to 
return

-  EmptySearchAction (Empty or MaxRows) this defines what is returned 
when the search field is empty. Empty means an empty resultset is returned like 
google. MaxRows means the first MaxRows are returned

-  List of columns to search on

 

This search is done server-side by dynamically generated SQL and the result set 
is returned for display. Some searches are quite resources intensive of the 
user chooses columns and sort orders on columns without proper indexes. Most 
front-end grids incl HTML grids do support ordering client side, but if you 
want to order server side i.e. last 50 rows, then you will pay the price

 

But it depends on the type of UX you want for your users and the use case at 
hand. The traditional paging may be appropriate but maybe some new way as well.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 07 October 2015 10:37 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Find grid page containing record

 

  

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 setys...@gmail.com <mailto:setys...@gmail.com>  
[firebird-support] wrote:

  

Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com <mailto:t...@telensa.com> 
 [firebird-support]:
> Given that a query needed to return data for a page of a grid is of the form
>
> SELECT FIRST 25 SKIP 
> .ID (and some other fields of human-readable data)
> FROM <  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 
>
> how can I find out what  is for a given  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 ma

Re: [firebird-support] Find grid page containing record

[Tim Ward t...@telensa.com [firebird-support]]

There is I'm afraid something of a difference between "the UX we might 
want" and "the UI we've got to which we're trying to add particular 
features".


On 07/10/2015 10:40, 'Louis van Alphen' lo...@nucleo.co.za 
[firebird-support] wrote:


I have taken the approach that it is undesirable to present users with 
pages and pages of data and having the user have to page until he 
finds what he wants. It’s not a great UX.


My view is to rather give the user powerful search facility to very 
quickly get to the data he wants. So I have adopted a google-like 
search function where, as the users types in a search box, the 
resultset is filtered according to the search specification. The 
search specification includes the following attributes:


- Search text (the text the user enters)

- MatchType (anywhere, exact, startswith, endswith) Exact will 
generate a where clause in the form Column=@parameter, and anywhere 
will generate a where clause like Column=%param% etc


- Case Sensitive

- MaxRows to return. This defines the max nuber of matching rows to return

- EmptySearchAction (Empty or MaxRows) this defines what is returned 
when the search field is empty. Empty means an empty resultset is 
returned like google. MaxRows means the first MaxRows are returned


- List of columns to search on

This search is done server-side by dynamically generated SQL and the 
result set is returned for display. Some searches are quite resources 
intensive of the user chooses columns and sort orders on columns 
without proper indexes. Most front-end grids incl HTML grids do 
support ordering client side, but if you want to order server side 
i.e. last 50 rows, then you will pay the price


But it depends on the type of UX you want for your users and the use 
case at hand. The traditional paging may be appropriate but maybe some 
new way as well.


From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]

Sent: 07 October 2015 10:37 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Find grid page containing record

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 setys...@gmail.com 
<mailto:setys...@gmail.com> [firebird-support] wrote:


Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com 
<mailto:t...@telensa.com> [firebird-support]:
> Given that a query needed to return data for a page of a grid is of 
the form

>
> SELECT FIRST 25 SKIP 
> .ID (and some other fields of human-readable data)
> FROM <  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 
>
> how can I find out what  is for a given  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

Re: [firebird-support] Find grid page containing record

[setysvar setys...@gmail.com [firebird-support]]

Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com [firebird-support]:
> Given that a query needed to return data for a page of a grid is of the form
>
> SELECT FIRST 25 SKIP 
> .ID (and some other fields of human-readable data)
> FROM <  plus tables as needed for other fields in the
> SELECT, WHERE and ORDER BY clauses>
> WHERE 
> ORDER BY 
>
> how can I find out what  is for a given  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