How to implement this SQL query with CTE in Exposed DSL?

Question

I have a SQL query that uses a Common Table Expression (CTE) with window functions, and I need to implement it using Kotlin's Exposed DSL. Since Exposed doesn't have built-in CTE support, I'm looking for a way to create custom CTEs.

Query:

WITH ranked_orders AS (
    SELECT
        customer_id,
        order_date,
        total_amount,
        DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rank_num
    FROM orders
)
SELECT
    customer_id,
    order_date,
    total_amount
FROM ranked_orders
WHERE rank_num <= 3;

Based on this JetBrains video (29:00) https://www.youtube.com/watch?v=xgfeqj8UyVA&lc=Ugz_N4GGefP6accqKbl4AaABAg, it seems Exposed provides the tools to implement CTEs ourselves, but I haven't found concrete examples of how to do it. They created custom CTE, haven't provided code. Any working code examples would be greatly appreciated!

You don't need a CTE for this. This is equivalent to a subquery as the posted answer shows. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 11 at 9:43
@PanagiotisKanavos Does this DSL support derived tables straightforwardly or does that just push the question to that? — Martin Smith
– Martin Smith, Commented Aug 11 at 10:39
@MartinSmith by googling around I found subqueries are supported through alias. I don't use Kotlin so I can't test whether .where works on an aliased query. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 11 at 10:46
This query was just for context. I want to know how to implement CTE in Exposed? Video I linked at 29:00, they have used a custom CTE (using With). — user31219547
– user31219547, Commented Aug 13 at 15:04

Luuk · Accepted Answer · 2025-08-13 18:22:50Z

3

+50

You can rewrite your SQL to this:

SELECT
    customer_id,
    order_date,
    total_amount
FROM (
    SELECT
        customer_id,
        order_date,
        total_amount,
        DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rank_num
    FROM orders
) ranked_orders 
WHERE rank_num <= 3;

It seems that kotlin-exposed does not know how to handle CTE, the same way as a regular database does do, so rewriting the SQL is option one.

The second option is to take a deep dive in the docs: adjustWhere, or with

edited Aug 13 at 18:22

answered Aug 9 at 16:13

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user31219547 Aug 13 at 15:04

This query was just for context. I want to know how to implement CTE in Exposed? Video I linked at 29:00, they have used a custom CTE (using With).

Luuk Aug 13 at 18:23

Updated to clarify why rewriting the query seems to be needed.

Collectives™ on Stack Overflow

How to implement this SQL query with CTE in Exposed DSL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related