Introduction

Views and Common Table Expression or CTE are two important concept in PostgreSQL. There are good chances that you will have to deal with it if you work with data.

In my quest to become a SQL wizard, I had to tackle this of course. In a first part I will compare Views and CTE. I will then give you an example with an app I created for my passion of Coffee.

Views And CTE

First we have to give some definitions of our Views and the Common Table Expression.

Views

In SQL the views are basically tables, they contain several fields of different tables we have in our database.
But what we need to understand is that they are virtual tables, What does this mean ?
It means that they do not exist physically in our database.

A view is actually a stored query that you can access as a table. You execute the query each time you want to have access to certain field of this View.

For example in my app I wanted to have the rating ,for the coffees I have in my database, along with the name and the origin of coffee.

I can create a view for this like the following :

CREATE VIEW coffees_ratings AS (SELECT cr.rating AS rate,
 cr.origin as coffee_origin, c.name as coffee_name FROM 
      coffees c INNER JOIN coffee_reviews 
      cr ON c.id = cr.coffee_id)

And then I will be able to access it like a real table like this :

SELECT * FROM coffees_ratings

Pretty simple isn't it , so now let's go to the common table expression.

Common Table Expression

Sometimes we want to use a specific result of a query as temporary table, that we want to reference in other part of our query.
It might seems really similar to the View but not there are some differences. Views are computed each time can be used in any queries whereas CTE is executed once and then stored in-memory and then it can be reference as many times as you want but in the current current query.

With the same example from the view we can describe the the query like this.

Here is an example :

WITH coffees_rating as 
    (SELECT cr.rating AS rate, cr.origin as coffee_origin, c.name as coffee_name FROM 
      coffees c INNER JOIN coffee_reviews 
      cr ON c.id = cr.coffee_id)
SELECT coffee_name, rate , origin FROM coffees_rating

That's it really ?

That said there is a bunch of things that I have not dove into for the sake of a reasonably long article.
For example you can have Views that are physically stored on your disk it is the Materialized View.

To continue with this Materialized thing, CTE are by default materialized and stored in-memory. But you can specify that you don't want materialized your CTE for several reasons.

So CTE and Views can quite complex with a lot of differents ways to deal with them.

Summarize

To summarize a bit what are the key differences of these 2 SQL concept from my understanding.

CTE create in-memory tables, that can be accessed from another part of your SAME query. What I mean is that you have to define the CTE before the real query that you want to make. CTE "just" enables you to write easy to follow query. But if you want to reuse a CTE in another query you have to rewrite it in your other query.

Views helps you to have a better architecture of your code.
If there is a query that you use a lot in a different part of your code/app. You can create a View to create a virtual table and you don't have to rewrite it everywhere. You can even materialized it so it physically exists and it improves your performance.
Basically it helps you to respect the DRY principles.

That said you can use Views and CTE with each other !

Rails

In a first place I will tackle the

Common Table Expression

I will deal with the API of Rails under the current latest stable version in the moment which is 7.0.4, it is really simple to create CTE in Rails.
If we want to translate the example above we have to use the Arel Gem.

There is several way to do that I will present 2 of them way plain sql and with a lot of Arel API.

With the Arel API :

c_table = Arel::Table.new(:coffees)
cr_table = Arel::Table.new(:coffee_reviews)
pr = c_table[:id].eq(cr_table[:coffee_id])
query = c_table.project(Arel.star).join(cr_table).on(pr).to_sql
cte_table = Arel::Table.new(:coffees_rating)
cte_result = Arel::Nodes::As.new(cte_table,query)
sql = cte_table.project(cte_result[:name]).with(cte_result).to_sql

With Arel API and Plain SQL :

sql = Arel.sql('WITH coffees_rating as (SELECT coffee_reviews.rating
 AS rate, coffees.origin as origine, coffees.name as cname 
FROM coffees INNER JOIN coffee_reviews ON coffees.id = 
coffee_reviews.coffee_id) SELECT cname FROM coffees_rating')

Tips : If you want to try one of the previous solution in your rails console you can do like in the following line.

ActiveRecord::Base.connection.exec_query sql

Views

So now let's deal with Views, there is a really well known gems out there which is Scenic. I will use this one for creating my views. But can also create them with a rails migration or directly in your database it is up to you.

So you can add the scenic view to your gemfile. Then you can use the generator of scenic to create a new view in our ruby code.
If you wonder how to create a generator I did an article on that you can read it here.

rails g scenic:view coffees_rating

It will create a new rails migration with a new method which is create_view like this:

class CreateCoffeesRatings < ActiveRecord::Migration[7.0]
  def change
    create_view :coffees_ratings
  end
end

This generator will also creates a sql file to create our View.

CREATE VIEW coffees_ratings AS (SELECT cr.rating AS rate, cr.origin as coffee_origin, c.name as coffee_name FROM 
      coffees c INNER JOIN coffee_reviews 
      cr ON c.id = cr.coffee_id)

Eventually you can run your migration and that's it you can access your view. You can even create an ActiveRecord model for your view so it is easy to access it.

Conclusion

So now you and I know the differences between Views and CTE and how to use them in rails.
That said there is another way to use them in rails that I did tackle in this article.

Arel is a really good gem to deal with relational algebra and SQL in rails. For your information ActiveRecord actually rely on this in the background.
But maybe you would prefer to write a raw sql query and execute it directly.

Same for the scenic gem, maybe it can be worthy for your needs to use it or maybe it is more worthy to use plain SQL and store it directly in your Database.

All of this is a matter tradeoff and a matter of management of your time, outside dependency, skills you have/want.

Thanks for reading me I hope you learn something !

Thanks for reading me !

Keep in Touch

On Twitter : @yet_anotherdev