How to form a random sample of goods with the presence of at least 1 product of a certain group?

Question

PostgreSQL

Good day.
There is a table with goods. It is necessary to choose from it 4 products randomly, but from 4 ex - at least 1 must be from a certain group of products (with charity).
What is the best way to make a query in this case?
From what comes to mind:
- a request for 3 randoms followed by a request for one (or more, depending on the number of results of the previous request) random from the desired category // 2 requests instead of one
- dancing with union // complex request logic
- stored procedures // what you really don't want
Is there anything more efficient?

Answer 1

Keep:
SELECT *
FROM public.bazaar_items
WHERE charity IS TRUE
UNION
SELECT *
FROM public.bazaar_items
OFFSET (random()* B ) LIMIT R ;
Where B is a constant, integer, 1 <= B <= MAX - R .
However, if I were you, I would not once again use the computing resources of the DBMS server by calling random () on it, but would give it to the backend to be torn to pieces by rewriting the last line of the query as:
OFFSET ( F ) LIMIT R ;
Where F-- an integer generated in the range 1 <= F <= MAX - R on the backend.
MAX -- the maximum number of items.
R -- the maximum number of output items (in your case 4).
Try to keep a middle ground in the query in terms of reading and understanding / performance, use caching. This will be effective.