The db_table_browse model is used to generate a browse/query page to show a set of SQL database table rows and to allow the user to position the browse results to a desired starting value. This model allows you to create an efficient browse screen for almost any table without writing any code by hand.
Pagination Overview
Pagination is a very common requirement when performing SQL queries. That is, rather than showing all available records, it is common to show a "page" of rows and allow the user to navigate to the next page or one of a number of subsequent pages. Here we see a typical set of pagination links offered by Google:
One common technique for implementing pagination of SQL query results is to use the database's LIMIT and OFFSET keywords. While these options get the job done, they do so in a very inefficient way since databases are forced to read all of the records that as skipped by the OFFSET parameter just to dismiss them. As the user scrolls farther and farther, more and more rows need to be read (and re-read) and discarded.
In reality, the very usefulness of these links is questionable in the sense that users are very unlikely to want to just to page 5 say, while skipping over pages 2 to 4. Since there is no context as to what might be on page 5 relative to the other pages there is really no benefit to jumping to a particular page.
The db_table_browse model takes a different approach to pagination whereby users are only allowed to advance to the next logical page or go back to a previous page. This pagination is done without using the SQL OFFSET statement. Instead, whenever a page of rows is fetched from the database, the db_table_browse object always fetches one extra row that is not shown to the user. If the user requests the next set of rows, a new query is built with an optimal SELECT statement designed to only fetch the rows that are needed for the next page (plus one extra row in case the user want to advance the page again).
Remote vs. Local Pagination
The generated db_table_browse objects offer two separate but complementary pagination features.
- Database Fetch Limit
- Local View Limit
Consider the following browse results:
Notice the Row Limit in the first field has been set to 50. This says, "Fetch 50 rows from the database at a time". Further down the page we see that the Show value is set to 10. This says, "Show 10 rows of the results at a time". The numbers along the bottom are used to navigate between the first 10 rows, rows 11 - 20, 21 - 30, etc.. This navigation is handled locally, that is within the browser. The Search box can also be used to search for values within the set of 50 rows that are available locally.
Keys vs. Filters
Generated browse pages can have a combination of key fields and filter fields. Key fields are used to "jump" to a specific row within the database while filter fields are used to accept only rows that match a certain criteria. Consider the following Actor Browse form:
We can tell from the key icon after the Last Name field that this is a key field. Since First Name does not have a key icon beside it we know that this is a filter field. Let's look at how these values determine the search results.
Entering Key Field Values
Suppose the user enters "smith" into the last name field and clicks the single array button which is used to fetch a set of rows. Here are the results of this query:
Notice that the first row returned is for last name "Sobiesky". This tells us that the database does not contain anyone with last name "Smith". Since Last Name is a key field this is sort of like opening a dictionary and looking for a particular word. If that word is not found you would just see the next word beyond the spelling of the word you were seeking. Notice that the button to the right of the Fetch button is active. This tells the user that there are rows beyond what is shown and they can click this button to fetch the next set of rows.
By default, Key fields also support wildcard characters. Here the user is asking for all Last Names that begin with the letters "st".
Notice that the Next button is disabled since there are only 3 rows that satisfy the search so Next is not applicable. Here are examples of the various wildcard options that the user could enter:
=streep - Key is exactly equal to streep
>streep - Key is greater than streep (alphabitically)
!streep - Key is not equal to streep
steep* - Key begins with the letters streep
jones,smith,walker - Comma separated values can be used to provide a list of values to be matched
smith:smythe - Colon is used to provide a range of value to be matched
Compound Keys
There can be multiple key fields however it is important to understand that components of a compound key definition are not treated independently. Instead, all key components are processed as though they make up one long combined key value. For example, consider the following browse which has keys Last Name + First Name:
In this example the user has started browsing by Last Name value "Williams". Since nothing was entered into the first name, this part of the key defaults to blank. Here there are only a handful of Williams rows but imagine there were thousands of such rows. Since First Name is also part of the key, this allows the user to "jump" to a specific First Name within the specified Last Name. Here we see that the user asked to start browsing from Last Name + First Name = "williams" + "nancy"
Notice that, since the database does not include anyone with the name Nancy Williams, the first row shown is for the next higher combined key which is for Randy Williams. Also notice that rows are always ordered according to the key columns. In this case, the rows are returned in Last Name sequence and for rows with the same Last Name they are further sorted by First Name.
Consider the following results wherein the user has left the last_name field empty while specifying a first_name value.
Notice that the browse did not position to "Jessica" as they might have expected. Once again, this is because keys are always combined to form a compounds starting value. In this case, the empty last_name value is added to the front of the supplied first_name and it is the last_name that influences the positioning so we start at last name Akroyd and the value that the user entered into the first_name field really has no effect on positioning the screen.
The default behaviour of treating compound keys as one long combined key field is altered when wildcards are detected in some of the key fields. Here we see the user has used a wildcard character in the first_name field. In this case, the browse will show all rows containing first name "jessica".
One final note related to compound keys. These may or not be defined as compound keys within the database. The browse pages may respond faster if the database defines compound keys that correspond to compound keys used in your browse programs but this is not necessary. Similarly, the individual sort and filter fields used within your browse programs do not need to be actual keys in the database but making them keys would most likely improve performance.
Entering Filter Values
Unlike key fields which reflect starting values by default, filter fields are assumed to be exact match fields by default. Consider the following browse output:
We know that the First Name field is not a key field because there is no Key icon next to it. No starting value has been provided for the Last Name which is a key so the browse will start from the first available Last Name. Since First Name is a filter field, the results only show rows with First Name exactly matching the value Christian. Similar to key fields, wildcards can also be entered for filter fields to show a range of matching values. The main difference is that we don't need to use the = sign for exact matches since this is the default behaviour for filter fields.
