From 222ecc27bc86de1e74e871c14e247a11c5564d73 Mon Sep 17 00:00:00 2001 From: Michael McVady Date: Mon, 29 Jan 2024 22:05:04 -0600 Subject: Fix sorting of index --- src/queries.h | 22 +++++++++++++--------- 1 file changed, 13 insertions(+), 9 deletions(-) (limited to 'src/queries.h') diff --git a/src/queries.h b/src/queries.h index 7b5b55d..6c11317 100644 --- a/src/queries.h +++ b/src/queries.h @@ -4,19 +4,23 @@ const char *q_select_entry = "WHERE id = $1;"; const char *q_select_entries = -"SELECT id, title, created_at::DATE, updated_at::DATE, body " -"FROM entries " -"ORDER BY updated_at DESC, created_at DESC " -"LIMIT $1;"; +"WITH inner_query AS " +"( " +" SELECT id, title, created_at, updated_at, body " +" FROM entries " +" ORDER BY updated_at DESC, created_at DESC " +" LIMIT $1 " +") " +"SELECT id, title, created_at::DATE, updated_at::DATE, body FROM inner_query;"; const char *q_search_entries = "WITH search_query AS " "( " -" SELECT id, title, created_at::DATE, updated_at::DATE, body, " -" TS_RANK(search_vector, TO_TSQUERY('english', $1)) AS rank " -" FROM entries " -" WHERE search_vector @@ TO_TSQUERY('english', $2) " -" ORDER BY rank DESC " +" SELECT id, title, created_at::DATE, updated_at::DATE, body, " +" TS_RANK(search_vector, TO_TSQUERY('english', $1)) AS rank " +" FROM entries " +" WHERE search_vector @@ TO_TSQUERY('english', $2) " +" ORDER BY rank DESC " " LIMIT $3 " ") " "SELECT id, title, created_at::DATE, updated_at::DATE, body FROM search_query;"; -- cgit v1.2.3