Subject: Docbot search fix

From: Petr Jelinek <[email-masked]>
Date: Fri, 16 Nov 2007 12:30:18 +0100

Hello,

there is one problem in current implementation of docbot search on pg 
website (the "Based on your search term, we recommend the following 
links" part). It only works for single words which is not how docbot is 
meant to work.
Attached patch handles multi-word search queries the way docbot does.

-- 
Regards
Petr Jelinek (PJMODOS)

Index: system/page/search.php
===================================================================
--- system/page/search.php	(revision 1813)
+++ system/page/search.php	(working copy)
@@ -105,7 +105,15 @@
 
     function search_docbot() {
         if ($this->pagenum > 1) return; // only show docbot urls on first page 
-        $qry = "SELECT url FROM docbot_keylist WHERE \"key\" = '". pg_escape_string($this->query) ."'ORDER BY url ~ 'postgresql.org' desc, url LIMIT ". $this->maxdocbots;
+        // split query to separate words
+        $keys = preg_split('/\s+/', str_replace('"', '', $this->query));
+
+        // build the keyword search sql query
+        $part = "SELECT kurl FROM docbot_key WHERE \"key\" = lower('";
+        $qry = "SELECT url FROM docbot_url WHERE id IN (\n";
+        $qry .= join("\n\tINTERSECT\n", array_map(create_function('$e', 'global $part; return $part.pg_escape_string($e)."\')";'), $keys));
+        $qry .= ")\nORDER BY url ~ 'postgresql.org' desc, url LIMIT ".$this->maxdocbots;
+
         $rs = $this->pg_query($qry,'search');
         for ($i = 0; $i < pg_num_rows($rs); $i++) {
             $r = pg_fetch_array($rs, $i, PGSQL_ASSOC);

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 24 Nov 2007 14:36:54 -0500 (EST)

Uh, did this get addressed?  If so, I didn't see it.

---------------------------------------------------------------------------

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---

From: Petr Jelinek <[email-masked]>
Date: Mon, 26 Nov 2007 15:35:03 +0100

> Uh, did this get addressed?  If so, I didn't see it.
Umm nobody applied patch nor did any other changes to the search afaics, 
so no it wasn't addressed.

-- 
Regards
Petr Jelinek (PJMODOS)

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 6 Mar 2008 16:52:21 -0500 (EST)

Has no one addressed this either?

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 6 May 2008 23:27:35 -0400 (EDT)

Based on my testing, this has still not been addressed.

Bruce Momjian wrote:
> Sent via pgsql-www mailing list ([email-masked])
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-www

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com