Ruby on rails / Active Record Query: return if field / record with array not contains element

Question

I've got in my TravelIdeas table field country_trips, which includes the array of countries.

#<TravelIdea:0x007faa7bec40f0> {
                      :id => 9,
                :idea_key => "romantic",
               :idea_type => nil,
                    :cost => 2000,
              :created_at => Mon, 10 Jul 2017 07:48:49 UTC +00:00,
              :updated_at => Mon, 16 Oct 2017 08:10:47 UTC +00:00,
           :country_trips => [
                [0] "PL"
               ],
     :available_languages => [
                [0] "pl"
              ]
}

Local travels has nil in :country_trips, so I can ignore them:

idea.where.not(country_trips: nil) if country.present?

I've got my user destination country, so I want to show him all travel ideas which contains his dreamed country.

It would be sth like SQL server CONTAINS (column, 'country')

Probably this is not about LIKE because this is not about matching 1 country to 1 country.

For example country = user.destination_country means: country = "PL"

but

:country_trips => [
   [0] "PL",
   [1] "DE"
   ],

I'm looking for sth like

TravelIdea.where(country_trips: country)

that will return all travel ideas which have this country in country_trips array :P

Did you use Rails' serialize :country_trips or did you use your RDMS built-in system for storing arrays? (like this for postgres postgresql.org/docs/9.1/static/arrays.html) — MrYoshiji
– MrYoshiji, Commented Oct 17, 2017 at 13:38
try this TravelIdea.where('country_trips = any(?)', 'PL') (you can google postgres array rails and you will find useful information like this one coderwall.com/p/sud9ja/rails-4-the-postgresql-array-data-type) — MrYoshiji
– MrYoshiji, Commented Oct 17, 2017 at 13:44
I tried this and it returns error output error: #<ActiveRecord::StatementInvalid: PG::UndefinedObject: ERROR: could not find array type for data type text[] — eudaimonia
– eudaimonia, Commented Oct 17, 2017 at 13:47

Leonel Galán · Accepted Answer · 2017-10-17 17:41:26Z

1

This is more a postgres question, section 8.15.5. Searching in Arrays says:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

In your case, you could write that as a SQL segment inside the .where method:

TravelIdea.where("? = ANY (country_trips)", country)

edited Oct 17, 2017 at 17:41

answered Oct 17, 2017 at 13:46

Leonel Galán

7,1872 gold badges44 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ruby on rails / Active Record Query: return if field / record with array not contains element

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related