Limit returned results

Limit the number of results returned to make page more
responsive. This is buggy at this point — it doesn't do exactly what I
expect, e.g. when I attempt to scroll, or use pagination, it doesn't
actually display all the pages as expected.

3 files changed, 25 insertions, 8 deletions

diff --git a/uploader/publications/models.py b/uploader/publications/models.py
index 992ebfa..f4e8d33 100644
--- a/uploader/publications/models.py
+++ b/uploader/publications/models.py
@@ -71,16 +71,23 @@ def update_publications(conn: Connection , publications: tuple[dict, ...]) -> tu
 
 def fetch_publications(
         conn: Connection,
-        search: Optional[str] = None
+        search: Optional[str] = None,
+        offset: int = 0,
+        limit: int = -1
 ) -> Iterable[dict]:
     """Fetch publications from the database."""
     _query = "SELECT * FROM Publication"
     _params = None
     if search is not None and bool(search):
         _query = (f"{_query} "
-                  "WHERE CONCAT(PubMed_ID, ' ', Authors, ' ', Title) "
-                  "LIKE %s")
-        _params = (f"%{search}%",)
+                  "WHERE PubMed_ID LIKE %s "
+                  "OR Authors LIKE %s "
+                  "OR Title LIKE %s")
+        _params = (f"%{search}%",) * 3
+
+    if limit > 0:
+        _query = (f"{_query} LIMIT {limit} OFFSET {offset}")
+
     with conn.cursor(cursorclass=DictCursor) as cursor:
         cursor.execute(_query, _params)
         debug_query(_query, logger)
diff --git a/uploader/publications/views.py b/uploader/publications/views.py
index 63acf1b..8a65ff2 100644
--- a/uploader/publications/views.py
+++ b/uploader/publications/views.py
@@ -38,6 +38,10 @@ def index():
 def list_publications():
     # request breakdown:
     # https://datatables.net/manual/server-side
+    _page = int(request.args.get("draw"))
+    _length = int(request.args.get("length") or '-1')
+    _start = int(request.args.get("start") or '0')
+    _search = request.args["search[value]"]
     with (database_connection(app.config["SQL_URI"]) as conn,
           conn.cursor(cursorclass=DictCursor) as cursor):
         cursor.execute("SELECT COUNT(*) FROM Publication")
@@ -46,12 +50,17 @@ def list_publications():
             **row, "index": idx
         } for idx,row in enumerate(
             fetch_publications(
-                conn, request.args["search[value]"]), start=1))
+                conn,
+                _search,
+                offset=_length * (_page-1),
+                limit=_length),
+            start=(_start + 1)))
 
         return json.dumps({
-            "draw": request.args.get("draw"),
+            "draw": _page,
             "recordsTotal": _totalrows,
-            "recordsFiltered": len(_publications),
+            "recordsFiltered": (
+                len(_publications) if bool(_search) else _totalrows),
             "publications": _publications,
             "status": "success"
         })
diff --git a/uploader/templates/phenotypes/add-phenotypes-base.html b/uploader/templates/phenotypes/add-phenotypes-base.html
index 928fb84..01cd0fe 100644
--- a/uploader/templates/phenotypes/add-phenotypes-base.html
+++ b/uploader/templates/phenotypes/add-phenotypes-base.html
@@ -135,9 +135,10 @@
                   dataSrc: "publications"
               },
               select: "single",
+              paging: true,
               scrollY: 700,
-              paging: false,
               deferRender: true,
+              scroller: true,
               layout: {
                   topStart: "info",
                   topEnd: "search"