Hstore PostgreSQL Array comparision to any in array, Rails 3

Question

Thank you for your time:

I have a group of records (users to be specific) with an Hstore hash saved in a "data" field. The thing is I want to make a single SQL to fetch the users from that group of users with hash hstore saved property in that "data" field matching another array of strings values like this:

data: { "foo" => "", "bar" => "bars", "baz" => "bazz" }

array of strings to match ["foos", "bazz"]

NOTE: I want to match the values, NOT the keys

every user has a set of "data" foo/bar/bas properties, and I send an array of "keys" from the hash to extract and compare to the second "values" array but I can't seem to make a single SQL matching. So far this is all I have:

keys = ["foo", "baz"]
values_to_match = ["bars", "bazz"]
users.where("users.data -> ARRAY[?] ILIKE ANY (ARRAY[?])", keys, values_to_match)

Hoang Phan · Accepted Answer · 2015-12-01 04:59:46Z

1

Assuming you are writing a class method for model User, you could think of a naive sql solution:

def self.matched_users(keys, values_to_match)
  result = self
  keys.each_with_index do |key, index|
    result.where("users.data -> ? ILIKE ?", key, "%#{values_to_match[index]}%")
  end
  result
end

or if passing a hash

def self.matched_users(hash)
  hash.inject(users) do |result, (key, value)|
    result = result.where("users.data -> ? ILIKE ?", key, "%#{value}%")
  end
end

answered Dec 1, 2015 at 4:59

Hoang Phan

2,1661 gold badge17 silver badges16 bronze badges

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

1 Comment

Alexis Rabago Carvajal Over a year ago

I'll give it a try but isn't there a more SQL like elegant solution which doesn't involve ruby iteration? it is more like an improvement because the original query iterates over a user array and makes an SQL query over each user, which is very uneffective, and stacking SQL queries over is not very elegant or codewise I think, I will try it anyway, it gives me some direction thanx

Alexis Rabago Carvajal · Accepted Answer · 2015-12-01 18:02:41Z

0

I found a simple SQL solution:

users.where("(users.data -> ARRAY[?]) && (ARRAY[?])", keys, values_to_match)

Seems like Intersection of arrays compares correctly the values from the data field of users even tho the documentation says it returns a hash like:

hstore -> ARRAY[keys] | result: { "value", "value" }

thank you for your time to all those who took their time to answer or read, if anyone could clarify why this happens I would be thankfull

answered Dec 1, 2015 at 18:02

Alexis Rabago Carvajal

3755 silver badges13 bronze badges

Collectives™ on Stack Overflow

Hstore PostgreSQL Array comparision to any in array, Rails 3

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related