Can search_cop work with Postgres hstore?

[DannyBen]

(somewhat related to #31)

I am evaluating my options in regards to having arbitrary data that is still searchable in Rails and Postgres. One option I am looking at is the Postgres hstore structure.

Is there any chance that this can work with search_cop?

Searching a table with hstore column, looks like this:

Profile.where("settings->'color' = ?", "yellow")
# => #<ActiveRecord::Relation [#<Profile id: 1, settings: {"color"=>"yellow", "resolution"=>"1280x1024"}>]>

I would love to have something much simpler in search_cop - specifically in the string search format - maybe like this:

Profile.search "settings.color = yellow and <some additional free text expressions>"

Or perhaps an alternative (additional) approach:

search_scope :search do
   attributes :color, :resolution
   options :color, type: :hstore, field: :settings
   options :resolution, type: :hstore, field: :settings
end

and then, we can just use the plain attribute name (without column name prefix):

Profile.search "color = yellow or resolution = 1024x486"

If this works - or can be made to work - this would be super cool.

[mrkamel]

hi, search_cop tries to be as database agnostic as possible and hstore is postgres only, but yeah, this can be made to work. Bit of a question is, as you already pointed out, how you specify the attributes, but it's definitely possible and i'd be willing to integrate it.

[DannyBen]

I might try to tinker with it next week. For my use case, this is better:

Profile.search "settings.color = yellow"

since the whole reason for using hstore, is to allow completely arbitrary fields, so I need to avoid the requirement to define them as a search scope option.

[DannyBen]

After looking at the code, and some failed attempts, I opted to ditch this hstore direction since it looks like too much work (mostly due to the fact that I am unfamiliar with the code).

If nobody else can / want to pick up this glove, feel free to close this issue (although I think it would be a cool feature still).

[mrkamel]

i'll dig into it.

[DannyBen]

i'll dig into it.

Thank you! And I love this gem, it literally saves me a lot of time, so I am more than willing to contribute and help where I can.

[mrkamel]

You can already take a look into https://github.com/mrkamel/search_cop/pull/60 The PR adds jsonb as well as hstore support.

Please note that it is not easily possible (by design) to allow access to arbitrary fields of an hstore/jsonb field by via some dot syntax (hstore_field.arbitrary_field: some_value). Instead, every accessable field needs to be specified explicitly.

[DannyBen]

Thanks for investing your time into it

Alright, I looked at the code. So, if I understand correctly it will allow me to use one of json stores, and still search inside it using searchcop standard syntax, assuming I declare them in the model like any other search cop field. Right?

If so, this is great!

[mrkamel]

it's available now