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 Category entries.
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 ) ProjectItems 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 needs here?
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.