Query polymorphic tables easier using a view
Tables with polymorphic associations can be quite annoying to query with sql.
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_type can refer to either the
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
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.
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.
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
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.
Have some thoughts on this post? Reply with an email.