Performing ORDER BY and GROUP BY on alias fields

[moritz89]

When performing a select query with ORDER BY and GROUP BY keywords that reference a function field with an alias, it does not result in the expected SQL statement. Below is the code and the query it should result in:

from querybuilder.fields import MultiField
from querybuilder.query import Query

class TimeBucket(MultiField):
     def __init__(self, bucket_seconds=60, *args, **kwargs):
         super().__init__(*args, **kwargs)
         self.bucket_seconds = float(bucket_seconds)
         self.auto_alias = f"{self.field.name}_bucket"

     def get_select_sql(self):
         return f"""time_bucket('{self.bucket_seconds:.3f}s',"{self.field.name}")"""

time_bucket = TimeBucket(field="time")
query = Query().from_table("datapoint", [time_bucket, AvgField("value", alias="avg_value")])

SELECT time_bucket('60.000s',"time") AS "time_bucket", AVG(datapoint.value) AS "avg_value" FROM datapoint GROUP BY 1 ORDER BY 1

group_by and order_by support a dict with {"alias": "bucket"} but this does not seem to result in the target SQL query. The group by and order by cannot use the alias name of the function but have to either:

1. Use a number to reference the position of the select statement
2. Repeat the function in the select statement (i.e., time_bucket('60.000s',"time"))

As reference, the outputs of the above queries are:

> query.get_sql()
> 'SELECT time_bucket(\'60.000s\',"time") AS "time_bucket", AVG(datapoint.value) AS "avg_value" FROM datapoint'

> query.group_by({"alias": "time_bucket"}).get_sql()
> 'SELECT time_bucket(\'60.000s\',"time") AS "time_bucket", AVG(datapoint.value) AS "avg_value" FROM datapoint GROUP BY alias'

> query.group_by({"alias": "time_bucket"}).order_by("time_bucket").get_sql()
> 'SELECT time_bucket(\'60.000s\',"time") AS "time_bucket", AVG(datapoint.value) AS "avg_value" FROM datapoint GROUP BY alias ORDER BY time_bucket ASC'

> query.group_by(time_bucket).order_by(time_bucket).get_sql()
> 'SELECT time_bucket(\'60.000s\',"time") AS "time_bucket", AVG(datapoint.value) AS "avg_value" FROM datapoint GROUP BY time_bucket ORDER BY time_bucket ASC'

[moritz89]

The workaround of using the field name itself instead of the alias works.

[moritz89]

But gives wrong results, but at least the following works:

query = Query().from_table(...).where(...)
ordered_query = query.get_sql() + " GROUP BY 1 ORDER BY 1"
results = query.select(sql=ordered_query)