0

Ok this is a bit complex

I currently have a search function that allows customers to search records based on 1 or more states.

they can enter multiple states and this function will search the post to match the post that matches any one of those states.

With this the post can only have 1 state.

I am tring to expand and allow each post to have multiple states. but for this to happen i need to figure out how to match any one of the seached states to any one of the posted states.

In this example type = "dest" and states = "mo, ks, ar, ok"

def PrepareSearch.states(type, states)
  states = states.split(",")
  st = ""
  states.each {|s| st += "'#{s}'," }
  st = st[0..-2]
  "#{type}_state IN (#{st})"
end

It would match a post that has any one of those states in dest_state and only has 1 of them in it

I need this to be expanded and match something like this:

dest_state = "mo, ks, ok, ar" 

states = "ks, ne, co"

I need that to match because ks is in both

But i also need it to work if it only has one state in the dest_state as well

my database has 100k + records that this will be searching thru.

dest_state is a stored field in the record in the database

Big Al Ruby Newbie
  • 834
  • 1
  • 10
  • 30

1 Answers1

0

You can use the Postgres "overlap" operator &&:

SELECT * FROM tbl WHERE dest_state && states;

An additional benefit is that this operator can be supported with a GIN index on the state array column. Makes a huge difference for performance. Example:

Since your table is big I would use int or smallint instead of strings as "state" codes. Or use an enum.

Another option would be to normalize your database schema and implement this as n:m relationship between posts and states. Details:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • That's fine. `&&` is an SQL operator for arrays. – Erwin Brandstetter Feb 18 '15 at 17:26
  • Was kind of hoping to avoid creating a new table... that table would have 3 columns... id, post_id, state with 1 - 10 records for each post... so with as many records as i have would have 300k - well over a million records – Big Al Ruby Newbie Feb 18 '15 at 17:31
  • @BigAlRubyNewbie: That's right. An array column wins concerning storage and performance, but you *need* index support. And performance with `int[]` or `smallint[]` beats of `text[]`. The index is much more important here than the type, though. A normalised schema on the other hand allows many other standard features like referential integrity and makes a couple of things easier. – Erwin Brandstetter Feb 18 '15 at 17:48