sql.rb

# {
    #   characters: [...],
    #   locations:  [...]
    # }
    def content(
      content_types: Rails.application.config.content_types[:all].map(&:name),
      page_scoping:  { user_id: self.id },
      universe_id:   nil
    )
      return {} if content_types.empty?

      polymorphic_content_fields = [:id, :name, :page_type, :user_id, :created_at, :updated_at, :deleted_at, :archived_at, :privacy]
      where_conditions = page_scoping.map { |key, value| "#{key} = #{value}" }.join(' AND ') + ' AND deleted_at IS NULL AND archived_at IS NULL'

      sql = content_types.uniq.map do |page_type|
        if page_type != 'Universe'
          # Even though we're selecting universe_id here, it's still absent from all of the result rows. No idea why.
          # Removing Universe from `content_types` and adding universe_id to the content_fields works, so maybe it's something to
          # do with UNIONing the NULL column?
          # clause = "SELECT #{polymorphic_content_fields.join(',')},universe_id FROM #{page_type.downcase.pluralize} WHERE #{where_conditions}"
          clause = "SELECT #{polymorphic_content_fields.join(',')} FROM #{page_type.downcase.pluralize} WHERE #{where_conditions}"
        else
          # clause = "SELECT #{polymorphic_content_fields.join(',')},id          FROM #{page_type.downcase.pluralize} WHERE #{where_conditions}"
          clause = "SELECT #{polymorphic_content_fields.join(',')} FROM #{page_type.downcase.pluralize} WHERE #{where_conditions}"
        end

        if universe_id.present? && page_type != 'Universe'
          clause += " AND universe_id = #{universe_id}"
        end
        clause
      end.compact.join(' UNION ALL ') + ' ORDER BY page_type, id'

      # TODO: improve this query -- it uses a tooon of memory
      result = ActiveRecord::Base.connection.execute(sql)
      @content_by_page_type ||= result.to_a.each_with_object({}) do |object, hash|
        object.keys.each do |key|
          object.except!(key) if key.is_a?(Integer)
        end

        hash[object['page_type']] ||= []
        hash[object['page_type']] << ContentPage.new(object)
      end
    end