CODEWITHSUNDEEP
mysqlsqlruby-on-railsrubyruby-on-rails-4
pwnchaurasia
pwnchaurasia

Reputation: 1499

Search In a column which stores an array

In my table professional_infos table there is one column primary_skill_ids which stores an array of skills for particular user. I want to list all the user who has some particular skill sets. example -

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"]

now I want to perform a search like get me all the users which have any or all of the skill primary_skill_ids as ["1","4]

please help me to write a rails query to do so.

I have done like the following

     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

Upvotes: 1

Views: 93

Answers (2)

mmsilviu
mmsilviu

Reputation: 1451

PostgreSQL supports standard SQL arrays and the standard any operation syntax:

So, you can use an SQL like 

where name ilike any (array['%val1%', '%val2%'])

Using Rails it can be written like:

User.where('primary_skill ilike any (array[?])', ["1","4"] )

Additional info:

In your situation you can use "ILIKE", or "LIKE", but the difference is:

  • "ILIKE" is case-insensitive
  • "LIKE" is case-sensitive.

Usage of wildcards (% from '%val1%') are detailed here

Upvotes: 0

max
max

Reputation: 102036

Serializing relation data in a string column violates the very idea of a relational database - which is that you have foreign key columns that point to other tables. Using array* or string types in the DB for associations is a really bad idea:

  • It is highly inefficient to search strings compared to an indexed column containing integers or uiids.
  • There is no referential integrity enforced by foreign key constraints.
  • Its not how ActiveRecord works - which means you'll be wasting time by fighting the framework.

Instead you want to create a 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

In this example we are using a table named user_skills to join the two models:

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

You can then setup UI controls by using the collection helpers:

# 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

Upvotes: 3

Related Questions