Query polymorphic tables easier using a view

Feb 2023

Tables with polymorphic associations can be quite annoying to query with sql.

Polymorphic?

Some popular ORMs, like Rails’s Active Record, allow the use of polymorphic associations. Here’s an example of what this looks like:

You can see that the columns imageable_id and imageable_type can refer to either the employees or products table. If you only work in ORM land, it’s quite convenient. If you want to grab a product’s pictures, you can retrieve it with @product.pictures, similarly with an employee’s pictures with @employee.pictures.

Problems

The problems start coming in if you need to query it with SQL. The below code uses PostgreSQL but the concept should be applicable to other SQL databases. If you only want an employee’s pictures, you need to use a where clause like this: where imageable_type = 'Employee' and imageable_id = :id. That doesn’t seem so bad right? What if there are two polymorphic associations in a single table? This is quite common in some Rails codebases. You would have to do something like:

select *
from pictures
where
  imageable_type = 'Employee'
  and imageable_id = :imageable_id
  and department_type = 'Marketing'
  and department_id = :department_id;

You can start to imagine that it can get troublesome very quickly.

Possible solutions

What are some possible ways to make querying this data easier? create normalized tables for each polymoprhic association create views for each polymoprhic association Let’s take a brief look at each one.

a. Normalized tables

If we created normalized tables, then we would get foreign keys back. But, do we keep the polymoprhic table or migrate all data over to the new ones? If we migrate all the data and remove the polymoprhic table, it means we have to update all the code that references that table. This is the most complete solution if you can afford to do this. If you don’t want to update all the old code, you could set triggers so that inserts/updates/deletes on the polymorphic table are done on the new normalized tables instead. Although it results in a messier data scheme, this way does work.

b. Views

A scrapier alternative that doesn’t involve migrating data is to create views for each polymoprhic association. A view is not physically materialized, so it’s not a table. But it’s more than just a ‘shortcut’ to a longer query because it can also be updatable.

Using a view

Let’s look at an example. If you want to follow along, you can use this postgres-wasm tool in your browser!

create table pictures (
  id serial primary key,
  name text not null,
  imageable_id text not null,
  imageable_type text not null
);

insert into pictures (imageable_type, imageable_id, name)
values ('Employee', 1, 'someone');
insert into pictures (imageable_type, imageable_id, name)
values ('Product', 1, 'thingy');

Here we have our pictures table that we’ve been looking at, along with some sample data for an employee and a product.

a. Creating a view

Next up we’re going to create the view.

create view employee_pictures_view as
  select
      id as picture_id,
        imageable_id as employee_id,
        name
    from pictures
    where imageable_type = 'Employee';

select * from employee_pictures_view;

Now when you query this employee_pictures_view, you will only get the employee pictures, and not any other group. So much easier already!

But wait, what if I want to insert/update/delete something for an employee’s pictures? You can also do that with a view. If we didn’t rename a column, we could just do a straight update into the view. But since we did, we will have to create a new function that converts our changed column name into the original column name. Then use that function as a trigger for inserts/updates/deletes for the view.

b. Updating a view

This example below only goes over the insert, but the process is similar for updates/deletes.

create or replace function employee_pictures_view_insert()
returns trigger
language plpgsql
as $function$
begin
  insert into pictures (imageable_type, imageable_id, name)
    values ('Employee', new.employee_id, new.name);

    return new;
end;
$function$;

create trigger employee_pictures_view_insert_trigger
instead of insert on
employee_pictures_view for each row execute procedure employee_pictures_view_insert();

Now you can easily do an insert into this view.

insert into employee_pictures_view (employee_picture_id, name) values (2, 'person');

select * from employee_pictures_view;

Now, take a look at the original pictures table and see that it has the new data that you inserted into employee_pictures_view.

select * from pictures;

c. Benefits and Tradeoffs

The main benefit of this method is that there’s still a single source of truth for all the pictures since there’s only one pictures table. You don’t have to touch any of the old code at all. And your new code can use the new views and almost treat them as tables. Of course, you still don’t get to use foreign keys here, but that’s one of the tradeoffs. views are a very convenient method for these sorts of issues.