On Sunday, September 7, 2014 3:09:44 AM UTC-7, Lucas wrote:
>
> Hi!
>
> I was wondering if there is more elegent way to filter using values stored
> in hstore column.
>
> I have an Entry model (Sequel::Model) that I want to filter basing on
> values in :status column (of :hstore type).
>
> My flags are: :processed, :duplicate, :eol.
>
> I found out that query:
> Entry.where("status -> 'processed' = 'true'")
> produces
> SELECT * FROM \"entries\" WHERE (status -> 'flag_processed' = 'true')
> and works as expected, but just doesn't feel right to pass in a string as
> an argument.
>
It's also generally a bad idea as it won't use an index.
> Using:
> Entry.where(status: Sequel.hstore(processed: true))
> produces:
> SELECT * FROM \"entries\" WHERE (\"status\" =
> '\"processed\"=>\"true\"'::hstore)
> and although does not return error, doesn't return any values either.
>
That's because it only matches if the hstore only contains a processed key
with a true value, not any other keys.
You probably want to use the pg_hstore_ops extension:
Sequel.extesion :pg_hstore_ops
Entry.where(Sequel.hstore_op(:status).contains(processed: true))
In addition to working, this can also use a GIN or GIST index on the status
column.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
