Ruby/Sequel关联创建异常：Language查询所属语系返回nil

输入数据 #

language_families_csv #

languages_csv #

数据库Schema #

module Language_to_LanguageFamily
  def self.included(base)
    base.one_to_many :languages, key: :parent_family, class: :Language
    base.many_to_one :parent_family, class: :LanguageFamily, key: :family_code, primary_key: :family_code
  end
end  


### Language_Families ###
MyDB.create_table :Language_Families do
  primary_key :language_family_id
  Integer :level, index: true, null: false
  String :family_code, size: 7, index: true, null: false, unique: true
  String :meta_comment, text: true
  
  # 自关联到主键
  foreign_key :parent_code, :Language_Families, key: :language_family_id
end

class LanguageFamily < Sequel::Model
  plugin :timestamps, update_on_create: true, force: true
  plugin :json_serializer
  plugin :validation_helpers

  # 自关联
  one_to_many :children, class: :LanguageFamily, key: :parent_code
  many_to_one :parent, class: :LanguageFamily, key: :parent_code

  # 外部关联
  include Language_to_LanguageFamily
end

### Languages ###
MyDB.create_table :Languages do
  primary_key :language_id
  String :language_code, size: 7, index: true, null: false, unique: true
  String :meta_comment, text: true

  # 可选关联
  foreign_key :parent_family, :Language_Families, key: :family_code, null: true, default: nil
end

class Language < Sequel::Model
  plugin :timestamps, update_on_create: true, force: true
  plugin :json_serializer
  plugin :validation_helpers
  
  # 外部关联
  include Language_to_LanguageFamily

  # 手动定义访问器
  attr_accessor :parent_family
end

实现代码 #

require 'csv'
require 'sequel'

MyDB = Sequel.connect adapter: :sqlite, database: 'MirDB.sqlite'

MyDB.drop_table(*db.tables)

# 存储语系code对应的id
code_id = {}

# 插入语系数据
language_families_csv.each do |family|
  language_family = LanguageFamily.new
  language_family.level = family["level"].to_i
  language_family.family_code = family["family_code"]
  language_family.meta_comment = family["meta_comment"]
  if parent_code = family["parent_code"]
    language_family.parent = LanguageFamily.find(parent_code: parent_code)
  end
  language_family.save
  code_id[language_family.family_code] = language_family.id
end

# 插入语言数据
languages_csv.each do |lang_row|
  language = Language.new
  language.language_code = lang_row["language_code"]
  language.meta_comment = lang_row["meta_comment"]
  
  if lang_row["parent_family"]    
    lang_parent_family = LanguageFamily.first(family_code: lang_row["parent_family"])
    if lang_parent_family       
      language.parent_family = lang_parent_family
      # puts "#{language.parent_family} ↔ #{lang_parent_family.family_code}"
    else
      puts 'Family '.red + lang_parent_family.family_code.cyan + ' doesn’t exist!'.red
    end
  end
  
  language.save
end

问题现象 #

执行puts Language.all.to_s后，所有Language实例的parent_family均为nil：

[
    #<Language @values={:language_id=>1, :language_code=>"ENG", :meta_comment=>"English", :parent_family=>nil}>,
    #<Language @values={:language_id=>2, :language_code=>"FRA", :meta_comment=>"French", :parent_family=>nil}>,
    #<Language @values={:language_id=>3, :language_code=>"GER", :meta_comment=>"German", :parent_family=>nil}>,
    #<Language @values={:language_id=>4, :language_code=>"SPA", :meta_comment=>"Spanish", :parent_family=>nil}>,
    #<Language @values={:language_id=>5, :language_code=>"POL", :meta_comment=>"Polish", :parent_family=>nil}>,
    #<Language @values={:language_id=>6, :language_code=>"CES", :meta_comment=>"Czech", :parent_family=>nil}>,
    #<Language @values={:language_id=>7, :language_code=>"RUS", :meta_comment=>"Russian", :parent_family=>nil}>,
    #<Language @values={:language_id=>8, :language_code=>"UKR", :meta_comment=>"Ukrainian", :parent_family=>nil}>,
    #<Language @values={:language_id=>9, :language_code=>"BEL", :meta_comment=>"Belarusian", :parent_family=>nil}>,
    #<Language @values={:language_id=>10, :language_code=>"CSB", :meta_comment=>"Kashubian", :parent_family=>nil}>,
    #<Language @values={:language_id=>11, :language_code=>"POX", :meta_comment=>"Polabian", :parent_family=>nil}>,
    #<Language @values={:language_id=>12, :language_code=>"SLK", :meta_comment=>"Slovak", :parent_family=>nil}>,
    (...)
]

问题排查与修复 #

1. 关联定义错误 #

Language_to_LanguageFamily模块中的many_to_one :parent_family是为LanguageFamily类设计的，直接include到Language类会导致关联参数完全不匹配。Language类需要单独定义与LanguageFamily的关联：

修改Language类代码，移除模块include和手动attr_accessor，添加正确关联：

class Language < Sequel::Model
  plugin :timestamps, update_on_create: true, force: true
  plugin :json_serializer
  plugin :validation_helpers
  
  # 正确定义关联：Language表的parent_family字段存储的是LanguageFamily的family_code
  many_to_one :parent_family, class: :LanguageFamily, key: :parent_family, primary_key: :family_code
end

2. 手动attr_accessor覆盖关联属性 #

Language类中手动添加的attr_accessor :parent_family会覆盖Sequel自动生成的关联属性访问器，导致赋值无法同步到数据库字段。直接删除这行代码即可。

3. LanguageFamily自关联数据插入错误 #

插入语系数据时，language_family.parent = LanguageFamily.find(parent_code: parent_code)逻辑错误——CSV中的parent_code是语系的family_code，但find(parent_code: parent_code)是查找parent_code字段等于该值的记录，而parent_code字段存储的是主键language_family_id。

修改语系插入代码：

language_families_csv.each do |family|
  language_family = LanguageFamily.new
  language_family.level = family["level"].to_i
  language_family.family_code = family["family_code"]
  language_family.meta_comment = family["meta_comment"]
  
  # 先判断parent_code非空
  if parent_code = family["parent_code"]&.strip
    parent_family = LanguageFamily.find(family_code: parent_code)
    language_family.parent = parent_family if parent_family
  end
  
  language_family.save
  code_id[language_family.family_code] = language_family.id
end

4. 外键定义优化（可选） #

为了避免字段名与关联名称混淆，可将Languages表的外键字段重命名，同时修正外键定义：

# 修改Languages表创建代码
MyDB.create_table :Languages do
  primary_key :language_id
  String :language_code, size: 7, index: true, null: false, unique: true
  String :meta_comment, text: true

  # 重命名外键字段，明确指向family_code
  String :parent_family_code, size:7, index: true, null: true, default: nil
  foreign_key :parent_family_code, :Language_Families, key: :family_code
end

# 同步修改Language类的关联
class Language < Sequel::Model
  # ...其他插件
  
  many_to_one :parent_family, class: :LanguageFamily, key: :parent_family_code, primary_key: :family_code
end

# 插入语言数据时同步修改赋值逻辑
language.parent_family = lang_parent_family

修复后验证 #

执行以下代码确认关联生效：

# 验证波兰语的所属语系
polish = Language.find(language_code: 'POL')
puts polish.parent_family.meta_comment # 应输出"莱赫语分支"

# 验证莱赫语分支下的所有语言
leh_family = LanguageFamily.find(family_code: 'leh-ZLW')
puts leh_family.languages.map(&:language_code).inspect # 应输出["POL", "CSB", "POX"]

内容的提问来源于stack exchange，提问作者MrVocabulary