Ian Jones Logo

Common Table Expressons

You can build out recursive queries in sql. This can be done through a feature called Common Table Expressions (CTE).

CTE's build a virtual table that you can querie out of. When you add the recursive key word, it gives you the ability to define 2 things:

  1. The query that builds the seed row

  2. The query that continues recursively until all the rows are added to the result set

Heres a simple one to start with:

WITH RECURSIVE counter AS (
  SELECT 1 as n

  UNION ALL

  SELECT n + 1 FROM counter WHERE n < 10
)
SELECT * from counter;

The query above the union all sets the seed data. In this case, its just 1. We are naming this data as n so it can be used in the bottom query.

The bottom query adds n + 1 for each result until n < 10. This means on the first run:

n = 1
n + 1 = 2

Then on the second run:

n = 2
n + 1 = 3

This happens until n < 10.

Heres the resulting table:

 n
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

Now you can query this CTE as you would any other table.

select * from counter where n % 2 = 0
 n
----
  2
  4
  6
  8
 10
(5 rows)

Now with a less trivial example.

I have a data model like

Playlist1 -> Tracklist1 -> Playlist2

Playlist2 -> Tracklist2 -> Lesson1
Playlist2 -> Tracklist3 -> Lesson2

Playlist1 -> Tracklist4 -> Playlist3

Playlist3 -> Tracklist5 -> Lesson3
Playlist3 -> Tracklist6 -> Lesson4

Playlists have many tracklists. Tracklists can be a Playlist or a Lesson. Inner tracklists can contain more playlists or a lesson.

In this example, Playlist1 has 2 tracklists Playlist2 and Playlist3. In turn Playlist2 and Playlist3 each have 2 tracklists that point to lessons with a combined total of 4 lessons.

Given a root playlist (Playlist1), I need to find all the lessons in that playlist and any sub playlists (so Lesson1 Lesson2 Lesson3 Lesson4).

Heres the query:

 WITH RECURSIVE purchases AS (#{purchases_scope.to_sql}),
    sub_tree AS (
            SELECT
            id,
            tracklistable_id,
            tracklistable_type,
            playlist_id
        FROM
            tracklists
        WHERE
            playlist_id in (select sellable_id from purchases)
        UNION ALL
        SELECT
            t.id,
            t.tracklistable_id,
            t.tracklistable_type,
            t.playlist_id
        FROM
            tracklists t,
            sub_tree st
        WHERE
            t.playlist_id = st.tracklistable_id and st.tracklistable_type = 'Playlist'
        )
select * from sub_tree

The seed query will find a playlist give some ids in an outside CTE. We will grab all the Tracklists for that given root playlist.

Now the recursive query comes into play. We are querying the tracklists table again and the where clause is important here.

t.playlist_id = st.tracklistable_id and st.tracklistable_type = 'Playlist'

The recursive query will take the tracklist rows from the seed query, and for each one, it will check if st.tracklistable_type = 'Playlist'. This means that we will be looking for this next playlists tracklists. We then connect this tracklist that is in the result set with the tracklists of the playlist it represents.

This will recur all the way down until there are no more tracklists that point to a playlist.

Backlinks