Issue constructing a has_many relation with scope using multiple join tables

Question

I have a tag system on multiple models that are linked together.

The system works like this:

• A Top has many Middles
• A Middle has many Lows
• Tops, Middles and Lows have many Tags
• A tag associated to Top level is supposed to qualify every Middle and Low associated to it.
• Same goes for a tag that would be associated to a Middle, every Low associated to it would 'inherit' from the tags.

This mechanic is not on a database level, in the end in what concerns the database, Tops, Middles and Lows all have their own tag collection, and i initially implemented instance methods on each model so that when you call, for example, low_instance.all_tags, it concatenates the tag collections of it's parent Middles, and the one of its Top.

Here is what the models look like:

#          ______________________________
#         /                              \
#      (1)                                (*)
#    [Top] (1) __ (*) [Middle] (*) __ (*) [Low]
#      (*)               (*)              (*)
#        \_______________ | ______________/
#                         |
#                         *
#                       [Tags]

class Low < ApplicationRecord
  has_many :low_tags, dependent: :destroy
  has_many :tags, through: :low_tags
  has_many :middle_foos, dependent: :destroy
  has_many :middles, through: :middle_foos
end

class Middle < ApplicationRecord
  belongs_to :top
  has_many :middle_tags, dependent: :destroy
  has_many :tags, through: :middle_tags

  has_many :middle_lows, dependent: :destroy
  has_many :lows, through: :middle_lows
end

class Top < ApplicationRecord
  has_many :middles, dependent: :destroy
  has_many :lows, dependent: :destroy
  has_many :top_tags, dependent: :destroy
  has_many :tags, through: :top_tags
end


### Join tables
class MiddleLow < ApplicationRecord
  belongs_to :middle
  belongs_to :low
end

class LowTag < ApplicationRecord
  belongs_to :low
  belongs_to :tag
end

class MiddleTag < ApplicationRecord
  belongs_to :middle
  belongs_to :tag
end

class TopTag < ApplicationRecord
  belongs_to :top
  belongs_to :tag
end

That actually works like a charm. The issue is that i want to be able to search my Lows with the awesome Ransack gem and using the full tag collection of a Low (its self tags, plus the ones inherited from the parent Middles and Top)

Problem: Ransack only works with ActiveRecord::Relations. So from Ransack's point of view, i can only search my Lows using their self-tags and not the full inherited collection as this does not exist on the database level.

The initial solution to this problem i wanted to implement is to add a "copy" full tag collection on the database level that updates with the rest and that i could use to search with Ransack.

But I'm sure i don't have to add anything to the database as all the info is already here in the join tables and i kind of don't want to duplicate that info which is not super cool i think and would make the code base less understandable.

I have seen potential solutions using has many with scopes like so:

has_many :all_tags, ->(low) {
  unscope(.........).
  left_joins(..........).
  where(.........)
  # Returs self tags (Low) + tags from associated Middles + tags from the Top
}

I'm pretty sure this would be the best solution, but I'm really not good when it comes to database querying especially with so much models and join tables. I get confused and can't seem to find what to put in that scope so that i get this full collection of tags.

So if anybody has a clue about that, any help would be greatly appreciated!

By the way, using Rails 6.1 and Ruby 2.7

Answer 1

So found the solution to the query I wanted to construct in the end.

The scope looks like following:

    has_many :full_tags, lambda { |low|
        where_clause = 'top_tags.top_id = ? or low_tags.low_id = ?'
        where_args = [low.top_id, low.id]
        if low.middles.any?
          where_clause += ' or middle_tags.zone_id IN ?'
          where_args << low.middle_ids
        end
        unscope(where: :low_id)
          .left_joins(:middle_tags, :top_tags, :low_tags)
          .where(where_clause, *where_args).distinct
      }, class_name: 'Tag'

Calling xxx.full_tags on any instance of low returns the whole collection of tags from every middle it belongs to , plus those from the top it belongs to, plus its own tags, and the distinct makes it a unique collection.

That being said, that didn't fully fixed my problem because the whole purpose was to pass this scoped has_many relation as an attribute used by the Ransack gem to filter out my Low models, out of their full inherited collection of tags.

Big disappointment it was when i discovered that Ransack performs eager loading when it comes to search on associations: Rails does not support Eager Loading on scoped associations

So i ended up implementing a whole other solution for my tagging system. But hey, i learned a lot.