How to sort list by column in a grandparent table?

Rails
1

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 How to paginate, sort and search a table with Ajax and Rails · dev.nozav.org 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
2

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

3

Cayce B. 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!

4

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.

5

Perfect! Thanks Chris.

6

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!

7

Chris G. wrote:

:include => [:artist, {:venue => :town}]

Learn a new thing every day. Very cool.

c.

8

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