Creating new items based on sort values

Question

I have a situation where I'm building a new list based on sort values on another column.

For example table: products id, name, sort_order, expiration_date

table: lists id, product_id

Let's say I want to always have 15 products in the lists table and in order. However, products expire, and when this happens they are being removed from the lists table, then we need to add in new products based on the "next in line" by the sort_order (the last sort_order id in lists + 1, if it exists, if not, start over).

I hope this makes sense...

But my question is, is there a way to handle this all in one query?

Right now, here's how I would do it:

    Query 1: SELECT count(*), sort_order as so FROM lists ORDER BY sort_order DESC

if($count < 15){
    $difference = 15 - $count;
    for($c = $count; $c >= 1; $c -=1){
        Query 2: SELECT id FROM products WHERE sort_order = $so + 1 LIMIT 1
        if(results = 0){
             Query 3: SELECT id FROM products ORDER BY sort_order ASC LIMIT 1

             Query 4: INSERT (id) into lists

        }else{

             Query 5: INSERT (id) INTO LISTS

        }
    }
}

Just seems like a lot of queries for a fairly simple task.... Any suggestions would be huge!

Thank you!

Answer 1

Hmmmz, something like this (I'm not entirely clear on it the process, but you get the point).

  $start_sort_order = N;//whatever integer it is.


  INSERT INTO lists (id)
  SELECT id FROM products 
  ORDER BY 
     sort_order < $start_sort_order, -- sort_order > $start_order == 0 => earlier, 
                                     -- sort_order < $start_order == 1 => at the end
     sort_order                      -- afterwards standard (so a result could be 
                                     -- like : 89,90,93,96,97,99,4,5,6,7, etc...
  LIMIT 15;

Answer 2

get max_sort_order and current_sort_order

select id, if (p.sort_order > current_sort_order,
     sort_order - $current_sort_order + $max_sort_order,  
     sort_order - $current_sort_order) 'relative_sort_order' from product 

order by relative_sort_order asc 
limit 15;