The problem
On some project I inserted a new column called position so the entries are orderable by the users. On this project users can create categories which have e.g. a title and may have a super/parent category and are owned by the user who created them.
The problem was that I wanted to have an initial alphabetical ordering of the categories and some rules had to apply. For the sorting only categories of the same user are considered and only categories that have a common super category.
The table looked similar to the following, however note that I have left out some columns and changed the data types (e.g. int as id instead of uuid) to make it more redeable.
| id | title | … | super_category_id | user_ref | position |
|---|---|---|---|---|---|
| 1 | A | NULL | user_1 | 0 | |
| 2 | C | NULL | user_2 | 0 | |
| 3 | B | NULL | user_2 | 0 | |
| 4 | BB | 1 | user_1 | 0 | |
| 5 | AA | 1 | user_1 | 0 | |
| 6 | CC | 2 | user_2 | 0 |
The solution
1
2
3
4
update categories
set position=r.row_id
from (select c.id, row_number() over (partition by c.super_category_id, c.user_ref order by c.title asc) as row_id from categories c) as r
where categories.id = r.id;
This sql query was the solution and would result in a table as seen below.
| id | title | … | super_category_id | user_ref | position |
|---|---|---|---|---|---|
| 1 | A | NULL | user_1 | 1 | |
| 2 | C | NULL | user_2 | 2 | |
| 3 | B | NULL | user_2 | 1 | |
| 4 | BB | 1 | user_1 | 2 | |
| 5 | AA | 1 | user_1 | 1 | |
| 6 | CC | 2 | user_2 | 1 |
The inner select query divides the query’s result into partions. partition by c.super_category_id, c.user_ref order by c.title asc divides the query’s result set into partions, every partion has a unique super_category_id and user_ref and is in itself ordered alphabetically.
Using row_numer() we get the row number within the partion for every column and update the position column accordingly in the table.