Development

Automatic Scopes and Indexing Hstore

Joe Hirn — Tandem Alum

last updated June 4, 2013

It’s been a while since I blogged about Single Table Inheritance. You may wonder how that’s been working out for me? Well, it’s been fantastic! We currently have 7 subclasses each with their own attributes. On boarding our new dev was smooth (read, we weren’t too clever) and performance has been great. It’s the most heavily used table in our database loading 100s per page with automatic scrolling and refreshing and cocktail onions, etc… We did have a couple additions over the months with and I’d like to share those with you.

Indexing

It turns out indexing these hstore attributes was not a problem. Almost boring in fact.

  execute %Q[
  CREATE INDEX ""
  ON 
  USING btree
  (
    type,
    (data->'my_attribute')
  );
]

Yawn.

The above is a btree, compound index on my STI table that indexes by the type of the object and the ‘my_attribute’ attribute of my hstore column, ‘data’. It only creates a string index. I had attempted typing the index as an integer but I could not get ActiveRecord to generate SQL which used the index. I think this has to do with the way the postgres adapter generates queries, but I didn’t have the time or incentive to dive in. My client appreciates that =)

There are other indexes for full text search (GIN and GIST) as well as hash indexes so make sure you look up the one that’s right for your needs.

Generating Scopes

One other thing I wanted to avoid was writing that odd looking query syntax to filter out by hstore attributes. Let’s take a look:

Model.where(" -> ''=''")

And if you want the less vulnerable version.

Model.where(" -> ''=?, ")

Um… that’s nice. We can do better!

To prevent this from being sprinkled all over the code base we added a line to our HstoreAccessor module to generate as scope for each attribute you pass to it. I chose to name the scopes “for<attributename>”, but that’s just my taste. Change that part if you don’t like the scope names, but chaining scopes is much nicer looking than having to write predicates with the syntax above.

Wouldn’t you agree?

Model.for_attribute1("value1")
Model.for_is_this_better(true).for_how_much_better(1000)

The code to generate the scope.

send(:scope, "for_#{key}", -> value { where("#{hstore_attribute} -> '#{key}'=?", value.to_s)})

Put this right inside of HstoreAccessor.store_accessor method like so and you’re good to go.

module HstoreAccessor
  def self.included(base)
    base.extend(ClassMethods)
  end

  module ClassMethods
    def hstore_accessor(hstore_attribute, *keys)
      Array(keys).flatten.each do |key|
        define_method("#{key}=") do |value|
          send("#{hstore_attribute}=", (send(hstore_attribute) || {}).merge(key.to_s => value))
          send("#{hstore_attribute}_will_change!")
        end
        define_method(key) do
          send(hstore_attribute) && send(hstore_attribute)[key.to_s]
        end
        ###Right here
        send(:scope, "for_#{key}", -> value { where("#{hstore_attribute} -> '#{key}'=?", value.to_s)})
      end
    end
  end
end

ActiveRecord::Base.send(:include, HstoreAccessor)

Now because I balked on type safety, everything is cast to a string. You could try leaving off the to_s call on value or casting it to whatever data type the column is cast to, but I didn’t need it… sorry. We are using this strictly for storing text data and a few ID’s which perform well enough being stored as strings.

Thank you for reading. I look forward to your comments!

Tandem – software development companies in Chicago with practice areas in digital strategy, human-centered design, UI/UX, and web application and custom mobile development.

Joe Hirn