thr3ads.net - Rails - How to sort list by column in a grandparent table? [Nov 2006]

If this information is useful, please help other people find it:
Share via:

Sandy

2006-Nov-10 11:13 UTC

How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Cayce Balara

2006-Nov-10 15:14 UTC

head link

Re: How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Sandy

2006-Nov-10 15:47 UTC

head link

Re: How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Cayce Balara

2006-Nov-10 17:17 UTC

head link

Re: How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Chris Gernon

2006-Nov-10 17:45 UTC

head link

Re: How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Sandy

2006-Nov-10 18:24 UTC

head link

Re: How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Cayce Balara

2006-Nov-10 18:43 UTC

head link

Re: How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Chris Gernon

2006-Nov-10 19:57 UTC

head link

Re: How to sort list by column in a grandparent table?

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
-~----------~----~----~----~------~----~------~--~---

Rails - Nov 2006 - How to sort list by column in a grandparent table?

How to sort list by column in a grandparent table?

Re: How to sort list by column in a grandparent table?

Re: How to sort list by column in a grandparent table?

Re: How to sort list by column in a grandparent table?

Re: How to sort list by column in a grandparent table?

Re: How to sort list by column in a grandparent table?

Re: How to sort list by column in a grandparent table?

Re: How to sort list by column in a grandparent table?