-1

what is the best way to structure Location information for users? I need to be able filter hundreds of users by City/Country.

Currently I have created a free-text string for location as I could not find any gem for whole cities grouped under countries. Btw this is a custom scaffold, not devise.

enter image description here

Should I just enter as free-text as in city, country and then filter based on that string with alike?

Thank you

Designer
  • 1,061
  • 1
  • 12
  • 26

2 Answers2

1

If you have structured data (i.e., address, city, state, country) for your users, you can filter on those. For example:

WHERE city || ', ' || country ILIKE ?

If you don't, you can still try that assuming freeform location information on your users table (you weren't clear on the database schema):

WHERE location ILIKE ?

Ideally you'd split up the city and country and filter separately:

WHERE city ILIKE ? AND country ILIKE ?

Hundreds shouldn't be much of a performance issue but you can definitely build indexes for either of those:

CREATE INDEX city_country_on_users ON users(city || ', ' || country);
CREATE INDEX city_country_on_users ON users(location);
CREATE INDEX city_country_on_users ON users(city, country);

respectively

Caleb Hearth
  • 3,315
  • 5
  • 30
  • 44
  • Thanks Caleb! So I should have 2 separate text inputs for **City** and **Country** in my User table. right? I will type this inputs as I add users and by this way the city/country data will grow in the table... Later I can pull this as a clickable filter. So I dont really need a separate table for city/location, right? – Designer Dec 08 '17 at 16:08
1

I don't recommend usage of free-tex input because you will get different possible entries for the same city and country which will make filteration an impossible task. It is always better to use a drop down list.

Here is a full list of countries and cities using java script.

change the location attribute with two attributes country and state and use the previous list to fill them.

amronrails
  • 100
  • 13