Query multiple tables easily with Rails and Postgres Views

As your app grows in size, you'll likely find yourself having a few models with common attributes. You may eventually want to perform queries across these models as if they were one. For example, sort all of your articles, books and videos by when they were created. It would be easy to come up with a brute force way to do this, but luckily we have powerful tools in Rails and Postgres, which we'll talk about in this tutorial.

The Scenario

Imagine you have a content management system with articles, books and videos. You implemented each of these content types as their own model backed by their own database table. Though they have some common attributes like title, description and created_at.

This visual illustrates some attributes of articles, books and videos.

You can see how we would naturally want to develop some features like searching all our content types in one query, or sorting them all by created_at.

You could "brute force" this problem, which might involve querying each of these tables separately and combining the results in Ruby, but this is inefficient. A step up might be to create a UNION query to combine the results at the database level, but this has drawbacks as well.

Using Postgres Views in Rails

We can create an elegant solution using Postgres Views and a single Rails model.

Postges also offers Materialized Views. These actually store the results of their query. They can be useful if you're dealing with data that changes infrequently as they offer performance benefits. But in this tutorial, we'll only explore views.

A view is like a virtual table. We can query it, but it doesn't actually store any data. It can act like a reusable subquery and that's how we'll be using it in this case.

Additionally, thanks to the power of ActiveRecord, we'll only need 7 lines of code to implement the model in Rails. Let's see how it's done.

Setting up the view in our database

1) First, let's install the Scenic gem. It's not required, but it gives us nice ways to create and manage views.

bundle add scenic

2) Use scenic to generate the necessary files. We'll name our view search_results. The scenic gem will create a couple files for us through this command.

rails generate scenic:view search_results
      create  db/views/search_results_v01.sql
      create  db/migrate/20240731153045_create_search_results.rb

The search_results_v01.sql file defines our view, the migration will add the view to our database when we run rails db:migrate.

3) Implement the view. Add the following to db/views/search_results_v01.sql,

SELECT 'Article' as searchable_type, id AS searchable_id, title, description, created_at FROM articles
UNION ALL
SELECT 'Book' as searchable_type, id AS searchable_id, title, description, created_at FROM books
UNION ALL
SELECT 'Video' as searchable_type, id AS searchable_id, title, description, created_at FROM videos;

Then just run the migrations,

rails db:migrate

The above SQL unions our 3 tables and allows us to query against all the common columns as if we had all our articles, books and videos in a single table.

The column names searchable_type and searchable_id are important. They follow Rails naming conventions for polymorphic associations and will make it easy to go from a SearchResult to its associated Article, Book or Video. More on that in the next section.

Set up the SearchResult model

Now that we have a view in our Postgres database to simplify querying these 3 different tables, we need a way to query it with ActiveRecord in our Rails app.

To do this, we can setup a really simple, but powerful model.

# app/models/search_result.rb
class SearchResult < ApplicationRecord
  belongs_to :searchable, polymorphic: true

  def readonly?
    true
  end
end

The belongs_to :searchable, polymorphic: true tells ActiveRecord this model is associated to a "searchable" model. Here is where the searchable_type and searchable_id column names are handy as they follow Rails conventions for polymorphic associations. This single line of code is the key to unlocking lots of built in Rails features.

We also override the #readonly? method to tell ActiveRecord this is not a model we should be expecting to write to with methods like #save and #update. After all, this model is backed by a view, not a table.

Putting it Together

With this setup, we have a really easy and elegant way to perform otherwise messy queries. Here are some examples

Simple queries

# find all content with 'rails' in the title
SearchResult.where("lower(title) LIKE ?", "rails")
=> [#<SearchResult>, ...]

# find the 3 newest pieces of content
SearchResult.order(created_at: :desc).limit(3)
=> [#<SearchResult>, #<SearchResult>, #<SearchResult>]

We can see that for our needs, this design behaves like any regular Rails model backed by a table.

Using our polymorphic belongs_to

Lets also see how we can take advantage of the polymorphic association we set up.

# find and access the newest piece of content
search_result = SearchResult.order(created_at: :desc).limit(1).first
search_result.searchable
=> #<Video>

In this example, a Video was the most recently created piece of content. By calling #searchable on our SearchResult instance, we can get an instance of the Video itself.

This is incredibly useful for rendering partials, or even basic routing, as we could now add a link to the video with #polymorphic_path.

<%= link_to search_result.title, polymorphic_path(search_result.searchable) %>

Conclusion

Using Postgres Views with Rails provides an elegant solution for querying across multiple related models. This approach combines powerful features of both Postgres and Rails, resulting in clean, maintainable code. While it's not a silver bullet for all scenarios involving multiple models, it's a powerful tool to have in your Rails development toolkit. It's also worth noting this can all be achieved with MySQL as well. You can either create your view without scenic, or use the MySQL adapter.