Hi All, I''m building an application that lists events (music gigs) along with the corresponding venues and towns where they are happening. I followed the tutorial at http://dev.nozav.org/rails_ajax_table.html and can get the list to sort by all columns except for by town, which is a parent of venues, which in turn is a parent of gigs. The relevant tables are set up as follows: gigs ( id artist_id venue_id date ) venues ( id name town_id ) towns ( id name ) The gigs_controller.rb is then set up as follows def list gigs_per_page = 20 sort = case @params[''sort''] when "artist" then "artists.artist_name" when "venue" then "venues.venue_name" # broken when "town" then "venues.towns.town_name" when "date" then "date_time" when "artist_reverse" then "artists.artist_name DESC" when "venue_reverse" then "venues.venue_name DESC" # broken when "town_reverse" then "venues.towns.town_name DESC" when "date_reverse" then "date_time DESC" end @gigs_pages, @gigs = paginate :gigs, :order => sort, :conditions => "date_time >= now()", :per_page => gigs_per_page, :include => [:artist, :venue] end So, what I want to do is sort the list according to the name of the town in the same way as the other columns - any suggestions? Cheers, - Sandy -- 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 -~----------~----~----~----~------~----~------~--~---
Change the following lines:> :include => [:artist, :venue]should be :include => [:artist, :venue, :town]> # broken when "town" then "venues.towns.town_name"should be when "town" then "towns.town_name"> # broken when "town_reverse" then "venues.towns.town_name DESC"should be when "town_reverse" then "towns.town_name DESC" And it should work for you. Explanation... The values in your sort variable represent SQL fragments, they are not Ruby objects/variables. What I mean is, in this: when "venue" then "venues.venue_name" the "venues.venue_name" is not a Ruby statement accessing the "venue_name" method of the "venues" object. It is a fragment of a larger sql statement that Rails builds for you. Rails takes your "sort" value and tacks it onto the statement after "order by", like this: select * from gigs gigs ,venue venues where venues.id = gigs.venue_id order by venues.venue_name <<<< here''s your sort variable value Rails wouldn''t normally include the venues table in the select statement, but you are telling it to do so with the :include parm of the paginate function. For what you want to do in the broken parts, your statement would look like this: select * from gigs gigs ,venues venues ,towns towns where venues.id = gigs.venue_id and towns.id = venues.town_id order by towns.town_name So, before you can sort by anything on the town table, you need to tell Rails to include it in the select, by adding :town to the :include parm. Once you do that, you know that rails is including the towns table, aliased as "towns" (go figure), and you can use any field from towns in your sort with "towns.fieldname". Sorting, especially on a multi-table join like this, is one of those areas where Rails cannot completely abstract you away from the SQL, so if you are not versed in SQL or the nuts/bolts of how Rails builds its SQL, this might be one of the more difficult areas you deal with in Rails. c. Sandy wrote:> > sort = case @params[''sort''] > when "artist" then "artists.artist_name" > when "venue" then "venues.venue_name" > # broken when "town" then "venues.towns.town_name" > when "date" then "date_time" > when "artist_reverse" then "artists.artist_name DESC" > when "venue_reverse" then "venues.venue_name DESC" > # broken when "town_reverse" then "venues.towns.town_name DESC" > when "date_reverse" then "date_time DESC" > end > > @gigs_pages, @gigs = paginate :gigs, > :order => sort, > :conditions => "date_time >= now()", > :per_page => gigs_per_page, > :include => [:artist, :venue] > end >-- 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 -~----------~----~----~----~------~----~------~--~---
Cayce Balara wrote:> Change the following lines: > >> :include => [:artist, :venue] > should be :include => [:artist, :venue, :town] > >> # broken when "town" then "venues.towns.town_name" > should be when "town" then "towns.town_name" > >> # broken when "town_reverse" then "venues.towns.town_name DESC" > should be when "town_reverse" then "towns.town_name DESC" > > And it should work for you.Hi Cayce, Thanks for the reply and explanation. Making the changes as you suggest results in the following response: "Association named ''town'' was not found; perhaps you misspelled it?" Presumably this is because there is no ''belongs_to :town'' in the gig.rb model - if I put this in (which I''m not sure is correct) then the error changes to: Mysql::Error: Unknown column ''gigs.town_id'' in ''on clause'': SELECT COUNT(DISTINCT gigs.id) FROM gigs LEFT OUTER JOIN artists ON artists.id = gigs.artist_id LEFT OUTER JOIN venues ON venues.id = gigs.venue_id LEFT OUTER JOIN towns ON towns.id = gigs.town_id So all I think needs changing is ''gigs.town_id'' should really be ''venues.town_id'' - I just cant get my head around how? The way I have it in my head is that many gigs belong to one venue, and then many venues belong to one town so there is no direct association between gigs and towns other than via a venue..? Thanks again! -- 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 -~----------~----~----~----~------~----~------~--~---
Sandy wrote:> Hi Cayce, > > Thanks for the reply and explanation. Making the changes as you suggest > results in the following response: > > "Association named ''town'' was not found; perhaps you misspelled it?" > > Presumably this is because there is no ''belongs_to :town'' in the gig.rb > model - if I put this in (which I''m not sure is correct) then the error > changes to: > > Mysql::Error: Unknown column ''gigs.town_id'' in ''on clause'': SELECT > COUNT(DISTINCT gigs.id) FROM gigs LEFT OUTER JOIN artists ON artists.id > = gigs.artist_id LEFT OUTER JOIN venues ON venues.id = gigs.venue_id > LEFT OUTER JOIN towns ON towns.id = gigs.town_id > > So all I think needs changing is ''gigs.town_id'' should really be > ''venues.town_id'' - I just cant get my head around how? > > The way I have it in my head is that many gigs belong to one venue, and > then many venues belong to one town so there is no direct association > between gigs and towns other than via a venue..? > > Thanks again!Ahhh, now that is tricky. You are correct that :include requires that you have the necessary associations in place. And yes, also - gig.town_id should be venues.town_id. The :include is telling Rails to associate :town to :gig and that''s not what you want. I understand and agree with your associations - town has venues, venue has gigs. hmmmmm... My first thought is that maybe use: gig has_many :venues gig has_many :towns, :through => :venues town belongs_to :gig, :through => :venues ... and see if Rails knows how to build it appropriately. I haven''t done a lot of the has_many :through code yet, and I don''t have an environment at hand to test/debug this out for you. But, my expectation is that would be the way to do it - give it a shot and post your results and I can dive a little deeper on it this eve. It''s certainly an interesting wrinkle. c. -- 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 -~----------~----~----~----~------~----~------~--~---
I don''t think has_many :through is what you want here. I believe what you are looking for is: Models: class Gig < ActiveRecord::Base belongs_to :artist belongs_to :venue end class Venue < ActiveRecord::Base has_many :gigs belongs_to :town end class Town < ActiveRecord::Base has_many :venues end Controller: class GigsController < ApplicationController [...] def list gigs_per_page = 20 sort = case @params[''sort''] when "artist" then "artists.artist_name" when "venue" then "venues.venue_name" when "town" then "town.town_name" when "date" then "date_time" when "artist_reverse" then "artists.artist_name DESC" when "venue_reverse" then "venues.venue_name DESC" when "town_reverse" then "town.town_name DESC" when "date_reverse" then "date_time DESC" end @gigs_pages, @gigs = paginate :gigs, :order => sort, :conditions => "date_time >= now()", :per_page => gigs_per_page, :include => [:artist, {:venue => :town}] end [...] end See the section "Eager loading of associations" at http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html for more details. Hope this helps! -- 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 -~----------~----~----~----~------~----~------~--~---
Perfect! Thanks Chris. -- 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 -~----------~----~----~----~------~----~------~--~---
Chris Gernon wrote:> :include => [:artist, {:venue => :town}]Learn a new thing every day. Very cool. c. -- 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 -~----------~----~----~----~------~----~------~--~---
Glad to help! I just realized the official API documentation I linked above actually doesn''t talk about cascaded eager loading. Looks like that''s another feature that got added without really being documented yet. For more details and examples, see this blog post by the person who actually contributed this feature: http://habtm.com/articles/2006/02/21/eager-loading-with-cascaded-associations -- 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 -~----------~----~----~----~------~----~------~--~---