Use Common Table Expressions

December 26, 2023 • 4 min read

Hey! Thanks for stopping by! Just a word of warning, this post is 3 years old, . If there's technical information in here it's more than likely out of date.

I have the popular posts on this site which are stored in a Turso table, the table is generated from the Fathom Analytics API on this site and you can see them when you get to the bottom of a post or in the footer of the site. Here’s the sitch, using the Turso client I needed to run several queries, for the day, month and year. Because SQLite is single threaded, I needed to run each query in sequence, which was causing latency in the +layout.server.ts file.

Tl;dr, skip to Common Table Expressions for the solution.

I was doing something like this:

1// Fetch Popular Posts
2const popular_posts_promises = ['day', 'month', 'year'].map(
3	(period) => fetch_popular_posts(fetch, period),
4);
5
6const [
7	popular_posts_daily,
8	popular_posts_monthly,
9	popular_posts_yearly,
10] = await Promise.all(popular_posts_promises);

fetch_popular_posts was running the queries though the Turso client individually.

Ok, no problem, so, I’ll do a union query and send off the one request to the Turso database, right?

1SELECT 'day' AS period, * FROM popular_posts WHERE date_grouping = 'day'
2UNION
3SELECT 'month' AS period, * FROM popular_posts WHERE date_grouping = 'month'
4UNION
5SELECT 'year' AS period, * FROM popular_posts WHERE date_grouping = 'year';

Yes, but, the Fathom API doesn’t return the title of the post, just the pathname. Which means that the data, although fine in itself, is not useful as is.

So, this was the JSON I was getting with that query:

1{
2	"daily": [
3		{
4			"period": "day",
5			"id": 1,
6			"pathname": "/posts/use-chrome-in-ubuntu-wsl",
7			"pageviews": 90,
8			"visits": 22,
9			"date_grouping": "day",
10			"last_updated": "2023-12-26 11:44:33"
11		}
12		// rest of the posts...
13	]
14}

I need to do a join to another table to get the title of the post so I can get some JSON back like this:

1{
2	"daily": [
3		{
4			"period": "day",
5			"id": 1,
6			"pathname": "/posts/use-chrome-in-ubuntu-wsl",
7			"title": "Use Google Chrome in Ubuntu on Windows Subsystem Linux",
8			"pageviews": 90,
9			"visits": 22,
10			"date_grouping": "day",
11			"last_updated": "2023-12-26 11:44:33"
12		}
13		// rest of the posts...
14	]
15}

This is where the query gets a bit more chunky and where things begin to fall apart. 😅

So, a typical join to get the title of the post from another table:

1SELECT
2  'day' AS period,
3  pp.id,
4  pp.pathname,
5  p.title,
6  pp.pageviews,
7  pp.visits,
8  pp.date_grouping,
9  pp.last_updated
10FROM
11  popular_posts pp
12JOIN
13  posts p ON pp.pathname = '/posts/' || p.slug
14WHERE
15  pp.date_grouping = 'day'
16ORDER BY
17  pp.pageviews DESC
18LIMIT 20

So, I need to do this for the other periods adding in the union query, I’ll skip the repeated fields for brevity here, it’s essentially this:

1SELECT
2  'day' AS period,
3  pp.id,
4  pp.pathname,
5  p.title,
6  pp.pageviews,
7  pp.visits,
8  pp.date_grouping,
9  pp.last_updated
10FROM
11  popular_posts pp
12JOIN
13  posts p ON pp.pathname = '/posts/' || p.slug
14WHERE
15  pp.date_grouping = 'day'
16ORDER BY
17  pp.pageviews DESC
18LIMIT 20
19
20UNION ALL
21
22SELECT
23  'month' AS period,
24  -- Same columns as above
25WHERE
26  pp.date_grouping = 'month'
27-- Same ORDER BY and LIMIT
28
29UNION ALL
30
31SELECT
32  'year' AS period,
33  -- Same columns as above
34WHERE
35  pp.date_grouping = 'year'
36-- Same ORDER BY and LIMIT;

So, whacking that into the Turso shell I get this:

1SQL string could not be parsed: near UNION, "None": syntax error at (17, 6)

Loads of variations on that and still the same error.

Common Table Expressions

Common Table Expressions (CTEs) in SQLite are a way to compose temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Start a CTE with WITH and then give it a name and the query you want to run.

Essentially each CTE is wrapped in parentheses and separated by a comma.

Then you can run a query against it:

1WITH cte_name AS (
2  SELECT * FROM table_name WHERE column_name = 'value' LIMIT 1
3)
4SELECT * FROM cte_name;

This is particularly handy for breaking down complex queries into simpler parts, which is sort of what I was doing. I’m essentially doing this:

1WITH cte_name_1 AS (
2  SELECT * FROM table_name WHERE column_name = 'value' LIMIT 1
3),
4cte_name_2 AS (
5  SELECT * FROM table_name2 WHERE column_name = 'value' LIMIT 1
6),
7cte_name_3 AS (
8  SELECT * FROM table_name3 WHERE column_name = 'value' LIMIT 1
9)
10
11SELECT * FROM cte_name_1
12UNION ALL
13SELECT * FROM cte_name_2
14UNION ALL
15SELECT * FROM cte_name_3;

This means creating temporary results for each of the periods and then querying them with a union query.

1WITH DayResults AS (
2  SELECT
3    'day' AS period,
4    pp.id,
5    pp.pathname,
6    p.title, -- Include title from the posts table
7    pp.pageviews,
8    pp.visits,
9    pp.date_grouping,
10    pp.last_updated
11  FROM
12    popular_posts pp
13  JOIN
14    posts p ON pp.pathname = '/posts/' || p.slug -- Join with posts table
15  WHERE
16    pp.date_grouping = 'day'
17  ORDER BY
18    pp.pageviews DESC
19  LIMIT 20
20),
21MonthResults AS (
22  SELECT
23    'month' AS period,
24    -- Same as DayResults query
25),
26YearResults AS (
27  SELECT
28    'year' AS period,
29    -- Same as DayResults query
30)
31
32SELECT * FROM DayResults
33UNION ALL
34SELECT * FROM MonthResults
35UNION ALL
36SELECT * FROM YearResults;

Runs fine in the Turso shell and returns the data I need like in the json above.

Bit of a round about way of doing it, but, it works and the latency has gone. 🎉

There's a reactions leaderboard you can check out too.

Useful? Share it on Bluesky.

✨ View the stats for this post ✨

Sign up for the newsletter

Want to keep up to date with what I'm working on?

Join other developers and sign up for the newsletter.

I care about the protection of your data. Read the Privacy Policy for more info.