The surprisingly difficult problem of user-defined order in SQL

Re:The surprisingly difficult problem of user-defined order ... by tester756 on Saturday January 16, 2021 @07:29AM (#25801819)

yolo hack - this way we only update 1 row, but when obtaining items, then we have to join/load entry from this table too

ItemsOrderConfiguration Table

Id | Json (varchar(MAX) or proper JSON type in Relational DBs)

1 | [

     { 
     
      "ItemId": 1, 
      
      "Order": 2
      
     },
     
     { 
     
      "ItemId": 2, 
      
      "Order": 1
      
     },
     
    ]

2 | [

     { 
     
      "ItemId": 15, 
      
      "Order": 1
      
     },
     
     { 
     
      "ItemId": 32, 
      
      "Order": 2
      
     },
     
    ]

the bad thing about this is that you lose constrains e.g that ItemId actually exists, but it shouldn't be a problem.

[ Parent | on HN ]

Re:The surprisingly difficult problem of user-defined order ... by earthboundkid on Saturday January 16, 2021 @08:54AM (#25802169)

Postgres has an array type, so you can just make an array of IDs.

[ Parent | on HN ]