planet: Add proper full-text search.

[ipfire.org.git] / webapp / backend / planet.py

diff --git a/webapp/backend/planet.py b/webapp/backend/planet.py
index 0c7565684e6f77a2330f18e95ecac8b89a162c57..47036a10fb0d4af0b766ed11c77c88e544e8c131 100644 (file)
--- a/webapp/backend/planet.py
+++ b/webapp/backend/planet.py
@@ -268,33 +268,14 @@ class Planet(Object):
                return id
 
        def search(self, what):
-               # Split tags.
-               tags = what.split()
-
-               query = "SELECT planet.* FROM planet INNER JOIN ( \
-                               SELECT post_id FROM planet_tags \
-                               INNER JOIN planet ON planet_tags.post_id = planet.id \
-                               WHERE %s GROUP BY post_id HAVING COUNT(post_id) = %%s \
-                       ) pt ON planet.id = pt.post_id ORDER BY published DESC"
-
-               args = (tags, len(tags))
-
-               clauses, args = [], tags
-               for tag in tags:
-                       clauses.append("planet_tags.tag = %s")
-               args.append(len(tags))
-
-               entries = self.db.query(query % " OR ".join(clauses), *args)
-               return [PlanetEntry(self.backend, e) for e in entries]
-
-       def search_autocomplete(self, what):
-               tags = what.split()
-               last_tag = tags.pop()
-
-               res = self.db.query("SELECT tag, COUNT(tag) AS count FROM planet_tags \
-                       WHERE tag LIKE %s GROUP BY tag ORDER BY count DESC", "%s%%" % last_tag)
-
-               if tags:
-                       return ["%s %s" % (" ".join(tags), row.tag) for row in res]
-
-               return [row.tag for row in res]
+               res = self.db.query("WITH \
+                       q      AS (SELECT plainto_tsquery(%s, %s) AS query), \
+                       ranked AS (SELECT id, query, ts_rank_cd(to_tsvector(%s, markdown), query) AS rank \
+                               FROM planet, q WHERE markdown @@ query ORDER BY rank DESC) \
+                       SELECT *, ts_headline(markup, ranked.query, 'MinWords=100, MaxWords=110') AS markup FROM planet \
+                               JOIN ranked ON planet.id = ranked.id \
+                               WHERE status = %s AND published IS NOT NULL AND published <= NOW() \
+                               ORDER BY ranked DESC LIMIT 10",
+                       "english", what, "english", "published")
+
+               return [PlanetEntry(self.backend, e) for e in res]