The Категория label is associated with the One-to-Many label. Is it possible to customize the order of delivery of goods without adding an additional field (for example, the rate field and then order by rate )?

Let me explain why I don’t like to add another field: if we already have 100 products with a rate from 1 to 100, if we need to add a product exactly in the middle, we need to rewrite the rate for half of the products.

    1 answer 1

    Miracles do not happen. order by need to specify some sort of field. So without it in any way. But no one forces to number 1,2,3 .... You can initially number for example 1,101,201 ... and when inserting into the middle of the record, look for the average value. those. at the rate after the 2nd entry give 150.

    A little 100, take the 10,000 initial step, for example. In any case, it will be necessary to provide for a procedure for complete renumbering of the table, which is to be launched in case there is still no gap for the new record. But in 10k increments, its launch will be very rarely needed.

    Those. The algorithm for issuing a new rate is as follows:

    • If a record is added after the last existing one, we take the rate of the last record +10000

    • In any other case, take the arithmetic average between the rate of records before and after.

    • If all the same there is no place - start renumbering

    • When deleting an entry from some position, the rate of other entries is not changed at all.

    By the way, renumbering is performed in one request (Example for MySQL):

     update table set rate=(select @i:=@i+10000) where (select @i:=1)=1 order by rate

    Renumbering for postgresql:

     update table O set rate=N.new from ( select id, (row_number() over(order by rate))*10000 new from table ) N where N.id=O.id

    If you need numbering within any groups, add partition by to the over() phrase

    • Why should he add goods to the middle? well, just in the business domain logic, what does he want to achieve? - strangeqargo 10:46 pm
    • @strangeqargo He asked " Can I customize " - it means his user should be able to decide for himself at what place of sorting the product should be. Such a requirement is quite common - Mike
    • I see this as a perversion. - strangeqargo
    • @strangeqargo Well, is it to some marketer who can tell me who decides that some product was stale and should be promoted by showing first positions - Mike
    • one
      @strangeqargo By the way, the question never says what is for the site. (In principle, the same seems to me so, but I would not focus on this). Maybe this is generally for some kind of output form in the NOT web system, for the authorities who want to see individual positions at once :) The algorithm is certainly possible. But customers receiving the product are not always comfortable. Maybe they want it themselves, without the intervention of the programmer. - Mike