Google+ Followers

zondag 30 augustus 2015

Echte wereld PostgfreSQL: sorteer-kolom updaten vanuit een array.

Om gebruikers de mogelijkheid te geven om zelf de sorteervolgorde van records te bepalen kun je een integer kolom toevoegen waar je dr volgorde in zet:

DROP TABLE IF EXISTS foo;

CREATE TABLE foo (id int, naam text, volgorde int);
INSERT INTO foo VALUES (1,'hoofdstuk 4',1),(2,'hoofdstuk 5',2),(3,'hoofdstuk 2',3),(4,'hoofdstuk 3',4),(5,'hoofdstuk 1',5);
SELECT * FROM foo ORDER BY volgorde ASC;

Als je ze nu opvraagt dan klopt er niets van de hoofdstuk volgorde:

SELECT * ROM foo ORDER BY volgorde:
id - naam - volgorde
5;"hoofdstuk 1";1
3;"hoofdstuk 2";2
4;"hoofdstuk 3";3
1;"hoofdstuk 4";4
2;"hoofdstuk 5";5

Om de volgorde aan te passen kun je iets als een drag-drop interface maken en wanneer je die data submit dan krijgt je een array met de nieuwe volgorde van de id's: "5,3,4,1,2".

En nu komt de lol: PostgreSQL kan zon string zelf'verwerken, door het met UNNEST() om te zetten naar een setje records met één integer waarde:

SELECT * FROM UNNEST(5,3,4,1,2);

Maar dan weet je alleen de id's die moeten worden aangepast, hoe weet je dat record 5 een volgorde van 1 moet krijgen? Daar is windowing voor. Met ROW_NUMBER() krijg je votweg een 1 voor het eerste resultaat van UNNEST, en 2 voor het tweede, etc:

SELECT reorder_id , ROW_NUMBER() OVER () AS nieuwe_volgorde FROM unnest(ARRAY[5,3,4,1,2])

En door dat te joinen met de foo tabel kun je alles in één query doen:
WITH reorder AS (SELECT reorder_id , ROW_NUMBER() OVER () AS new_order FROM unnest(ARRAY[5,3,4,1,2]) AS reorder_id)  UPDATE foo SET volgorde=new_order FROM reorder WHERE id=reorder_id;

Zo simpel is het met PostgreSQL.