Unpredictable aliases of tables relationships

rubyonrails-talk
1

This issue is slightly complex so here is a bit of background:

I'm developing a wrapper that allows you to easily integrate extjs with rails. At the moment i'm developing with the "grid widget".

The point of the widget is to allow the user a customizable sortable, filterable, paginated grid that works with relationships with the minimum of setup.

To set one of these grids up you follow the steps:

a) Setup a configuration yaml file which defines a "base model" to work with, followed by all the "columns" to be displayed in the grid using the relationship path from the base model to define where to fetch the data from.

employee_addresses:   model: Person   store:     - gender     - birthday     - age     - email     - home_address.street_no     - home_address.street_name     - home_address.street_type     - home_address.suburb.name     - home_address.suburb.post_code     - work_address.suburb.name

b) In the controller you simply perform a search applying security conditions etc, create a Grid object, and pass that to a view helper which sets up the html and javascript. (note at this stage, the named_filter filter does not do anything when first viewing, it only filters after filter conditions start coming in via xhr)

def myaction

  people = Person.filter('employee_addresses', params[:filter]).find(:all,                         :include => [{:home_address => :suburb},{:work_address => :suburb}],                         :conditions => 'some_extra_security_stuff',                         :limit => 10)   @grid = Grid.new(:name => 'employee_addresses',                    :collection => people)   respond_to do |format|     format.html { render :action => :grid_model_test }     format.json do       render :text => @grid.data.to_json     end   end end end

c) The user then using the js interface selects some filtering conditions and this gets passed via params into the controller in the form something like field_type: string field_name: home_address.suburb.name conditions: equal value: my_home_suburb

This then gets passed into the filter in which i have to construct the appropriate sql conditions.

The issue I have been struggling over is that I can not predict what the table name will be aliased by activerecord. I have to be able to convert 'home_address.suburb.name' into suburb.name. but in some cases it activerecord aliases that to suburbs_addresses.name.

From what I can tell, the first time the relationship is used it uses "suburbs" with no alias, then "suburbs_addresses". But in another field i've noticed that it's used the relationship name "home_addresses_people".

Here is an example of the generated sql: FROM `people` LEFT OUTER JOIN `addresses` ON `addresses`.id = `people`.work_address_id LEFT OUTER JOIN `suburbs` ON `suburbs`.id = `addresses`.suburb_id LEFT OUTER JOIN `addresses` home_addresses_people ON `home_addresses_people`.id = `people`.home_address_id LEFT OUTER JOIN `suburbs` suburbs_addresses ON `suburbs_addresses`.id = `home_addresses_people`.suburb_id WHERE (home_address.suburb.suburb like 'my_home_suburb')

Is there anyway I can make the aliasing of the tables predictable? .. i.e I know that it will always use a specific alias

One solution i have considered (although it's a little extreme, and might break apps using the plugin) is to force active record to alias all fields based on their relationships. i.e. suburbs would become home_address_suburb, work_address_suburb etc

Any help will be greatly appreciated as i've been struggling with this issue, i've already got a fully working solution that works without filtering

2

The doc has this to say:

     # ActiveRecord uses table aliasing in the case that a table is
referenced multiple times in a join. If a table is referenced only
once,      # the standard table name is used. The second time, the table is
aliased as <tt>#{reflection_name}_#{parent_table_name}</tt>. Indexes
are appended      # for any more successive uses of the table name.

3

Frederick Cheung wrote:

field `home_addresses_people`.id = `people`.home_address_id LEFT OUTER JOIN `suburbs` suburbs_addresses ON `suburbs_addresses`.id = `home_addresses_people`.suburb_id WHERE (home_address.suburb.suburb like 'my_home_suburb')

The doc has this to say:

     # ActiveRecord uses table aliasing in the case that a table is referenced multiple times in a join. If a table is referenced only once,      # the standard table name is used. The second time, the table is aliased as <tt>#{reflection_name}_#{parent_table_name}</tt>. Indexes are appended      # for any more successive uses of the table name.      #      # Post.find :all, :include => :comments      # # => SELECT ... FROM posts LEFT OUTER JOIN comments ON ...      # Post.find :all, :include => :special_comments # STI      # # => SELECT ... FROM posts LEFT OUTER JOIN comments ON ... AND comments.type = 'SpecialComment'      # Post.find :all, :include => [:comments, :special_comments] # special_comments is the reflection name, posts is the parent table name      # # => SELECT ... FROM posts LEFT OUTER JOIN comments ON ... LEFT OUTER JOIN comments special_comments_posts There are some more examples in the docs. Fred

Fred, cheers I didn't notice that before. I suppose in that case i'll always need access to the "include" hash in the filter as well to be able to work out how each table is aliased.

people = Person.filter('employee_addresses',params[:filter], :include => [{:home_address => :suburb}).find(:all,                         :include => [{:home_address => :suburb}, {:work_address => :suburb}],                         :conditions => 'some_extra_security_stuff',                         :limit => 10)