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.
Categories : [ id | name ]
Things : [ id | name | category_id ]
Projects : [ id | name ]
Budget_Items : [ project_id | category_id | budget ]
I assume that you have the following model:
class Category < ActiveRecord::Base
has_many :budget_items
has_many :things
end
class Thing < ActvieRecord::Base
belongs_to :category
end
class Project < ActiveRecord::Base
has_many :categories
has_many :budget_items
end
class BudgetItem < ActiveRecord::Base
belongs_to :project
belongs_to :category
end
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
project = Project.find(1)
project.categories.each {|cat| p cat.name }
project.budget_items.each {|bi| p bi.budget}
Hope this helps and that I have satisfied your problem domain…
end
class BudgetItem < ActiveRecord::Base
belongs_to :project
belongs_to :category
end
Close - I don’t have has_many :categories for a Project, because there
is no relationship between a project and a category, except through the
budget_items that exist for the project.
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:
project = Project.find(1)
project.categories.each {|cat| p cat.name }
project.budget_items.each {|bi| p bi.budget}
Nope, no project.categories. The best I have found is: