php - select 30 random rows where sum amount = x

Question

I have a table

items
id int unsigned auto_increment primary key,
name varchar(255)
price DECIMAL(6,2)

I want to get at least 30 random items from this table where the total sum of price equals 500, what's the best approach to accomplish this?

I have seen this solution which looks have a similar issue MySQL Select 3 random rows where sum of three rows is less than value

And I am wondering if there are any other solutions that are easier to implement and/or more efficient

Answer 1

The closest answer I can provide is this

set @cnt = 0;
set @cursum = 0;
set @cntchanged = 0;
set @uqid = 1;
set @maxsumid = 1;
set @maxsum = 0;
select 
    t.id,
    t.name,
    t.cnt
from (
    select 
        id + 0 * if(@cnt = 30, (if(@cursum > @maxsum, (@maxsum := @cursum) + (@maxsumid := @uqid), 0)) + (@cnt := 0) + (@cursum := 0) + (@uqid := @uqid + 1), 0) id, 
        name,  
        @uqid uniq_id,
        @cursum := if(@cursum + price <= 500, @cursum + price + 0 * (@cntchanged := 1) + 0 * (@cnt := @cnt + 1), @cursum + 0 * (@cntchanged := 0)) as cursum, if(@cntchanged, @cnt, 0) as cnt  
    from (select id, name, price from items order by rand() limit 10000) as orig
) as t

where t.cnt > 0 and t.uniq_id = @maxsumid
;

So how it works? At first we select 10k randomly ordered rows from items. After it we sum prices of items until we reach 30 items with sum less than 500. When we find 30 items we repeat the process until we walk through all the 10k selected items. While finding these 30 items we save maximum found sum. So at the end we select 30 items with greatest sum (meaning the closest to the target 500). Not sure if that's what you originally wanted, but finding the exact sum of 500 would require too much effort on DB side.