Rails 关于父子分类的查询问题

bindiry · July 16, 2015 · Last by bindiry replied at July 23, 2015 · 2511 hits

下面是实现父子分类的代码:

migrate:

class CreateCategories < ActiveRecord::Migration
  def change
    create_table :categories do |t|

      t.integer :parent_id
      t.string  :name

      t.timestamps null: false
    end
  end
end

model:

class Category < ActiveRecord::Base
  has_many :sub_categories, class_name: "Category", foreign_key: "parent_id", dependent: :destroy
  belongs_to :parent_category, class_name: "Category", foreign_key: "parent_id"
end

查询语句:

categories = Category.includes(:sub_categories).where(parent_id: nil)

控制台返回的 SQL:

Category Load (1.5ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" IS NULL
Category Load (1.8ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" IN (1, 5)
Category Load (1.9ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" = ?  [["parent_id", 2]]
Category Load (1.2ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" = ?  [["parent_id", 3]]
Category Load (1.3ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" = ?  [["parent_id", 4]]
Category Load (1.3ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" = ?  [["parent_id", 6]]
Category Load (1.0ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" = ?  [["parent_id", 7]]
Category Load (1.2ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" = ?  [["parent_id", 8]]

发现会为子分类再查询一遍子分类,控制台返回的 SQL 语句从第 2 行之后,都是无用的。

另外数据是要最终渲染成 API 提供的 json 数据。

哪位同学有好的解决办法?

目前的办法是把记录全取出来,手动处理父子结构,然后渲染成 json

如有更好的办法,请告知,谢谢啦。

gem awesome_nested_set

#2 楼 @flowerwrong 只有父子分类,想能不用这个就不用这个。

@bindiry 遇到同样问题,能把你的手动方法 share 一下吗

#4 楼 @bajie

categories = Category.all
list = []
parent_hash = {}
categories.each do |category|
  if category.parent_id.blank?
    pcategory = category.attributes
    pcategory["sub_categories"] = []
    list.push(pcategory)
    parent_hash[category.id] = list.index(pcategory)
  end
end
categories.each do |category|
  if !category.parent_id.blank?
    list[parent_hash[category.parent_id]]["sub_categories"].push(category)
  end
end

最后的 list 就是想要的结果。

def self.to_tree(options={})
  options = { ignore_empty: false }

  Rails.cache.fetch('community_tree') do
    mappings = {}
    communities = Community.order(parent_id: :desc, position: :asc)
    communities.each do |community|
      if community.parent_id
        hash = community.to_hash
        mappings[community.parent_id.to_s][:children].push(hash)
      else
        hash = community.to_hash.merge(children: [])
        mappings[community.id.to_s] = hash
      end
    end
    mappings.values
      .sort_by { |c| c[:position] }
      .reject { |c| options[:ignore_empty] && c[:children].empty? }
  end
end

我是取一回,然后做 cache

#2 楼 @flowerwrong 如果是用 awesome_nested_set 的话,如果我想输出树型结构的 JSON 数据,是不是也要先数据库一次读出,然后自己手动组织结构再转出 JSON?

这个结构为啥不用单表继承

#8 楼 @flypiggys 现在又在做另一个需求了,是三级分类

You need to Sign in before reply, if you don't have an account, please Sign up first.