How to compare a joined table with the source dynamically

Question

The Context

I have Five models: Reward, BrandSubscription, Brand, Tier and User.

• Brand has many Tiers.
• BrandSubscription belongs to a Tier and to User.
• Reward belongs to a Tier.
• Tier has an attribute named order. If a BrandSubscription have a tier of a higher order, it will also have all the lower tiers as well.
• A BrandSubscription can see all Rewards from all its Tiers, that are in this case the Tier that it belongs to and all its lower Tiers.

The Problem

My problem is with the last item of the list above. I'm trying to get all rewards of a brand subscription.

The ideal way would be a has_many :rewards on the BrandSubscription entity, that would be through: :tier. On Tier I could have a has_many :rewards. The problem with this approach is that the rewards are not restricted to the current tier, but must include all rewards from the lower order tiers.

To achieve that, I put a scope on the has_many :rewards of the Tier model:

class Tier < ActiveRecord::Base
  belongs_to :brand
  has_many :rewards

  has_many :with_lower_tiers, lambda {
    where(this_table[:order].gteq(that_table[:order]))
  }, through: :brand, source: :tiers

  has_many :achievable_rewards, through: :with_lower_tiers, source: rewards
end

The problem here, is with this_table and that_table. I need to make some kind of a join here, so I could have a comparison between the tables. The one approach I could make work was this:

class Tier < ActiveRecord::Base
  belongs_to :brand
  has_many :rewards

  has_many :with_lower_tiers, lambda { |tier|
    where(current_scope.table[:order].lteq(tier.order))
  }, through: :brand, source: :tiers

  has_many :achievable_rewards, through: :with_lower_tiers, source: rewards
end

Here I use the tier owner object and get its order. The problem here is that I can't really rely on the tier argument. The follow query already breaks, because what is really passed as argument to the scope function is the "owner" entity of the query, in this case, BrandSubscription:

BrandSubscription.joins(:with_lower_tiers)

The SQL query I want to get is the following, where I can get all available rewards from a User. Note that I'm joining the tiers table twice, and the is precisely where I'm having troubles:

SELECT DISTINCT rewards.*
  FROM tiers
  INNER JOIN brand_subscriptions ON tiers.id = brand_subscriptions.tier_id
  INNER JOIN tiers tiers_reward ON tiers_reward.brand_id = tiers.brand_id
  INNER JOIN rewards ON tiers_reward.id = rewards.tier_id
  WHERE tiers_reward.order <= tiers.order
    AND brand_subscriptions.user_id = 1234

I believe some Arel may help but I really would love if I could rely entirely on the ActiveRecord for this, as the code would be much cleaner.

References

I'm using the following links in order to try to solve this problem:

• Join the same table twice with conditions
• https://robots.thoughtbot.com/using-arel-to-compose-sql-queries
• http://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html
• https://gist.github.com/mildmojo/3724189
• http://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html
• ActiveRecord query with alias'd table names

Answer 1

You could build the query yourself instead of relying on complicated has_many-through associations

class Reward < ActiveRecord::Base
  belongs_to :tier
end

class Brand < ActiveRecord::Base
  has_many :tiers
end

class Tier < ActiveRecord::Base
  belongs_to :brand
  has_many :rewards

  # has_many :lower_tiers, ->(tier){ where('tiers.order < ?', tier.order) }, through: :brand, source: :tiers

  def lower_tiers
    Tier.where('brand_id = ? AND order < ?', brand_id, order)
  end

  def achievable_rewards
    Rewards.where('tier_id IN (?) OR tier_id = ?', lower_tiers.select(:id), id)
  end
end


class BrandSubscription < ActiveRecord::Base
  belongs_to :user
  belongs_to :tier

  def rewards
    tier ? tier.achievable_rewards : Reward.none
  end
end