Design Advice Wanted: modeling a left outer join

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.

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.