Q: Rails Combining and Sorting ActiveRecord Relations when using polymorphic associations I am working on a small collection tracker where I feel like STI could really simplify this problem but it seems the general consensus is to avoid STI whenever possible so I have broken my models apart.  Currently, they are all the same but I do have a few different bits of metadata that I can see myself attaching to them.
Anyways, the root is a Platform which has many Games, Systems, Peripherals, etc. and I am trying to show all of these relations on a view in a dynamic table that is filterable, sortable and searchable.
For example a query could be @platform.collectables.search(q).order(:name).
# Schema: platforms[ id, name ]
class Platform < ApplicationRecord
  has_many :games
  has_many :systems
  has_many :peripherals
end

# Schema: games[ id, platform_id, name ]
class Game < ApplicationRecord
  belongs_to :platform
end

# Schema: systems[ id, platform_id, name ]
class System < ApplicationRecord
  belongs_to :platform
end

# Schema: peripherals[ id, platform_id, name ]
class Peripheral < ApplicationRecord
  belongs_to :platform
end

In the above, the polymorphism comes into play when I add them to a Collection:
# Schema: collections[ id, user_id, collectable_type, collectable_id ]
class Collection < ApplicationRecord
  belongs_to :user
  belongs_to :collectable, polymorphic: true
end

Now, when I view a Platform, I expect to see all of its games, systems and peripherals which I refer to as collectables.  How would I query all of these while being able to sort as a whole (ie: "name ASC").  Below works in theory but this changes the relation to an Array which stops me from further filtering, searching or reordering at the database level so I can't tag on another scope or order.
class Platform < ApplicationRecord
...

  def collectables
    games + systems + peripherals
  end
end

I stumbled on Delegated Types which kind of sounds like the step in the direction that I am looking for but maybe I am missing something.
I'm tempted to try the STI route, I don't see these models diverging much and things that are different could be stored inside of a JSONB column cause it's mostly just metadata for populating a view with and not really searching against.  Basically a model such as this but it seems so frowned upon, I feel like I must be missing something.
# Schema: collectables[ id, platform_id, type, name, data ]
class Collectable < ApplicationRecord
  belongs_to :platform
end

class Platform < ApplicationRecord
  has_many :collectables

  def games
    collectables.where(type: 'Game')
  end

  def systems
    collectables.where(type: 'System')
  end

  ...
end


A: One solution here would be Delegated Type (a relatively new Rails feature) which can basically be summarized as Multiple Table Inheritance through polymorphism. So you have a base table containing the shared attributes but each class also has its own table - thus avoiding some of  the key problems of STI.
# app/models/concerns/collectable.rb
# This module defines shared behavior for the collectable "subtypes"
module Collectable
  TYPES = %w{ Game System Peripheral }
  extend ActiveSupport::Concern
  included do
    has_one :base_collectable, as: :collectable
    accepts_nested_attributes_for :base_collectable
  end 
end

# This model contains the base attributes shared by all the collectable types
# rails g model base_collectable name collectable_type collectable_id:bigint
class BaseCollectable < ApplicationRecord 
  # this sets up a polymorhic association
  delegated_type :collectable, types: Collectable::TYPES
end

class Game < ApplicationRecord
  include Collectable
end

class Peripheral < ApplicationRecord
  include Collectable
end

class System < ApplicationRecord
  include Collectable
end

You can then setup a many to many assocation through a join model:
class Collection < ApplicationRecord
  belongs_to :user 
  has_many :collection_items
  has_many :base_collectables, through: :collection_items

  has_many :games, 
    through: :base_collectables, 
    source_type: 'Game'
  has_many :peripherals, 
    through: :base_collectables, 
    source_type: 'Peripheral'
  has_many :systems, 
    through: :base_collectables, 
    source_type: 'Systems'
end

class CollectionItem < ApplicationRecord
  belongs_to :collection
  belongs_to :base_collectable
end

# This model contains the base attributes shared by all the collectable types
# rails g model base_collectable name collectable_type collectable_id:bigint
class BaseCollectable < ApplicationRecord 
  # this sets up a polymorhic association
  delegated_type :collectable, types: %w{ Game System Peripheral }
  has_many :collection_items
  has_many :collections, through: :collection_items
end

This lets you treat it as a homogenius collection and order by columns on the base_collectables table.
The big but - the relational model still doesn't like cheaters
Polymorphic assocations are a dirty cheat around the object relational impedence missmatch by having one columns with a primary key reference and another storing the class name. Its not an actual foreign key since the assocation cannot resolved without first pulling the records out of the database.
This means you can't setup a has_many :collectables, through: :base_collectables association. And you can't eager load all the delegated types or order the entire collection by the columns on the games, peripherals or systems tables.
It does work for the specific types such as:
has_many :games, 
    through: :base_collectables, 
    source_type: 'Game'

Since the table can be known beforehand.
This is simply a tough nut to crack in relational databases which are table based and not object oriented and where relations in the form of foreign keys point to a single table.
VS STI + JSON
The key problem here is that all the data you're stuffing into the JSON column is essentially schemaless and you're restricted by the 6 types supported by JSON (none of which is a date or decent number type) and working with the data can be extremely difficult.
JSON's main feature is it simplicity which worked well enough as a transmission format. As a data storage format its not really that great.
It's a huge step up from the EAV table or storing YAML/JSON serialized into a varchar column but its still has huge caveats.
Other potential solutions

*

*STI. Fixes one the polymorphism problem and introduces a bunch more.

*A materized view that is populated with a union of the three tables can be treated as table and thus can simply have an ActiveRecord relation attached to it.

*Union query. Basically the same idea as above but you just use the raw query results or feed them into a model representing a non-sensical table.

*Non-relational database. Document based databases like MongoDB let you create flexible documents and non-homogenius collections by design while having better type support.

