diff options
| -rw-r--r-- | bin/serve.fnl | 69 |
1 files changed, 36 insertions, 33 deletions
diff --git a/bin/serve.fnl b/bin/serve.fnl index b764338..67359fd 100644 --- a/bin/serve.fnl +++ b/bin/serve.fnl @@ -222,13 +222,14 @@ (local where-conds []) (local sort-criteria []) (local query-args []) + (local has-search-query? (and query (< 0 (# query)))) ;; create WHERE clause from dynamic form parameters (when (< 0 (# tags)) (each [_ tag (pairs tags)] (table.insert where-conds "product_tags.tag = ?") (table.insert query-args tag))) - (when (and query (< 0 (# query))) + (when has-search-query? (table.insert where-conds "search.title MATCH ?") (table.insert query-args (synonyms.replace query))) (when (not (libstr.empty? site)) @@ -276,6 +277,11 @@ (.. (array.join sort-criteria ",\n") ",") "")) + (local from-sql + (if has-search-query? + "search INNER JOIN products ON search.fid = products.url" + "products")) + (local total (must (luna.db.query @@ -284,44 +290,41 @@ "SELECT count(ROWID) FROM ( SELECT products.ROWID - FROM search - INNER JOIN products ON search.fid = products.url - LEFT JOIN product_tags ON product_tags.product = search.fid - WHERE search.`table` = 'products' - AND products.archived = false + FROM %s + LEFT JOIN product_tags ON product_tags.product = products.url + WHERE products.archived = false %s - GROUP BY products.url)" where-sql) + GROUP BY products.url)" from-sql where-sql) query-args))) {:results (must (luna.db.query-assoc - db - (string.format - "SELECT products.title, - products.site, - products.description, - products.image, - products.url, - products.price, - products.weight, - products.price_per AS \"price-per\" - FROM search - INNER JOIN products ON search.fid = products.url - LEFT JOIN product_tags ON product_tags.product = products.url - LEFT JOIN permanent_products ON permanent_products.url = products.url - WHERE search.`table` = 'products' - AND products.archived = false - %s - GROUP BY products.url - ORDER BY %s - ROW_NUMBER() OVER - (PARTITION BY products.site - ORDER BY permanent_products.creation_time DESC, - products.title ASC), - rank - LIMIT 24 OFFSET ?" where-sql sort-sql) - (array.concat query-args [(* (- page 1) 24)]))) + db + (string.format + "SELECT products.title, + products.site, + products.description, + products.image, + products.url, + products.price, + products.weight, + products.price_per AS \"price-per\" + FROM %s + LEFT JOIN product_tags ON product_tags.product = products.url + LEFT JOIN permanent_products ON permanent_products.url = products.url + WHERE products.archived = false + %s + GROUP BY products.url + ORDER BY %s + ROW_NUMBER() OVER + (PARTITION BY products.site + ORDER BY permanent_products.creation_time DESC, + products.title ASC) + %s + LIMIT 24 OFFSET ?" + from-sql where-sql sort-sql (if has-search-query? ", rank" "")) + (array.concat query-args [(* (- page 1) 24)]))) :total (if (< 0 (# total)) (. total 1 1) 0)}) |