Multiple associations to same table with alias

Question

I have a legacy application not written in Ruby. The legacy app uses a SQL Server DB and my Rails app needs to access this legacy DB.

The database has a User table, ViewKey table, and two bridge tables that implement two many-to-many relationships between User and ViewKey. The bridge tables are UserViewKeyAllowed and UserViewKeyDenied. So each user may have multiple view keys they are allowed to access and multiple view keys they are denied access.

Here are the models I have defined:

class User < ActiveRecord::Base
  self.table_name = 'tblUser'
  attribute_names.each { |attr| alias_attribute attr.underscore, attr }

  has_many :user_groups, foreign_key: :tblUserID
  has_many :user_view_key_alloweds, foreign_key: :tblUserID
  has_many :user_view_key_denieds, foreign_key: :tblUserID
  has_many :groups, through: :user_groups

  has_many :view_key_alloweds, through: :user_view_key_alloweds, source: :view_key
  has_many :view_key_denieds, through: :user_view_key_denieds, source: :view_key    
end

class ViewKey < ActiveRecord::Base
  self.table_name = 'tblViewKey'
  attribute_names.each { |attr| alias_attribute attr.underscore, attr }

  has_many :group_view_keys, foreign_key: :tblViewKeyID
  has_many :groups, through: :group_view_keys

  has_many :user_view_key_alloweds, foreign_key: 'tblViewKeyID'
  has_many :user_view_key_denieds, foreign_key: 'tblUserID'
end

class UserViewKeyDenied < ActiveRecord::Base
  self.table_name = 'tblUserViewKeyDenied'
  attribute_names.each { |attr| alias_attribute attr.underscore, attr }

  belongs_to :view_key, primary_key: 'ID', foreign_key: 'tblViewKeyID'
  belongs_to :user, primary_key: 'ID', foreign_key: 'tblViewKeyID'
end

class UserViewKeyAllowed < ActiveRecord::Base
  self.table_name = 'tblUserViewKeyAllowed'
  attribute_names.each { |attr| alias_attribute attr.underscore, attr }

  belongs_to :view_key, primary_key: 'ID', foreign_key: 'tblViewKeyID'
  belongs_to :user, primary_key: 'ID', foreign_key: 'tblUserID'
end

I've got the "has_many: ... through:" association working and the following Ruby seems to query the data correctly:

user = User.where(ID: 116).eager_load(:view_key_alloweds, :view_key_denieds).first

I'd like to be able to do something like the following:

user = User.where(ID: 116).eager_load(:view_key_alloweds, :view_key_denieds).where('tblViewKey.IsWebView = 1').first

I need to expand the second "where" in be something like "tblViewKey.IsWebView = 1 AND SomeAlias.IsWebView = 1". Is there a way to specify the alias for the ViewKey model or is there a better way to do this? Does active record have a standard way of naming table aliases?

I'm looking into using a scope but I'm not sure how to define one for a "has_many ... through" association.

Answer 1

After more research I was able to come up with a solution that works and has good performance. Active Record does have a scheme for creating table aliases when joining to the same table multiple times. It seems to be the association name plus the name of the table joining from. So using the models shown I can query the data I want using:

@user = User.where(ID: params[:user_id])
.eager_load(:view_key_alloweds, :view_key_denieds, groups: [:view_keys])
.where('[tblViewKey].IsWebView = 1 OR [view_key_denieds_tblUser].IsWebView = 1 OR [view_keys_tblGroup].IsWebView = 1')
.first

The :view_key_alloweds association is listed first in the call to eager_load so my where does not need a alias for that join to the tblViewKey table. The :view_key_denieds also joins to the tblViewKey table but since it joins from the tblUser table I know the alias will be [view_key_denieds_tblUser]. The association through "groups: [:view_key]" is accomplished through the tblGroups table so the alias will be [view_keys_tblGroup].

My code works but I don't know of a way to tap into the aliases being generated. A future version of Rails and Active Record could break my code. There is a discussion here https://github.com/rails/rails/issues/12224 about aliases but I'm new enough to ruby, rails, and active record that most of it was over my head.