0

I am trying to return products in random order (or descending order) and have a button that gets more random products that are NOT part of previous random products. Basically if I have 15 products total, I want 3 random products, and then click button and get 3 more random items and so on until there are no more items.

I have a pretty whack query going on to get data from my tables.

I'm using the 'DISTINCT on' to get one product and show it's lowest price (without it, it will list the product for however many prices it has). I know that it is effecting the random ordering / descending ordering.

SELECT DISTINCT on (products.id) products.*, prices.price FROM products
INNER JOIN product_price_size ON products.id = product_price_size.productId
INNER JOIN prices ON prices.id = product_price_size.priceId
WHERE active = true AND (archived IS NULL OR archived = false)
ORDER BY products.id, prices.price LIMIT 3

The 'get more products' button is basically the same query with OFFSET being a variable after LIMIT 3

Hopefully this makes sense. Does anyone have an idea on how to achieve what I'm trying to get?

Thanks!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Keith
  • 770
  • 1
  • 6
  • 17

1 Answers1

0

You can keep incrementing the offset of a stable ordering. One ordering might be based on a deterministic hash like md5 (change some_random_salt for a new ordering):

select id from tbl
order by md5(id || 'some_random_salt'), id
limit 3 offset ?

Or the modulo of prime number (change the prime number for a different ordering):

select id from tbl
order by id % 11, id
limit 3 offset ?

Then increment the offset:

[...]
limit 3 offset 3

[...]
limit 3 offset 6
jspcal
  • 50,847
  • 7
  • 72
  • 76