Common Table Expressions

[mikeyhew]

I was just wondering, would it be possible to implement support for common table expressions in diesel's query dsl? e.g. SQL like the following:

WITH previous_post AS (SELECT id, created_at FROM posts WHERE id = $1)
SELECT * FROM posts, previous_post WHERE (posts.created_at, posts.id) > (previous_post.created_at, previous_post.id)

Obviously it's possible to write the above with sql_query, but if there were some way to declare previous_post as a query and then use it from another query and have it output SQL like the above, and have everything be typesafe, that would be pretty cool.

Has anyone thought about whether that would be possible and how one would go about adding support for that in diesel, or in a separate library that uses diesel?

[weiznich]

It's certainly possible to extend diesels DSL to support specific CTE. The Extending Diesel guide has an example for such an extension (although that guide uses a different unsupported kind of query, the workflow would be the same for CTE's).

Now the other question is whether someone could write a generic DSL for CTE's. I would say that this is possible But requires quite a bit of work. Both for the actual design and also for the implementation. Generally speaking it would required some way to check which expressions appear in a select clause of a given query and some way to join subqueries. I don't think it's impossible to implement but it just hasn't been the focus yet.