Rails hStore postgresql activerecord database query?

Question

I want to be able to query an hStore column on PG database and return ONLY the values corresponding to a specific key.

The data looks like this: (keep in mind the :data is a postgresql hStore)

[ id: 2, data: {"tags"=>"Ruby, Objects", "type"=>"video", "title"=>"RubyTapas: Blocks, Procs, and Lambdas"}, 
  id: 3, data: {"tags"=>"JavaScript, Objects", "type"=>"video", "title"=>"RubyTapas: Blocks, Procs, and Lambdas"} ]

I want to do something to the effect of this:

Model.uniq.pluck(:tags)

And the output I expect is:

Ruby, Objects, JavaScript

*I am strictly trying to implement this with activerecord and postgresql.

MrPizzaFace · Accepted Answer · 2014-11-27 05:28:19Z

2

I was able to get my desired result successfully by doing:

Model.pluck(:data).map{|j| j['tags'].split(',') }.flatten.uniq

I am still interested in a solution which doesn't require to map over an activerecord relation. Please advise.

answered Nov 27, 2014 at 5:28

MrPizzaFace

8,12616 gold badges85 silver badges125 bronze badges

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

Comments

jjk · Accepted Answer · 2014-11-27 05:54:34Z

0

Via Ryan Bates

You can define a class eval that gives you access to the values:

 class Thing
      self.class_eval do
           %w[tags type title].each do |key|
           scope "has_#{key}", lambda { |value| where("payload @> hstore(?, ?)", key,           value) }

           define_method(key) do
                payload && payload[key]
           end

           define_method("#{key}=") do |value|
                self.payload = (payload || {}).merge(key => value)
           end
      end
 end

Now you can use

 t = Thing.find(2)
 t.type      <-"video"

Since pluck returns an array of field values, this should work (although I haven't tested it).

answered Nov 27, 2014 at 5:54

jjk

5267 silver badges15 bronze badges

Collectives™ on Stack Overflow

Rails hStore postgresql activerecord database query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related