Okay, I know this is probably a stupid question, and maybe I should
know better, but I''m just stumped by this concept... it''s
probably
not just Ruby on Rails specific but this seems like a good place to
ask..
How do I write code in a controller that refers to a distant table in
my database? For example, my application has a table for "pages",
where pages belongs_to :category and then category
belongs_to :website ... So each website has_many categories and each
category has_many pages.
My question, how do I ask for pages belonging to one website? I''m
writing code like this in a controller method:
@pages = Page.find_all (''category_id = '' +
@params[:id],
''title'')
This is just fine for finding all pages in a category, but then how
do I reach all the way to the website table? I have written the
correct code in my models... am I even approaching this question the
right way?
Thanks once again for all help given. I''m a little slow at
programming, but surely get an A for effort!
Regards,
Raymond
On 4/26/05, Raymond Brigleb <ray-THGPwszTed5CpjqP0VxSwUEOCMrvLtNR@public.gmane.org> wrote:> How do I write code in a controller that refers to a distant table in > my database? For example, my application has a table for "pages", > where pages belongs_to :category and then category > belongs_to :website ... So each website has_many categories and each > category has_many pages. > > My question, how do I ask for pages belonging to one website? I''m > writing code like this in a controller method: > @pages = Page.find_all (''category_id = '' + @params[:id], > ''title'') > > This is just fine for finding all pages in a category, but then how > do I reach all the way to the website table? I have written the > correct code in my models... am I even approaching this question the > right way?Each `website` object will have a `categories` method, and each of those will have a `pages` method. So you''re gathering "for category in website.categories" and "for page in category.pages". Unless there really is something more direct I don''t know about. :)> Regards, > RaymondSincerely, Tom Reinhart tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org http://AllTom.com/
On 27.4.2005, at 05:53, Tom Reinhart wrote:> On 4/26/05, Raymond Brigleb <ray-THGPwszTed5CpjqP0VxSwUEOCMrvLtNR@public.gmane.org> wrote: >> How do I write code in a controller that refers to a distant table in >> my database? For example, my application has a table for "pages", >> where pages belongs_to :category and then category >> belongs_to :website ... So each website has_many categories and each >> category has_many pages. >> >> My question, how do I ask for pages belonging to one website? I''m >> writing code like this in a controller method: >> @pages = Page.find_all (''category_id = '' + @params[:id], >> ''title'')Two suggestions: 1) Use the new find(:all, ...) syntax. 2) Use bind variables. (see http://rails.rubyonrails.com/classes/ActiveRecord/Base.html#M000650 for the complete syntax) So your call would be @pages = Page.find(:all, :conditions => ["category_id = ?", @params[:id]])>> >> This is just fine for finding all pages in a category, but then how >> do I reach all the way to the website table? I have written the >> correct code in my models... am I even approaching this question the >> right way? > > Each `website` object will have a `categories` method, and each of > those will have a `pages` method. So you''re gathering "for category in > website.categories" and "for page in category.pages".This has the disadvantage of using a ton of independent sql queries and you still wouldn''t get a clean array of all pages belonging to a website. This can be accomplished with a small helper function, though: class Website def find_pages Page.find(:all, :conditions => ["websites.id = ?", self.id], :joins => "join categories on pages.category_id = categories.id join websites on categories.website_id = websites.id") end end Now you can call find_pages for any Website object and the proc will give you a nice array of pages. Note that you might need to require ''page'' so that website class can call Page.find. //jarkko> > Unless there really is something more direct I don''t know about. :) > >> Regards, >> Raymond > > Sincerely, > > Tom Reinhart > tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org > http://AllTom.com/ > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Jarkko, Okay, I finally got my head around your suggestion for handling my database problems. It seems to be very close to what I want, but I''m getting the "Cartesian sum" (I think they call it) in my results, meaning that I''m getting more than one "title," more than one "created_on", etc. ... It''s weird. I''m not sure how to fix it. The problem was:>> >>> How do I write code in a controller that refers to a distant >>> table in >>> my database? For example, my application has a table for "pages", >>> where pages belongs_to :category and then category >>> belongs_to :website ... So each website has_many categories and each >>> category has_many pages. >> ............. >And your kind suggestion:> This can be accomplished with a small helper function, though: > > class Website > def find_pages > Page.find(:all, :conditions => ["websites.id = ?", > self.id], :joins => "join categories on pages.category_id = > categories.id join websites on categories.website_id = websites.id") > end > end >Well, it''s close. I can run a query directly into the database (just to test and try to understand it) that looks like this: select * from pages join categories on pages.category_id = categories.id join websites on categories.website_id = websites.id where websites.id = 1 and it *does* give me the correct results, it''s just that the table seems to contain all of the result fields, so there''s three id fields, three title fields, and so forth, for each result. In my app, I do get the right *number* of results, but I can''t use them. So how should I narrow down this problem? Any ideas? I''m so close I can almost taste it!!!! Thanks again for your kind help, Raymond
OK, I figured out my SQL problem. I resorted to find_by_sql thusly: @pages = Page.find_by_sql(["select pages.* from pages join categories on pages.category_id = categories.id join websites on categories.website_id = websites.id where websites.id = ?", @mysite]) This does what I need it to do just fine. I think mainly I just needed to be able to specify "select pages.*" and for some reason the find() command doesn''t let you do that. Is that true? Weird. Another note: I believe the API docs incorrectly show the find_by_sql syntax as being okay with [] braces but not needing () parens around the parameter, but it seems that they do. At least, I was getting obscure errors until I put parenthesis around the parameter, around the braces. Is this a real error? Should I file it someplace? Kindest regards, Raymond>> This can be accomplished with a small helper function, though: >> >> class Website >> def find_pages >> Page.find(:all, :conditions => ["websites.id = ?", >> self.id], :joins => "join categories on pages.category_id = >> categories.id join websites on categories.website_id = websites.id") >> end >> end >> >> > Well, it''s close. I can run a query directly into the database > (just to test and try to understand it) that looks like this: > > select * > from pages > join categories on pages.category_id = categories.id > join websites on categories.website_id = websites.id > where websites.id = 1 > > and it *does* give me the correct results, it''s just that the table > seems to contain all of the result fields, so there''s three id > fields, three title fields, and so forth, for each result. In my > app, I do get the right *number* of results, but I can''t use them. > > > So how should I narrow down this problem? Any ideas? I''m so close I > can almost taste it!!!! > > Thanks again for your kind help, > Raymond > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >