From 8daa0b56f8e53c34706870462b346749b824a9a7 Mon Sep 17 00:00:00 2001 From: unwox Date: Wed, 23 Oct 2024 23:46:42 +0600 Subject: shuffle query results so sites of the results are not grouped --- bin/serve.fnl | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) diff --git a/bin/serve.fnl b/bin/serve.fnl index 0c5ca6d..98e0aac 100644 --- a/bin/serve.fnl +++ b/bin/serve.fnl @@ -139,7 +139,7 @@ price_per AS \"price-per\", year FROM products - ORDER BY RANDOM() + ORDER BY ROW_NUMBER() over (PARTITION BY site ORDER BY ROWID) LIMIT ?" [limit]))) @@ -186,7 +186,7 @@ (table.insert where-conds "products.price <= ?")) (table.insert where-vars max-price)) - (local where-sql + (local where-sql (if (< 0 (# where-conds)) (.. "AND " (array.join where-conds "\nAND ")) "")) @@ -209,7 +209,7 @@ (luna.db.query* db (string.format - "SELECT highlight(search, 0, '', '') AS \"title\", + "SELECT products.title, products.site, products.description, products.image, @@ -225,7 +225,8 @@ LEFT JOIN product_tags ON product_tags.product = products.url WHERE search.`table` = 'products' %s - ORDER BY rank + ORDER BY ROW_NUMBER() over (PARTITION BY products.site ORDER BY products.ROWID), + rank LIMIT 48 OFFSET ?" where-sql) (array.concat where-vars [(* (- page 1) 48)]))) :total (if (< 0 (# total)) -- cgit v1.2.3