Has anybody tried to make a navigation like Google?

[Mike C. Fletcher]

Lad wrote:

>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