Sort on a polimorphyc association

Question

Suppose I have following polymorphic relationship

class Metric < ApplicationRecord
    belongs_to :metrizable, polymorphic: true
    belongs_to :company, class_name: 'Company', foreign_key: 'metrizable_id', optional: true
    belongs_to :portfolio, class_name: 'Portfolio', foreign_key: 'metrizable_id', optional: true

end

class Company < ApplicationRecord
    has_many :metrics, as: :metrizable, dependent: :destroy
end

class Portfolio < ApplicationRecord
    has_many :metrics, as: :metrizable, dependent: :destroy
end

Now Metrics are metrizable with either Company or Portfolio. In the both tables (Company, Portfolio), name field is there in the database.

example:

@search = Metric.ransack(params[:q])
@search.sorts = ["company_name asc", "portfolio_name asc"]
@search.result.to_sql

I get something like

"SELECT \"metrics\".* FROM \"metrics\" LEFT OUTER JOIN \"company\" ON \"company\".\"id\" = \"metrics\".\"metrizable_id\" LEFT OUTER JOIN \"portfolios\" ON \"portfolios\".\"id\" = \"metrics\".\"metrizable_id\" ORDER BY \"company\".\"name\" ASC, \"portfolios\".\"name\" ASC, \"metrics\".\"name\" ASC"

Even if I get all the records, they are not ordered by entity.name, entity(company, portfolio) I would like to have it all ordered by entity.name independent of the source table.

Answer 1

If it's a smaller dataset, and you don't expect bigger ones, you can simply sort them in Ruby.

Otherwise, you can add a database view on the companies and portfolios tables, and order by that. For example, in Postgres:

CREATE VIEW entities AS SELECT
  id,
  name,
  metrizable_id,
  'company' AS type
FROM
  companies
UNION
SELECT
  id,
  name,
  metrizable_id,
  'portfolio' AS type
FROM
  portfolios

Entity model:

class Entity < ApplicationRecord
  self.inheritance_column = nil

  has_many :metrics
end

Metric model:

class Metric < ApplicationRecord
  # ...
  belongs_to :entity, foreign_key: 'metrizable_id', optional: true
end

And then in the query:

@search = Metric.ransack(params[:q])
@search.sorts = ["entity_name asc"]
@search.result.to_sql