Ordering categories using SQL

[csmu (Keith John Hutchison - Ceiteach Seán Mac Úistin)]

The following code changes the order of categories to be alphanumeric using SQL.

Preconditions.
Postgres backend

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.

/var/discourse/launcher enter app
su postgres
psql discourse

and then executing the SQL.

Running from rails console.

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")

[pfaffman (Jay Pfaffman)]

I’ve not tested this recently but here’s at least a hint for how to alpha-sort categories at the rails console:

# 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

[csmu (Keith John Hutchison - Ceiteach Seán Mac Úistin)]

Thanks @pfaffman

Based on your ruby code I came up with

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

[amoun]

안녕하세요. 처음 왔습니다.
이 게시글에서 카테고리를 GUI 옵션을 통해 알파벳 순서로 설정할 수 없다는 점이 명확히 설명되고 있나요?

감사합니다

[pfaffman (Jay Pfaffman)]

그 정도는 됩니다. 고정된 순서로 배치할 수 있는 설정이 있습니다. 먼저 시스템 설정을 설정해야 합니다(아마도 'fixed’로 검색하면 찾을 수 있을 겁니다). 그런 다음 UX에서 처리할 수 있습니다.

항목이 열두 개 정도라면 UX에서 처리하는 것이 그렇게 나쁘지 않습니다(그 인터페이스는 문제가 많았고 요즘 어떻게 작동하는지 모르기 때문에 일부는 틀릴 수 있습니다). 하지만 수백 개가 있고 변경하지 않을 것이라면, 이전에 콘솔에서 실행하여 정렬하는 스크립트를 작성한 적이 있습니다. 항목이 많고 계속 변경하면서 정렬 상태를 유지하려면 플러그인이 필요합니다.

[amoun]

감사합니다. 저는 ‘discourse’ Fairphone 포럼의 사용자일 뿐이며, 관리자에게 나열 순서를 변경해 달라고 요청했습니다. 하지만 첫 응답은 이 포럼에서 어떻게 해야 하는지 알아봐 달라는 것이었으므로, 쉽게 사용할 수 있는 옵션은 아닌 것 같습니다.

다시 한번 감사합니다.

[pfaffman (Jay Pfaffman)]

카테고리가 수십 개가 아니라면 큰 문제가 아닙니다. 그리고 만약 그들이 직접 설정을 확인하거나 여기서 직접 질문하는 대신 여기로 보냈다면, 그것은 그들이 직접 하고 싶지 않아서일 뿐입니다.

[amoun]

다시 한번 감사합니다.

관리자들은 백엔드에 접근할 수 없는 것 같고, 이는 필수적인 것 같습니다.

그래서 직접 다운로드해서 호스팅을 해보겠습니다. 그러면 제가 원하는 작업을 어떻게 해야 하는지 알아내고 그 정보를 공유할 수 있을 것 같습니다 이 문제를 처리할 수 있는 관리자가 분명 있을 거예요.

[amoun]

모든 중재자 중 한 명이 방법을 알고 있는 것 같습니다. 알려드릴게요. 햄버거 메뉴를 통해 할 수 있다고 하던데 ??