Hi, I have two models, Place and Region. This seems like a good place to have a composite primary key, but seems that Rails doesnt support it. class Place belongs_to :region ... end class Region has_many :places ... end That would be fine, except the data I''m working with and will be importing has the following columns, with the levels representing a level of region (such as state/province, county, district, etc.) Region: country_id, level1, level2, name Place: id, name, ... level1, level2 .. This raw SQL works for displaying the place along with the name of it''s region. How do I achieve the same result through the ActiveRecord Place.find(:all, :include => :region)? SELECT places.id, places.name, places.country_id, countries.name, regions.name FROM places INNER JOIN regions ON places.country_id = regions.country_id AND places.admin1 = regions.level1 AND places.admin2 = regions.level2 LEFT JOIN countries ON places.country_id = countries.id Thanks! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Avishai wrote:> class Place > belongs_to :region > ... > end > > class Region > has_many :places > ... > end > > How do I achieve the same result through the ActiveRecord > Place.find(:all, :include => :region)? > > SELECT places.id, places.name, places.country_id, countries.name, > regions.name > FROM places > INNER JOIN regions ON places.country_id = regions.country_id > AND places.admin1 = regions.level1 > AND places.admin2 = regions.level2 > LEFT JOIN countries ON places.country_id = countries.id > > Thanks!It feels to me like your data model is wrong. Might work better if you had Region: id, name, parent_id, country_id, type (County, Town, ...) Country: id, name. Place: id, name region_id (Country could just be a special case of Region, but maybe you want it seperate somehow) Country: United Kingdom | |- Region: England | | |----- Region: Greater London | | |-----------Region: Shepherds Bush | | |----------------Region: West 12 Shopping Centre | Place: "Vue Cinema - Shepherds Bush" Although that wouldn''t be cheap to grab all the parents. Dunno, depends why you need this relationship. -- 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 -~----------~----~----~----~------~----~------~--~---
Thanks. In the end I decided to settle for something in between, since I don''t really need a very deep parent/child structure. I wrote a rake task that goes and fetches the region id (autonumbered) and inserts it into the region_id column, so that it''s easier for rails to understand. Since the underlying data won''t change much, this seemed to be a decent solution that''s easily adaptable to rails. -Avishai On May 14, 2:19 pm, Matthew Rudy Jacobs <rails-mailing-l...@andreas- s.net> wrote:> Avishai wrote: > > class Place > > belongs_to :region > > ... > > end > > > class Region > > has_many :places > > ... > > end > > > How do I achieve the same result through the ActiveRecord > > Place.find(:all, :include => :region)? > > > SELECT places.id, places.name, places.country_id, countries.name, > > regions.name > > FROM places > > INNER JOIN regions ON places.country_id = regions.country_id > > AND places.admin1 = regions.level1 > > AND places.admin2 = regions.level2 > > LEFT JOIN countries ON places.country_id = countries.id > > > Thanks! > > It feels to me like your data model is wrong. > > Might work better if you had > > Region: > id, > name, > parent_id, > country_id, > type (County, Town, ...) > > Country: > id, > name. > > Place: > id, > name > region_id > > (Country could just be a special case of Region, but maybe you want it > seperate somehow) > > Country: United Kingdom > | > |- Region: England > | | > |----- Region: Greater London > | | > |-----------Region: Shepherds Bush > | | > |----------------Region: West 12 Shopping Centre > | > Place: "Vue Cinema - Shepherds Bush" > > Although that wouldn''t be cheap to grab all the parents. > > Dunno, > depends why you need this relationship. > -- > Posted viahttp://www.ruby-forum.com/.- Hide quoted text - > > - Show quoted text ---~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Avishai wrote:> Thanks. In the end I decided to settle for something in between, since > I don''t really need a very deep parent/child structure.> -Avishai > > On May 14, 2:19�pm, Matthew Rudy Jacobs <rails-mailing-l...@andreas-Excellent Avishai. Good luck with the project. I fear sometimes my data models are too crazy to work with. MatthewRudy -- 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---