Rails has_many_through关联查询无结果，求问题排查解决

我用has_many_through关联了一组模型，想要获取classification_id在传入数组中的classification_fields及其属性，但查询没返回任何结果，请问哪里错了？

获取父ID并查询classification_fields的代码 #

@parentids = @classification.self_and_ancestors_ids.to_a if params[:class_id].present?
@details = ClassificationField.includes(:classifications).where(classification_id: [@parentids] ) if params[:sub].present?

Classification模型 #

belongs_to :parent, class_name: "Classification", optional: true
has_many :children, class_name: "Classification", foreign_key: "parent_id", dependent: :destroy

has_many :class_fields
has_many :fields, through: :class_fields, source: :classification_field

accepts_nested_attributes_for :fields, allow_destroy: true

has_closure_tree

ClassificationField模型 #

has_many :class_fields
has_many :classifications, through: :class_fields

ClassFields模型 #

belongs_to :classification
belongs_to :classification_field

动态表单渲染代码 #

<%= form.fields_for :properties, OpenStruct.new(@sr.properties) do |builder| %>
  <% @details.each do |field| %>
    <%= render "srs/fields/#{field.field_type}", field: field, form: builder %>
  <% end %>
<% end %>

数据库Schema #

enable_extension "plpgsql"

create_table "class_fieldmembers", force: :cascade do |t|
  t.integer "classification_id"
  t.integer "classification_field_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "class_fields", force: :cascade do |t|
  t.bigint "classification_id", null: false
  t.bigint "classification_field_id", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["classification_field_id"], name: "index_class_fields_on_classification_field_id"
  t.index ["classification_id"], name: "index_class_fields_on_classification_id"
end

create_table "classification_fields", force: :cascade do |t|
  t.string "name"
  t.string "field_type"
  t.string "required"
  t.string "classification_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.string "displayname"
end

create_table "classification_hierarchies", force: :cascade do |t|
  t.integer "ancestor_id", null: false
  t.integer "descendant_id", null: false
  t.integer "generations", null: false
  t.index ["ancestor_id", "descendant_id", "generations"], name: "classification_anc_desc_idx", unique: true
  t.index ["descendant_id"], name: "classification_desc_idx"
end

create_table "classifications", force: :cascade do |t|
  t.string "name"
  t.string "displayname"
  t.text "description"
  t.boolean "inuse"
  t.integer "sort_order"
  t.integer "parent_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "srs", force: :cascade do |t|
  t.string "summary"
  t.text "description"
  t.string "status"
  t.string "priority"
  t.bigint "user_id", null: false
  t.bigint "classification_id", null: false
  t.text "properties"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["classification_id"], name: "index_srs_on_classification_id"
  t.index ["user_id"], name: "index_srs_on_user_id"
end

create_table "users", force: :cascade do |t|
  t.string "email", default: "", null: false
  t.string "encrypted_password", default: "", null: false
  t.string "reset_password_token"
  t.datetime "reset_password_sent_at"
  t.datetime "remember_created_at"
  t.boolean "admin"
  t.string "first_name"
  t.string "last_name"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["email"], name: "index_users_on_email", unique: true
  t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
end

add_foreign_key "class_fields", "classification_fields"
add_foreign_key "class_fields", "classifications"
add_foreign_key "srs", "classifications"
add_foreign_key "srs", "users"

控制台查询结果 #

[1] pry(main)> ClassificationField.includes(:classifications).where(classification_id: [1, 6] )
  ClassificationField Load (0.7ms)  SELECT "classification_fields".* FROM "classification_fields" WHERE "classification_fields"."classification_id" IN ($1, $2)  [["classification_id", "1"], ["classification_id", "6"]]
  ClassificationField Load (0.6ms)  SELECT "classification_fields".* FROM "classification_fields" WHERE "classification_fields"."classification_id" IN ($1, $2) /* loading for inspect */ LIMIT $3  [["classification_id", "1"], ["classification_id", "6"], ["LIMIT", 11]]
=> #<ClassificationField::ActiveRecord_Relation:0x4a10>

问题排查与修复 #

1. 查询参数嵌套错误 #

你写的where(classification_id: [@parentids])会把数组再套一层，比如@parentids是[1,6]，最终会变成[[1,6]]，这会导致SQL查询里的IN条件变成IN ('[1,6]')（字符串形式），而不是预期的IN (1,6)。

修复：去掉外层的数组包裹，直接用@parentids：

@details = ClassificationField.includes(:classifications).where(classification_id: @parentids) if params[:sub].present?

2. 字段类型不匹配 #

看Schema里classification_fields表的classification_id是string类型，但你传入的是整数ID（比如1、6），数据库会做类型转换，可能导致匹配失败。

修复方案二选一：

• 修改数据库字段类型，把classification_fields.classification_id改成bigint（和classifications.id类型一致），然后跑迁移；
• 查询时把ID转成字符串：

@parentids = @classification.self_and_ancestors_ids.map(&:to_s) if params[:class_id].present?

3. 关联查询的逻辑误区 #

你用includes(:classifications)是预加载关联，但你的查询条件是直接查classification_fields表的classification_id字段，而不是通过关联表class_fields查询。如果你的业务逻辑是要找关联到指定分类的classification_fields（而不是classification_fields自身的classification_id字段匹配），那查询逻辑完全错了，应该改成：

@details = ClassificationField.joins(:classifications).where(classifications: { id: @parentids }).distinct

解释：joins(:classifications)会关联class_fields中间表和classifications表，然后通过classifications.id筛选，distinct用来避免重复的classification_fields结果。

4. 空值判断补充 #

要确保@parentids不为空才执行查询，避免生成IN (NULL)的无效SQL：

if params[:sub].present? && @parentids.present?
  @details = ClassificationField.joins(:classifications).where(classifications: { id: @parentids }).distinct
end

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