Lad said:
Hi,
I would like to make in my web application a similar navigation like
Google uses, I mean at the bottom of each page there are numbers of
returned pages after you search query.
Has anyone tried that? Is there an algorithm for that? I do not want to
re-invent the wheel.
Thank you for help
La.
First the assumptions:
* We'll assume you're using SQL queries against a database
* You don't want to have to produce the whole result-set across the
database interface, but you don't mind producing them on the
database side
* You want precise values (Google's aren't precise measures of
number of records)
o If you don't, you can use whatever estimation mechanism you
like to get the total instead of a second query
* You have a complete ordering of records (if not then your results
will randomly shuffle themselves as the user pages through)
Given that:
* You need to construct a query that produces (just) a count of all
records in the set
* You then need a query that is parameterised to return a subset of
the records in the set
o offset -- count of records from the start of the set
o limit -- number of records to display on any given page
Now, when you want to retrieve the records:
def search( self ):
"""Perform the search, retrieve records and total"""
self.records = self.SEARCH_QUERY(
connection,
whatToSelect = self.whatToSelectSubset,
limits = self.calculateLimits(),
orders = self.calculateOrders(),
wheres = self.calculateWheres(),
**self.queryParameters
)
for record in self.SEARCH_QUERY(
connection,
whatToSelect = self.whatToSelectCount,
limits = "",
orders = "",
wheres = self.calculateWheres(),
**self.queryParameters
):
self.total = record[0]
In that code the SEARCH_QUERY is a PyTable SQLQuery object, it just
takes care of the cross-database substitution operations. The code to
handle the whatToSelect determination looks like this (by default):
keyColumnName = common.StringProperty(
"keyColumnName", """Column name used to count total number of
columns""",
#define keyColumnName on Search!,
)
whatToSelectSubset = common.StringProperty(
"whatToSelectSubset", """What to select in the subset-of-records
query""",
defaultValue = "*",
)
whatToSelectCount = common.StringProperty(
"whatToSelectCount", """What to select in the count-of-records
query""",
defaultFunction = lambda prop,client: """COUNT(DISTINCT( %s
))"""%(client.keyColumnName),
)
the wheres are the natural WHERE clauses you want to apply to the query
(i.e. the search terms). The orders are normally a default set of
fields with the ability for the user to move any field to the front of
the set via UI interactions. The "limits" are actually both limits and
orders in this example, since they are tied together as the paging
functionality:
def calculateLimits( self ):
"""Calculate the limit/offset clauses for a select"""
return """LIMIT %s OFFSET %s"""%( self.limit, self.offset )
Just for completeness, here's an example of a SEARCH_QUERY:
SEARCH_QUERY = sqlquery.SQLQuery("""SELECT
%(whatToSelect)s
FROM
voip.voicemail JOIN voip.voipfeature USING (voipfeature_id)
JOIN voip.voipaccount USING (voipaccount_id)
%(wheres)s
%(orders)s
%(limits)s
""")
The UI then offers the user the ability to increase offset (page
forward), decrease offset (page backward), and re-sort (change the
ordering fields). You disable the paging if you've reached either end
of the record-set (offset<0 offset >= total-1), obviously.
Anyway, hope that helps,
Mike
--
________________________________________________
Mike C. Fletcher
Designer, VR Plumber, Coder
http://www.vrplumber.com
http://blog.vrplumber.com