Search string_to_array in Rails and PostgreSQL
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/