Ah I see you're adopting both. I was going to proc the results as I goto to the *avals*.
puts htable.where(Sequel.hstore_op(:hstore_col).values.contains(['charlie'])).select_map(:id) Seeing that PostgreSQL didn't have any functions for hstore values, I felt like just using Ruby to sniff out the qualified records. But you've provided those right into the query. Good job. Cheers On Monday, August 21, 2023 at 2:44:34 PM UTC-4 Jeremy Evans wrote: > On Mon, Aug 21, 2023 at 11:02 AM bee...@gmail.com <bee...@gmail.com> > wrote: > >> OK, so I'm now even more confused. I am assuming that there is a direct >> way of asking for records whose hstore field has a single value. Is this a >> correct assumption? > > > A better question to ask yourself is: "What PostgreSQL function/operator > does what I want?" You can review > https://www.postgresql.org/docs/current/hstore.html to see the functions > and operators available. If no PostgreSQL function/operator does what you > want, that pg_hstore_ops is not going to be able to help you. Looking at > that page, it appears like you should be able to use something like this: > > DB.extension :pg_array > Sequel.extension :pg_hstore_ops, :pg_array_ops > > DB[:table].where(Sequel.hstore_op(:hstore_column).values.contains(["your_value"])) > # SELECT * FROM "table" WHERE (avals("hstore_column") @> ARRAY['your > value']) > > To break that down: > > * values comes from pg_hstore_ops extension > * contains comes from pg_array_ops extension > * The pg_array extension is needed to convert the ["your_value"] to a > PostgreSQL array > > >> I'm also looking at the methods here: >> http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_hstore_rb.html >> >> It shows a .has_value? method, which is exactly what I'm looking for. > > > These describe Ruby methods you can call on a Sequel::Postgres::HStore > instances, not functions/operators you can use in queries. > > It seems I have to grab that whole dataset and perform a proc looking for >> the value. >> > > That's always an option, though I think the above would work. It's also > possible to index the avals call so the query can use an index scan instead > of a sequential scan. > > 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 sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/73863a8c-a7ea-4662-9a3e-98f394c1258en%40googlegroups.com.
