gistfile1.textile

In Thinking Sphinx, you can define fields and attributes from associations. For example:

class Forum < ActiveRecord::Base
  define_index do
    indexes creator.name, :as => :creator
    indexes topics.name, :as => :topics
    indexes topics.creator.name, :as => :topic_creators
    indexes topics.posts.creator.name, :as => :post_creators
  end
end

Creating the joins within ARel/ActiveRecord to get the relevant tables is easy enough:

Forum.joins(:creator).joins(:topics => [:creator, {:posts => :creator}])

It’s worth noting that if you simplified the second joins call into a couple, then the joins actually get duplicated – which is not ideal.

Forum.joins(:creator).joins(:topics => :creator).joins(:topics => {:posts => :creator})

Just to repeat – the above line of code is not the right way of doing things. But it’s not the issue I’m grappling with.

What I need to do is figure out the table aliases for each association, so I can have explicit references to columns (otherwise, any self respecting database would complain about the generated SQL). ARel/ActiveRecord does have this information, but it’s not in an easy-to-grok structure. Here’s some code:

def print_joins(relation, prefix = '')
  print_join 1, relation.relation1, prefix
  print_join 2, relation.relation2, prefix
end

def print_join(position, relation, prefix)
  if relation.respond_to?(:options)
    puts "#{prefix}#{position}: #{relation.name} AS #{relation.options[:as]}"
  else
    puts "#{prefix}#{position}: #{relation.name}"
    print_joins relation, prefix + '  '
  end
end

relation = Forum.unscoped
relation = relation.joins(:creator)
relation = relation.joins(:topics => [:creator, {:posts => :creator}])

print_joins relation.arel.relation.relation

# 1: forums
#   1: forums
#     1: forums
#       1: forums
#         1: forums AS 
#         2: users AS users
#       2: topics AS topics
#     2: users AS creators_topics
#   2: posts AS posts
# 2: users AS creators_posts

That may take a little time to understand, but what it comes down to is: when a relation is a join, it has two sub-relations – relation1 and relation2. relation2 never has any sub-relations, so all further joins are under relation1 (and under its relation1, and so on). It’s not a tree that matches all the associations from the perspective of the initial model.

Yes, all the joins are there, and the SQL that gets generated is correct. But I use a lot of concatentation and group functions on columns, hence why I have to be able to figure out what the table aliases are while generating the SELECT clause.

So, here’s what I actually want: a way to translate association chains (ie: topics.creator.name) to the relevant join object and/or table alias.

Any suggestions?

Hey Pat, not sure if this is what you're after, but check out the readme for Arel (http://github.com/rails/arel) from the section entitled "The Crazy Features" an on.

Two things i find useful (that might help your situation) are:

being able to obtain an arel representation of the table itself:

comments = Table(:comments)

and being able to explicitly alias said table:

replies = comments.alias

From the readme:

Arel will always produce a unique name for every table joined in the relation, and it will always do so deterministically to exploit query caching. Explicit aliasing is more common, however. When you want to extract specific slices of data, aliased tables are a necessity.

Nice suggestion Chris - unfortunately, that code is just for standard table references, not within the context of a relationship. I had a look at the code underneath this, just in case there's something there that's useful - but it's just an attr_reader.

Hi Pat:

I was also struggling with how to do a pair of joins on the same table. I asked a question on the RoR forum and then answered it myself:
http://www.ruby-forum.com/topic/619374
That may be some help. Short answer: AR invents aliases for you. But it's not clear how you discover what those aliases are, short of manually inspecting the results.