Design Advice Wanted: modeling a left outer join

rubyonrails-talk
1

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.

2

Ilan Berci wrote:

Phrogz wrote: > 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

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:

Category.find_all.each{ |cat|   budget_item = BudgetItem.find( :project_id=>1, :category_id=>cat.id )   puts cat.name, budget_item ? budget_item.budget : '-' }

...but I'm trying to find a solution that doesn't involve N+1 SQL queries for N Categories.