CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlruby-on-rails-4
Kingsley Simon
Kingsley Simon

Reputation: 2210

SQL Query Where Array is in Array Field

I have a table which has an array field called countries So lets say for example in this table called country_list i have the following records

Name         Countries
Asia         ['Malaysia, Singapore, Hong Kong]
Worldwide    ['Malaysia, Singapore, Hong Kong, Croatia, USA]

Now if i want to query this table to find out which records have ['Malaysia, 'Singapore'], how do i write the query in my ruby console.

I tried to do this but it didnt work

CountryList.where("countries IN (['Malaysia', 'Singapore'])") but it didnt work as expected.

Any help is appreciated

Upvotes: 1

Views: 878

Answers (2)

rahul mishra
rahul mishra

Reputation: 1470

Try out this:

countries = ['Malaysia', 'Singapore']
query = CountryList.where(":name = ANY(country_list.countries)", name: countries[0])

countries[1..-1].each do |c|
  query = query.or(CountryList.where(":name = ANY(country_list.countries)", name: c))
 end
query

Have Checked its working

enter image description here

Thanks!

Upvotes: 1

Rajesh V
Rajesh V

Reputation: 156

There is no direct approach to query serialized data. But the other work around is by selecting/filtering from all the results. Eg:

Model.all.select{|s| (s.countries &['Malaysia', 'Singapore']).present?}

Upvotes: 0

Related Questions