In my app, I have generic Things and Projects. Each Thing belongs to a
global Category. Each Project has a budget for one or more available
My DB design is this (monospace alignment ahead):
Categories : [ id | name ]
Things : [ id | name | category_id ]
Projects : [ id | name ]
Budget_Items : [ project_id | category_id | budget ]
My rails app has one Model for each table above.
When the admin is editing a specific project, I need to be able to list
*all* categories and the budget amount (if any) assigned for that
category. In SQL, I can draw out this data with one query as:
SELECT Categories.name, ProjectItems.budget FROM
Categories LEFT OUTER JOIN (
SELECT budget, category_id FROM Budget_Items WHERE project_id = 1
ON Categories.id = ProjectItems.category_id
...but I'm not sure how to use the above SQL (if at all) with
ActiveRecord to get an object with #name and #budget methods.
Should I change my table structure to make this easier?
Is there some Model magic I'm missing that makes my tables meet my
Would anyone advise me to just iterate Categories.find_all and do a
unique Budget_Item lookup for each category?
Right now I'm using Categories.find_all and setting up a complex
map-to-Hash system to create custom pseudo-tuples, but it feels very
wrong. And way more work than I think it ought to be do accomplish this
kind of a goal.
Suggestions and Flames welcome. Thanks in advance for the help.