# How to sort list by column in a grandparent table?

**URL:** <https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499>\
**Category:** Rails\
**Created:** [November 10, 2006, 12:13pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499 "2006-11-10T12:13:09Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 12:13pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/1 "2006-11-10T12:13:09Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 4:14pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/2 "2006-11-10T16:14:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 4:47pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/3 "2006-11-10T16:47:23Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 6:17pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/4 "2006-11-10T18:17:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 7:24pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/5 "2006-11-10T19:24:04Z")

</div>

Perfect! Thanks Chris.

---

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 6:45pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/6 "2006-11-10T18:45:58Z")

</div>

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](http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html)  
for more details.

Hope this helps!

---

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 7:43pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/7 "2006-11-10T19:43:34Z")

</div>

Chris G. wrote:

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

Learn a new thing every day. Very cool.

c.

---

<div class="post-metadata">

**Author:** ![sandy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/2bfe46/32.png) [@sandy](https://www.ruby-forum.com/u/sandy)\
**Post date:** [November 10, 2006, 8:57pm UTC](https://www.ruby-forum.com/t/how-to-sort-list-by-column-in-a-grandparent-table/75499/8 "2006-11-10T20:57:58Z")

</div>

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](http://habtm.com/articles/2006/02/21/eager-loading-with-cascaded-associations)