Query multiple key values with Rails + Postgres hstore

Question

I am trying to make a query to search for values in my hstore column properties. I am filtering issues by user input by attribute. It is possible to search Issues where email is X, or Issues where email is X and the sender is "someone". Soon I need to change to search using LIKE for similar results. So if you know how to do it with LIKE also, show both options please.

If I do this:

Issue.where("properties @> ('email => pugozufil@yahoo.com') AND properties @> ('email => pugozufil@yahoo.com')")

it returns a issue.

If I do this:

Issue.where("properties @> ('email => pugozufil@yahoo.com') AND properties @> ('sender => someone')")

Here I got an error, telling me:

ERROR:  Syntax error near 'd' at position 11

I change the "@>" to "->" and now this error is displayed:

PG::DatatypeMismatch: ERROR: argument of AND must be type boolean, not type text

I need to know how to query the properties with more than one key/value pair, with "OR" or "AND", doesn't matter.

I wish to get one or more results that include those values I am looking for.

Answer 1

I end up doing like this. Using the array option of the method where. Also using the suggestion from @anusha in the comments. IDK why the downvote though, I couldn't find anything on how to do something simple like this. I had doubt in formatting my query and mostly with hstore. So I hope it helps someone in the future as sure it did for me now.

if params[:filter].present?
    filters = params[:filter]
    conditions = ["properties -> "]
    query_values = []
    filter_query = ""
    filters.each do |k, v|
      if filters[k].present?
        filter_query += "'#{k}' LIKE ?"
        filter_query += " OR "
        query_values << "%#{v}%"
      end
    end
    filter_query = filter_query[0...-(" OR ".size)] # remove the last ' OR '
    conditions[0] += filter_query
    conditions = conditions + query_values
    @issues = @issues.where(conditions)
end