php - How to implement priorities in SQL (postgres) -

php - How to implement priorities in SQL (postgres) -

i'm writing software requires storing items in database, items need have 'priority' end with

id | name | priority --------+--------------+---------- 1 | pear | 4 2 | apple | 2 3 | orange | 1 4 | banana | 3

so now, top priority fruit orange, apple banana pear.

now, want create pear number 1 priority pear, orange, apple, banana. table like:

id | name | priority --------+--------------+---------- 1 | pear | 1 2 | apple | 3 3 | orange | 2 4 | banana | 4

whats best way accomplish php , postgres. given table not going more 12-13 items i've thought selecting entire table , rewriting priorities before updating back.

* of import *

the priorities can changed in order, priority 7 set priority 3 (thus moving below priority 3 downwards notch), , need close gap item priority 7 moved priority 3 has left in priority list.

ok, here effort maintain priorities unique , consecutive. implemented trigger+function. hard part avoid infinite recursion resulting updates within trigger. solved dirt/color flag, has placed within table. value not important; alter of it.

drop schema tmp cascade; create schema tmp ; set search_path=tmp; create table fruits ( id integer not null primary key , zname varchar not null , priority integer not null , flipflag boolean not null default false , constraint unique_priority unique (priority) deferrable deferred ); insert fruits(id,zname,priority) values (1 , 'pear' ,4) ,(2 , 'apple' ,2) ,(3 , 'orange' ,1) ,(4 , 'banana' ,3) ; create function shift_priority() returns trigger $body$ begin update fruits fr set priority = priority +1 , flipflag = not flipflag -- alternating bit protocol ;-) new.priority < old.priority , old.flipflag = new.flipflag -- redundant status , fr.priority >= new.priority , fr.priority < old.priority , fr.id <> new.id -- exlude initiating row ; update fruits fr set priority = priority -1 , flipflag = not flipflag new.priority > old.priority , old.flipflag = new.flipflag , fr.priority <= new.priority , fr.priority > old.priority , fr.id <> new.id ; homecoming new; end; $body$ language plpgsql; create trigger shift_priority after update of priority on fruits each row when (old.flipflag = new.flipflag , old.priority <> new.priority) execute procedure shift_priority() ; update fruits set priority = 1 id=1;

results:

select * fruits order id; notice: drop cascades 2 other objects detail: drop cascades table tmp.fruits drop cascades function tmp.shift_priority() drop schema create schema set notice: create table / primary key create implicit index "fruits_pkey" table "fruits" notice: create table / unique create implicit index "unique_priority" table "fruits" create table insert 0 4 create function create trigger update 1 id | zname | priority | flipflag ----+--------+----------+---------- 1 | pear | 1 | f 2 | apple | 3 | t 3 | orange | 2 | t 4 | banana | 4 | t (4 rows)

php postgresql constraints redbean candidate-key