mysql - 在存储数组的列中搜索

Question

在我的表 professional_infos 表中，有一列 primary_skill_ids 存储了特定用户的一系列技能。我想列出所有具有某些特定技能的用户。示例 -

user1 has primary_skill as ["1","3","5","9","4"]
user2 has primary_skill as ["1","7","9","4"]
user3 has primary_skill as ["1","4","11"]
user3 has primary_skill as ["7","9","4"]
user4 has primary_skill as ["1","7","9"]
user5 has primary_skill as ["7","9"]

现在我想执行搜索，比如让我找到所有具有任何或所有技能 primary_skill_ids 为 ["1","4] 的用户

请帮我写一个 Rails 查询来做到这一点。

我做了如下的

     wildcard_search = "%#{params[:search_title]}%"
            # key skills and best in search
      @key_skills=[] 
     @key_skills.each do | sk |
     # here I am thinking of looping through the ids and do a where clause on column primary_skill_ids but dont know its good idea


            end

Answer 1

序列化字符串列中的关系数据违反了关系数据库的理念——即您拥有指向其他表的外键列。在数据库中使用数组* 或字符串类型进行关联是一个非常糟糕的主意:

• 与包含整数或 uiid 的索引列相比，搜索字符串的效率非常低。
• 外键约束没有强制执行参照完整性。
• 这不是 ActiveRecord 的工作方式 - 这意味着您将通过与框架作斗争来浪费时间。

相反，您想创建一个 many-to-many association through a join table :

class User < ApplicationRecord
  has_many :user_skills
  has_many :skills, through: :user_skills
end

class Skill < ApplicationRecord
  has_many :user_skills
  has_many :users, through: :user_skills
end

class UserSkill < ApplicationRecord
  belongs_to :user
  belongs_to :skill
end

在此示例中，我们使用名为 user_skills 的表来连接两个模型:

create_table "user_skills", force: :cascade do |t|
  t.integer  "user_id"
  t.integer  "skill_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["skill_id"], name: "index_user_skills_on_skill_id", using: :btree
  t.index ["user_id"], name: "index_user_skills_on_user_id", using: :btree
end

然后您可以使用集合助手设置 UI 控件:

# app/views/users/_form.html.erb
<%= form_for(@user) do |f| %>
  <%= f.collection_check_boxes :skill_ids, Skill.all, :id, :name %>
<% end %>

# app/controllers/users_controller.rb
class UsersController < ApplicationController
  # POST /users
  def create
    @user = User.new(user_params)
    if @user.save
      redirect_to @user
    else
      render :new
    end
  end

  # PUT|PATCH /users/:id
  def update
    @user = User.find(params[:id])
    if @user.update(user_params)
      redirect_to @user
    else
      render :edit
    end
  end

  def user_params 
    params.require(:user)
          .permit(skill_ids: [])
  end
end