Elegant Way to Represent Polymorphic Relationship in Sequel Model?

[jvanderen1]

We currently have a polymorphic relationship with one of our tables. Using views, we would like to be able to represent these views using separate model classes.

Example

Given the following example SQL:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT,
  stage TEXT
);

CREATE VIEW high_school_students_view AS
    SELECT *
    FROM students
    WHERE stage = 'high_school';

CREATE VIEW elementary_school_students_view AS
    SELECT *
    FROM students
    WHERE stage = 'elementary_school';

Is it possible to have a single model reference these views correctly? I was thinking the ClassTableInheritance plugin would be a good choice:

class Student < Sequel::Model
  plugin :class_table_inheritance,
         table_map: { Student: :students, HighSchoolStudentsView: :high_school_students_view,
                      ElementarySchoolStudentsView: :elementary_school_students_view },
         ignore_subclass_columns: %i[name stage]
end

class HighSchoolStudentsView < Student
end

class ElementarySchoolStudentsView < Student
end

However, the SQL generated is quite ugly:

❯ HighSchoolStudentsView.dataset
=> #<Sequel::Postgres::Dataset: "SELECT * FROM (SELECT \"students\".\"id\", \"students\".\"name\", \"students\".\"stage\" FROM \"students\" INNER JOIN \"high_school_students_view\" ON (\"high_school_students_view\".\"id\" = \"students\".\"id\")) AS \"students\"">

Is there anyway to modify a dataset to use a simplified query while returning the parent model class? Something like:

❯ HighSchoolStudentsView.dataset
=> #<Sequel::Postgres::Dataset: "SELECT * FROM high_school_students_view">

[jeremyevans]

You aren't dealing with multiple tables, so single_table_inheritance would be more appropriate than class_table_inheritance. You could then try:

class HighSchoolStudent < Student
  set_dataset :high_school_students_view 
end

If you aren't further subclassing HighSchoolStudent, that may work for what you want.

[jvanderen1]

@jeremyevans You sir are legendary 💯 . I always appreciate your help!