ShowMeTheMoney-16 Let's play tag the expense with a category

Well I know its been a long time... I took a break.. I got married in it. Long story short... its been a while. But now let's end what I started
Basically we want to tag each expense with some text. Not much time would be spent on the updating and deleting categories. If we can just create categories and select existing ones, we should be good. We’ll need to get some confirmation from the user on this.. So we’ll defer the delete for sometime.
So basically a dropdown with existing categories – that is editable. So while entering the expense entry, the user can either choose an existing category or add a new one.

I wrote up an acceptance test. Although the former is more readable (being a DoFixture.. however I find that FitLibrary isn't ported yet for Ruby) So we have to make do with the corresponding ActionFixture.. more verbose but will do.

First lets add a migration to add the categories table and add a foreign key in the expenses table to refer to the former.
> ruby script/generate migration add_categories
This will create the corresponding file in db\migrate folder.

class AddCategories < ActiveRecord::Migration
def self.up
create_table :categories do |table|
table.column :name, :string, :limit => 80
execute "INSERT INTO categories VALUES (1, 'General');"
add_column :expenses, :category_id, :integer, {:null=>false, :default =>1}
execute "alter table expenses add constraint fk_expenses_to_categories foreign key(category_id) references categories(id)"


def self.down

execute "ALTER TABLE expenses DROP FOREIGN KEY fk_expenses_to_categories"
remove_column :expenses, :category_id
drop_table :categories


Now to run this migration against all three databases dev, prod and test
> rake db:migrate RAILS_ENV="test"

Add the category model with the unit tests for that one. We focus only on creation and retrieval .. don’t see any edits or deletes happening for categories

class CategoryTest < Test::Unit::TestCase
def test_create
def test_saveWithoutCategoryName_fails.

Now onto expense_test.rb
We need to
  • create the expense category if it does not exist
  • use the existing category if possible
  • ensure that the associated category is retrieved from the plot
the first two should be the responsibility of the outflow_controller
create categories.yml first. update expense model tests one at a time to account for the category.
Update OutflowController to create or use-existing category on creation of expense entry.

class OutflowController < ApplicationController

auto_complete_for :category, :name

def create
raise ArgumentError, "Category missing!", caller[1..-1] if (params[:category].nil? || params[:category][:name].nil?)

sCategoryName = (params[:category][:name]).strip
category = Category.find(:first, :conditions=>["name = ?", sCategoryName])
category = Category.create( {:name => sCategoryName} ) if (category.nil?)
params[:expense][:category_id] =

@expense =[:expense])
if (
flash[:notice] = 'Expense entry saved'
redirect_to :action=>'list'
render :action=>'new'

Update views to have an auto-completing field for the user to enter the category for an expense. This is a one-line addition in the controller as shown below coupled with the following in the view.

<td> <%= getAppString(:label_category) %> </td>
<%= text_field_with_auto_complete :category, :name%>

Right on! Let’s complete the implementation required for the second part of our acceptance test.

class ExpenseGetTopNCategoriesTest < Test::Unit::TestCase
fixtures :expenses, :categories

def test_getTopN_categories
obCategories = Expense.get_top_n_categories

assert_equal 3, obCategories.length
assert_equal categories(:rent_category).name, obCategories[0].name
assert_in_delta expenses(:rent_expense_entry).amount, obCategories[0].total_amount, 0.01

assert_equal categories(:apparel_category).name, obCategories[1].name
assert_in_delta expenses(:apparel_expense_entry).amount, obCategories[1].total_amount, 0.01

assert_equal categories(:entertainment_category).name, obCategories[2].name
assert_in_delta expenses(:movies_expense_entry).amount + expenses(:another_movie_expense_entry).amount,
obCategories[2].total_amount, 0.01


run ruby script/server –s
a little playing around helps me settle on this query
Expense.find_by_sql("SELECT, sum(amount) as total_amount
from expenses
join categories on category_id =
group by category_id
order by total_amount desc")

Stackoverflow users help me to convert this into Rails Model speak

Expense.find(:all, :select =>

" name, sum(amount)
:joins => "inner join categories on category_id =
:group => "category_id",
:order => "total_amount desc"

That turns out fine. I add more tests and a couple of more tables to the acceptance test to retrieve top n categories, top categories in a date range, top n categories in a date range. Soon enough...

Check in everything. Repeat the same for tagging inflows with categories. Should be easy. Until next time...

No comments:

Post a Comment