Search string_to_array in Rails and PostgreSQL

#activerecord

#array

#hstore

#rails

#postgresql

#string_to_array

Rails 3 has not array: true in migration, one of many solution is by storing array as string, for example
[1,2,3,4] becomes '1,2,3,4'. Let's see study cases below:

*SEARCH STRING IN TEXT ARRAY *

Example migration:

create_table :seo_configs do |t|
  t.text    :keywords
  t.integer :user_id
  t.timestamps
 end

SeoConfig.create(keywords: 'red,blue,yellow', user_id: 1)
SeoConfig.create(keywords: 'purple,black,blue', user_id: 2)

If you want to search ['yellow','blue'] as your arguments, the approach is:

SeoConfig.string_keywords('yellow','blue')

class SeoConfig < ActiveRecord::Base
 scope :string_keywords, ->(*keywords){ where("(string_to_array(keywords,',')::text[]) && (ARRAY[?]::text[])", keywords) }
end

#it will return any records that contains yellow or blue in column keywords

*SEARCH STRING IN INTEGER ARRAY *

SeoConfig.create(keywords: '300,200,100', user_id: 2)

SeoConfig.create(keywords: '10,100,30,300', user_id: 1)

SeoConfig.integer_keywords(30, 200, 305)

class SeoConfig < ActiveRecord::Base
 scope :integer_keywords, ->(*keywords){ where("(string_to_array(keywords,',')::int[]) && (ARRAY[?]::int[])", keywords) }
end

# it will return any records that contain integer 30, 200 or 305 in column keywords

You can combine your searching or scope with Array Operator from Postgresql.

http://www.postgresql.org/docs/9.1/static/functions-array.html#ARRAY-OPERATORS-TABLE

And other data type numerics:

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html

Other solutions you can use hstore in rails 4 or array:true in your migration . Other good gems:

https://github.com/jackc/surus

https://github.com/diogob/activerecord-postgres-hstore

http://codeloveandboards.com/blog/2014/07/16/rails-and-prostgresql-hstore-simple-use-case/