使用 SQL 对类别进行排序

开发
1

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")
2 个赞
2

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
1 个赞
3

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
1 个赞
4

嗨，第一次来这里。
这篇帖子是否说明了无法通过某些图形界面选项按字母顺序设置分类？

谢谢

5

差不多。有一些设置可以将它们按固定顺序排列。我认为你首先需要设置一个系统设置（搜索“固定”试试），然后就可以在用户界面中操作了。

如果你有十几个，在用户界面中操作并不算太麻烦（那个界面一直有些问题，而且我不确定现在它是怎么工作的，所以这部分内容可能不完全准确）；如果你有数百个且不会频繁变动，我曾经写过一些脚本在控制台中运行来对它们进行排序。如果你有很多且需要频繁变动，同时希望保持排序，那就需要一个插件了。

6

谢谢。我只是“公平手机”免费电话论坛的用户，曾询问版主是否可以调整列表顺序，但最初的回复是让我在这个论坛上查询具体操作方法，因此我想这显然不是一个现成的选项。

再次感谢。

7

除非有几十种分类，否则这根本不是什么大问题。如果他们把你派到这里，而不是自己去设置里查找或在这里提问，那说明他们只是不想自己动手。

8

再次感谢。

我认为版主无法访问后端，而这似乎是必不可少的。

我想我会下载并自行托管一个，这样我应该就能弄清楚如何实现我的需求，并将相关信息分享出去 :slight_smile: 一定有一位管理员可以处理这个问题。

1 个赞
9

看起来其中一位版主知道如何操作，我会告知你。消息说是可以通过汉堡菜单完成？