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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Phrogz wrote:> > 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 ] >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.. Caveat.. NOT TESTED ilan -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> > 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.. > >project.categories.each do |cat| cat.budget_items.each {|bi| puts bi.budget} end ilan -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 > endClose - 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---