How do I group by derived columns? Also, are common table expressions supported?

[grechkay]

I'm using sqlite

Say I have a schema:

id INTEGER,
val INTEGER

I want to get the sum of the values for odd ids and even ids.

In raw sql, I would accomplish this as such:

WITH T1 AS (
  SELECT 
    id % 2 AS mod,
    val
)
SELECT 
  mod,
  SUM(val) AS sum_val
FROM 
  T1
GROUP BY mod

However I'm having a really hard time replicating this logic with the diesel query builder. Right now I am basically using raw sql for this type of logic, but if possible, I'd like to migrate it over to the query builder.

[weiznich]

It's always hard to answer questions that ask "Is it possible to do xyz", because almost anything is possible if you are willing to spend time implementing in.

In this case the built-in DSL does not support CTE yet, although that's something that might change in the future. That written: You likely don't need to use a CTE expression there at all as you could write that query as well as

SELECT 
  id % 2 AS mod,
  SUM(val) AS sum_val
FROM 
  Table
GROUP BY mod

or as

SELECT 
  id % 2,
  SUM(val) AS sum_val
FROM 
  Table
GROUP BY id % 2

The first variant would require column aliasing support in the DSL, which again is not supported yet. The second variant is technically supported, but I'm not 100% sure if that would run into #3950.

Finally you can always extend the DSL on your own in your own crate, checkout the "Extending Diesel" for details. If you really want to express it that way as DSL that's the way you should go.