Skip to main content
When a stream needs reusable filtering logic, you can define it once in a and reuse it in that stream’s queries. This keeps stream definitions DRY and makes it easier to maintain. For the supported syntax of the with block and CTE rules, see Supported SQL — CTE and WITH syntax.

​
Why Use CTEs

Consider an app where users belong to organizations. Several tables need to filter by the user’s organizations: 
# Without CTEs - repetitive and hard to maintain
streams:
  org_projects:
    query: |
      SELECT * FROM projects 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
  
  org_repositories:
    query: |
      SELECT * FROM repositories 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
  
  org_settings:
    query: |
      SELECT * FROM settings 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
The same subquery appears three times. You can merge these into one stream and define the logic once using a CTE: 
# With a CTE and multiple queries
streams:
  org_data:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM projects WHERE org_id IN user_orgs
      - SELECT * FROM repositories WHERE org_id IN user_orgs
      - SELECT * FROM settings WHERE org_id IN user_orgs
If the membership logic changes, you update it in one place.

​
Defining CTEs

Define CTEs in a with block inside a stream. Each CTE has a name and a SELECT query: 
streams:
  my_stream:
    with:
      cte_name: SELECT columns FROM table WHERE conditions
    query: SELECT * FROM some_table WHERE col IN cte_name
The CTE query can include any filtering logic, including parameters: 
streams:
  user_data:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
      active_projects: SELECT id FROM projects WHERE archived = false
    query: SELECT * FROM projects WHERE org_id IN user_orgs AND id IN active_projects

​
Using CTEs in Queries

Once defined in a stream’s with block, use the CTE name in that stream’s query or queries. You can use it like a subquery or join it as if it were a table. Short-hand syntax (when the CTE has exactly one column): 
streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT * FROM projects WHERE org_id IN user_orgs
The short-hand IN cte_name is equivalent to IN (SELECT * FROM cte_name). If the CTE has more than one column, this form is an error; use explicit subquery or join syntax instead. Explicit subquery syntax (when you need to select specific columns): 
streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT * FROM projects WHERE org_id IN (SELECT org_id FROM user_orgs)
Join syntax (you can join a CTE as if it were a table): 
streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT projects.* FROM projects, user_orgs WHERE user_orgs.org_id = projects.org_id

​
Combining with Multiple Queries

CTEs work well with the queries feature (multiple queries per stream). This lets you share the CTE and keep all query results in one stream: the client only needs to manage one subscription instead of multiple. 
streams:
  user_data:
    with:
      my_org: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM projects WHERE org_id IN my_org
      - SELECT * FROM repositories WHERE org_id IN my_org
      - SELECT * FROM team_members WHERE org_id IN my_org

​
Complete Example

A full configuration using CTEs. Each stream that needs shared logic defines its own with block: 
config:
  edition: 3

streams:
  # Organization-level data (auto-sync) - one stream with CTE and multiple queries
  org_and_projects:
    auto_subscribe: true
    with:
      user_orgs: |
        SELECT org_id FROM org_memberships WHERE user_id = auth.user_id()
      accessible_projects: |
        SELECT id FROM projects
        WHERE org_id IN user_orgs
          OR id IN (SELECT project_id FROM project_shares WHERE shared_with = auth.user_id())
    queries:
      - SELECT * FROM organizations WHERE id IN user_orgs
      - SELECT * FROM projects WHERE id IN accessible_projects

  # Project details (on-demand) - same CTE and param, so one stream with multiple queries
  project_details:
    with:
      accessible_projects: |
        SELECT id FROM projects
        WHERE org_id IN (SELECT org_id FROM org_memberships WHERE user_id = auth.user_id())
          OR id IN (SELECT project_id FROM project_shares WHERE shared_with = auth.user_id())
    queries:
      - |
        SELECT * FROM tasks
        WHERE project_id = subscription.parameter('project_id')
          AND project_id IN accessible_projects
      - |
        SELECT * FROM files
        WHERE project_id = subscription.parameter('project_id')
          AND project_id IN accessible_projects

​
Limitations

The following rules apply to CTEs. For the full syntax reference, see Supported SQL — CTE and WITH syntax. Sync Streams do not support global CTEs. Use a with block only inside a stream. To reuse logic across streams, define the same CTE (or equivalent subquery) in each stream that needs it, or combine streams using multiple queries per stream so one stream can share a single CTE across queries. CTEs cannot reference other CTEs. Each CTE must be self-contained: 
# This won't work - cte2 cannot reference cte1
streams:
  my_stream:
    with:
      cte1: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
      cte2: SELECT id FROM projects WHERE org_id IN cte1  # Error!
If you need to chain filters, use nested subqueries in your stream query instead: 
streams:
  tasks:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: |
      SELECT * FROM tasks
      WHERE project_id IN (
        SELECT id FROM projects WHERE org_id IN user_orgs
      )
The short-hand IN cte_name works only when the CTE has exactly one column. If the CTE has multiple columns, use explicit subquery syntax or join the CTE as a table. CTE names take precedence over table/collection names. If you define a CTE with the same name as a database table/collection, the CTE will be used. Choose distinct names to avoid confusion.