# Ordering categories using SQL
**URL:** https://meta.discourse.org/t/ordering-categories-using-sql/111960
**Category:** Development
**Created:** [March 18, 2019, 9:37pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960 "2019-03-18T21:37:48Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@csmu](https://meta.discourse.org/u/csmu)
#### Post date: [March 18, 2019, 9:37pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/1 "2019-03-18T21:37:48Z")
The following code changes the order of categories to be alphanumeric using SQL.
Preconditions.
Postgres backend
```SQL
UPDATE categories
SET POSITION = subquery.position
FROM (
SELECT POSITION
, CASE WHEN category_id = 0 THEN parent_category_id ELSE category_id END category_id
, CASE WHEN category_id = 0 THEN parent_category_name ELSE category_name END category_name
FROM (
SELECT ROW_NUMBER - 1 AS POSITION
, parent_category_id
, parent_category_name
, category_id
, category_name
FROM ( SELECT row_number() OVER ( ORDER BY parent_category_name, category_name )
, * FROM (
SELECT * FROM (
SELECT id parent_category_id
, name parent_category_name
, 0 category_id
, cast('' AS text) category_name
FROM categories
WHERE parent_category_id IS NULL ORDER BY name
) parent_categories
UNION
(
SELECT child.parent_category_id
, parent.name parent_category_name
, child.id category_id
, child.name category_name
FROM categories child
INNER JOIN categories parent
ON child.parent_category_id = parent.id
WHERE child.parent_category_id IS NOT NULL ORDER BY child.name
)
) parents_with_children
ORDER BY parent_category_name, category_name
) ordered_parents_with_children
ORDER BY ROW_NUMBER
) category_positions_sorted_alphabetically
) subquery
WHERE id = subquery.category_id ;
```
I ran the code by starting up a psql session.
```plaintext
/var/discourse/launcher enter app
su postgres
psql discourse
```
and then executing the SQL.
Running from rails console.
```ruby
ActiveRecord::Base.connection.execute("UPDATE categories SET POSITION = subquery.position FROM ( SELECT POSITION , CASE WHEN category_id = 0 THEN parent_category_id ELSE category_id END category_id , CASE WHEN category_id = 0 THEN parent_category_name ELSE category_name END category_name FROM ( SELECT ROW_NUMBER - 1 AS POSITION , parent_category_id , parent_category_name , category_id , category_name FROM ( SELECT row_number() OVER ( ORDER BY parent_category_name, category_name ) , * FROM ( SELECT * FROM ( SELECT id parent_category_id , name parent_category_name , 0 category_id , cast('' AS text) category_name FROM categories WHERE parent_category_id IS NULL ORDER BY name ) parent_categories UNION ( SELECT child.parent_category_id , parent.name parent_category_name , child.id category_id , child.name category_name FROM categories child INNER JOIN categories parent ON child.parent_category_id = parent.id WHERE child.parent_category_id IS NOT NULL ORDER BY child.name ) ) parents_with_children ORDER BY parent_category_name, category_name ) ordered_parents_with_children ORDER BY ROW_NUMBER ) category_positions_sorted_alphabetically ) subquery WHERE id = subquery.category_id")
```
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [March 18, 2019, 9:55pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/2 "2019-03-18T21:55:38Z")
I’ve not tested this recently but here’s at least a hint for how to alpha-sort categories at the rails console:
```ruby
# alpha sort all categories matching search and their sub-categories
def sort_matching_categories_and_subcategories(search)
categories = Category.where("name like ?", search)
position = 100
categories.order(:name).each do |cat|
position += 5
cat.position = position
cat.save!()
c_position = 0
children = Category.where(:parent_category_id=>cat.id)
children.order(:name).each do |c|
c_position += 5
c.position = c_position
c.save!()
end
end
end
# alpha sort subcategories of a single category matching search
def sort_matching_subcategories(search)
categories = Category.where("name like ?", search)
if categories.count > 1
puts "Found more than one category"
end
categories.order(:name).each do |cat|
c_position = 5
children = Category.where(:parent_category_id=>cat.id)
children.order(:name).each do |c|
c_position += 5
c.position = c_position
c.save!()
end
end
end
```
---
### Author:  [@csmu](https://meta.discourse.org/u/csmu)
#### Post date: [March 19, 2019, 8:19pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/3 "2019-03-19T20:19:46Z")
Thanks @pfaffman
Based on your ruby code I came up with
```ruby
def sort_categories_by_name(skip=0)
parents = Category.where("parent_category_id is null")
position = 0
parents.order(:name).each do |parent|
parent.position = position
parent.save!()
position += (1+skip)
children = Category.where(:parent_category_id=>parent.id)
children.order(:name).each do |child|
child.position = position
child.save!()
position += (1+skip)
end
end
position += -(1+skip)
return position
end
sort_categories_by_name
```
---
### Author:  [@amoun](https://meta.discourse.org/u/amoun)
#### Post date: [March 13, 2021, 10:16am UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/4 "2021-03-13T10:16:07Z")
Hi. First time here.
Does this post clarify that Categories cannot be set alphabetically via some GUI option?
Thanks
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [March 13, 2021, 1:23pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/5 "2021-03-13T13:23:53Z")
Sort of. There are settings to put them in a fixed order. I think you first set a system setting (search for fixed, perhaps) and then you can do it in the ux.
If you have a dozen, it’s not too bad to do it in the ux (that interface has been troublesome and I don’t know how it works these days, so some of this might be wrong), if you have hundreds and won’t change, I once wrote some stuff to run at the console to sort them. If you have lots and change them all the time and want to keep them sorted, you’d need a plugin.
---
### Author:  [@amoun](https://meta.discourse.org/u/amoun)
#### Post date: [March 13, 2021, 2:17pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/6 "2021-03-13T14:17:13Z")
Thanks. I’m only a user of the ‘discourse’ [Freephone forum](https://forum.fairphone.com/) and asked the moderators if they could change the listed order, but the initial response was to query this forum to find out how, so clearly not a readily available option I imagine.
Thanks again
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [March 13, 2021, 3:03pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/7 "2021-03-13T15:03:37Z")
Unless there are dozens of categories, it’s just not a big deal. And if they sent you here rather than looking in the settings themselves or asking here themselves, they just don’t want to do it.
---
### Author:  [@amoun](https://meta.discourse.org/u/amoun)
#### Post date: [March 13, 2021, 3:14pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/8 "2021-03-13T15:14:53Z")
Thanks again.
I think the moderators don’t have access to the backend, which seems essential.
I think I’ll download and host one then I should be able to find out how to do what I want and pass on the info 🙂 There must be an administrator that can handle the issue.
---
### Author:  [@amoun](https://meta.discourse.org/u/amoun)
#### Post date: [March 13, 2021, 3:24pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/9 "2021-03-13T15:24:02Z")
Looks like one of the moderators knows how to do it, will let you know. The word is it can be done via the hamburger menu ??