---
title: "Common Table Expressions"
description: "CTEs in Tauri SQLite ORM."
canonical_url: "https://docs.ctrl-neo.dev/en/tauri-sqlite-orm/advanced-topics/common-table-expressions"
---
# Common Table Expressions (WITH)

A Common Table Expression (CTE), or `WITH` clause, allows you to create a temporary, named result set that you can reference within a subsequent query. CTEs are an excellent way to break down complex queries into logical, readable steps, improving maintainability.

The ORM supports CTEs through the `db.$with()` builder.

## How It Works

1. You define a CTE using `db.$with('cte_name').as(query)`.
2. The `query` you pass to `.as()` must be a query object: `{ sql: string; params: any[] }`. You can generate this using the [`sql` template literal](raw-sql) or by using another query builder's `.build()` method.
3. You then chain your main query (`.select()`, `.update()`, etc.) onto the `with` builder.
4. The CTE is now available by name within your main query, typically for use inside a join or subquery written with the `sql` template literal.

## Example: Selecting Users with Many Posts

Let's find all users who have written more than 10 posts. We can solve this cleanly by first creating a CTE that finds the `author_id` of prolific authors, and then selecting users whose IDs are in that set.

```typescript
import { db } from '$lib/db';
import { users, posts } from '$lib/db/schema';
import { sql } from '@type32/tauri-sqlite-orm';

async function getProlificAuthors() {
  // 1. Define the query for the CTE.
  // This query finds all author IDs that appear more than 10 times in the posts table.
  const prolificAuthorsQuery = sql`
    SELECT ${posts.authorId}
    FROM ${posts}
    GROUP BY ${posts.authorId}
    HAVING COUNT(*) > 10
  `;
  // Note: We are using the `sql` helper to safely embed column/table objects.
  // This will be translated to: SELECT author_id FROM posts ...

  // 2. Build and execute the full query.
  const results = await db
    // Define the CTE named 'prolific_authors'
    .$with('prolific_authors').as(prolificAuthorsQuery)
    
    // Chain the main SELECT query
    .select(users)
    
    // Use the CTE in the WHERE clause as a subquery
    .where(sql`${users.id} IN (SELECT author_id FROM prolific_authors)`)
    
    .all();

  console.log('Users with more than 10 posts:', results);
  return results;
}
```

In this example, the final SQL executed by the database would look something like this:

```sql
WITH prolific_authors AS (
  SELECT author_id
  FROM posts
  GROUP BY author_id
  HAVING COUNT(*) > 10
)
SELECT
  users.id,
  users.full_name,
  -- ... other user columns
FROM users
WHERE users.id IN (SELECT author_id FROM prolific_authors)
```


## Sitemap

See the full [sitemap](https://docs.ctrl-neo.dev/sitemap.md) for all pages.