Pothibo

Many to many relationships with Postgresql's hstore in Rails 4

It's been a while, hasn't it? Sorry, but life got in the way :(

But I'm back and I'm back with something p-r-e-t-t-y awesome. I started playing with hstore (postgresql) in some of my Rails project and found it to be very useful for handling many to many relationships.

Yup, hstore can be used to create many to many relationships in Rails!

My first attempt at doing this was a mess. Then I figure some things out and reworked the process until I had something that made sense.

Before I move on, I want to make sure you understand that this a glorified proof of concept. While I have a version of this working in production in two different projects, I don't know how it behaves when there's a huge load and neither did I test a whole bunch of situations. So test accordingly and if you find a caveat, I'd like to know about it!

Build a many to many relationships with hstore

There is one principle that you need to understand about hstore:

You can only query hstore's keys.

Given a model called Post with an hstore attribute :tags, the structure would need have the following structure:

p = Post.new p.tags = { '1': "The key are Tag.id", '2': "Right now, you can set everything as its value as you cannot query it...", '3': "Future version of hstore will make it possible to query hstore value through JSON manipulation" } p.tags.count => 3

I understand that taken out of the context, the example is lacking.

The goal is to have an interface within Post that would return an ActiveRecord object so we can either retrieve all tags or we can query the result even further; like a normal many to many relationship.

Still confusing? Let's build an example together.

Posts & tags

In this example, I'll build with you a relation between posts and tags. The goal is to have a many to many relationship where a posts can have multiple tags and tags can have multiple posts.

This will allow us to list all posts for a given tag or inversely, all tags for a given post.

Since hstore is still fairly new, I'll cover all the steps from the migration to the working relationship.

Generate the models

With rails' generate command, we can create the two models right from the console.

$ rails g model tag name:string $ rails g model post title:string

Before you can migrate your database, we'll need to tweak the post migration file to enable hstore on your postgresql's database.

class CreatePosts < ActiveRecord::Migration def change enable_extension 'hstore' create_table :posts do |t| t.string :title t.hstore :tags t.timestamps end end end

Migrating your database will now create a hstore key in your post model.

Create the many to many relationship

Because what we're doing is not part of Rails, we'll have to get our hand dirty a little bit.

Let's add the relation to Post:

class Post # Only way to persist changes before_save :persist_tags # This is similar to has_many as it tells rails to create a hstore accessor key for the specified attributes. store_accessor :tags # The scope uses hstore query type to retrieve post that contains the specific tag's id. More on this later. scope :tag, ->(tag) { where('posts.tags ? :key', key: tag.id.to_s) } # The lazy loader that makes the relation possible. # Because it's not a native relationship, we need to wrap it inside a PORO object. # There's also a call to super which retrieves the stored hstore value. def tags @tags ||= Tags.new((super || {})) end protected def persist_tags self.tags = self.tags.serialize end private # This private class is where the magic happens. It will store a relation that we'll be able to use to either iterate or add objects. class Tags def initialize(hash) @relation = Tag.where('tags.id in (?)', hash.keys) end def <<(tag) @relation << tag end def each(&block) @relation.each &block end def map(&block) @relation.map &block end def serialize hash = {} # Remember above I said the value was meaningless in our context? # I set it to 'true' but I could have set it to 'false' or anything else. # Just wanted to be consistent. @relation.each do |tag| hash[tag.id] = true end hash end end end

As for the Tag model, it's much simpler.

class Tag def posts # Let's use the scope we created above! @posts ||= Post.tag(self) end end

One thing that really stands out is the weird scope I created for the Post model. This is specific to how you query hstore keys in Postgresql. It will replace the :key from the first argument (the string), with the value of :key from the hash.

Another might be the private class I created. This is because the hstore relation that we're building is not part of Rails. Now, I believe it wouldn't be that hard to create a new ActiveRecord relations that would have the very same behaviour as the standard version. If someone feels like building a gem for this, let me know, I'll write about it here!

I could have also use a delegate pattern to delegate all ActiveRecord's method to the @relation ivar that is set. It's probably the best way to go but I wanted to keep the example as simple as possible.

Anyway, I hope you enjoy this small post about many to many relationships done with hstore! No extra table, no extra model!

I just love those kind of elegant solution where you can use new technology to make things work better!

Get more ideas like this to your inbox

You will never receive spam, ever.