Dynamic Collection Display Using Lookup Table Flags in Rails

Sometimes you need a longer subject to describe a post, so even though the current subject is ready long, here’s a better one: Using a Boolean Column in a SQL Has_Many Through: Table to Retrieve Filtered Collections in Rails.

Here’s the scenario.  We have Users and we have Stores, with Users having many Stores through a lookup table called User_Stores. User_Stores (our Rails UserStore class) contains user_id and store_id foreign keys to their respective tables. We also have a boolean column named “active” in User_Store that we want to use to display the User’s Active Stores by default and optionally all of their stores.

A picture is always best, so here’s the relationship breakout.

Our Model

Nothing much to report here other than establishing the usual has_many and through: relationships between Users and Stores. The two applicable models with configuration code for our purposes being User and UserStore.

With our has_many and through: relationships established we can retrieve a list of stores with

@stores = @user.stores

Active Stores Verses All Stores based on the UserStore.Active flag

We’ve arrived to the central issue, retrieving all stores or active stores only based on the boolean “active” field in the UserStore lookup table. We can make this hard or simple.  I wrote this post because I spent too much time making this hard when it turned out to be simple. My problem was that I was thinking in terms of creating a new class and new relationships. In reality we needed little more than a new method.

Let’s start at the end and show how we display all stores and active-only stores in the Controller.  Keep in mind that Stores belongs to User.

class StoresController < ApplicationController
before_filter :signed_in_user

def index
@user = User.find(params[:user_id])
@stores = @user.active_stores
end

def all
@user = User.find(params[:user_id])
@stores = @user.stores
end

We’re displaying active-only stores by default at /users/[user_id]/stores and added a new route and method to display both active and non-active stores at /users/[user_id]/stores/all.

The Method

And for the million dollar question (okay, the “ten buck question”), where did the @user.active_stores come from? This is a new method we added in our User Model.

def active_stores
self.stores.where(:user_stores => {:active => true})
end

The Route

One more thing to do, add a route in our /config/routes.rb file.  We’ll match up a new stores/all path with the “all” method in our StoreController.

match ‘users/:user_id/stores/all’ => ‘stores#all’,
:as => :user_stores_all, via: :get

Hope this helps someone who looks at creating dynamic collections based on a flag column in a lookup or “through” table as something more difficult than what it is.