000032_move_boards_category_to_end.up.sql

TLDR

This SQL file is used to move the "Boards" category to the end of a list by updating the sort_order column. The value for sort_order is calculated based on the total number of categories in the database.

Methods

N/A

Classes

N/A

{{- /* To move Boards category to to the last value, we just need a relatively large value. */ -}}
{{- /* Assigning 10x total number of categories works perfectly. The sort_order is anyways updated */ -}}
{{- /* when the user manually DNDs a category. */ -}}

{{if or .postgres .sqlite}}
UPDATE {{.prefix}}categories SET sort_order = (10 * (SELECT COUNT(*) FROM {{.prefix}}categories)) WHERE lower(name) = 'boards';
{{end}}

{{if .mysql}}
{{- /* MySQL doesn't allow referencing the same table in subquery and update query like Postgres, */ -}}
{{- /* So we save the subquery result in a variable to use later. */ -}}
SET @focalboard_numCategories = (SELECT COUNT(*) FROM {{.prefix}}categories);
UPDATE {{.prefix}}categories SET sort_order = (10 * @focalboard_numCategories) WHERE lower(name) = 'boards';
SET @focalboard_numCategories = NULL;
{{end}}