Allow function results in group by

[MrAngry]

Allow grouping by function result. This is especially useful with datetime fields. A simple example from postgresql :

SELECT EXTRACT(YEAR from date_column) FROM sales group by EXTRACT(YEAR from date_column)

You can see how this is useful when creating for example yearly, monthly or daily sales results. Without this functionality you need to denormalize your table so year , month and day stays in separate columns.

[long2ice]

try https://tortoise-orm.readthedocs.io/en/develop/functions.html#custom-functions with group_by

[MrAngry]

try https://tortoise-orm.readthedocs.io/en/develop/functions.html#custom-functions with group_by

I am not exactly sure what this gives me. I already created a custom function for EXTRACT but the group_by function does not allow me plugin it in as it expects strings. And if you try it anyway you get:

AttributeError: 'Extract' object has no attribute 'split'

[long2ice]

Could you show your code with models here? I can try it.

[MrAngry]

Could you show your code with models here? I can try it.
Sure:

class Sales(Model):
    product_model = ForeignKeyField("models.ProductModel") 
    district = ForeignKeyField("models.District")
    count = DecimalField(max_digits=6, decimal_places=0)
    date = DateField()

My custom extract work well with annotate

from pypika.functions import Extract
from tortoise.functions import Function

class Extract(Function):
   database_func = Extract

   def _get_function_field(
           self, field: "Union[ArithmeticExpression, Field, str]", *default_values
   ):
       return self.database_func(*default_values,field)

Usage example:

sales= Sales.all()
sales = sales.annotate(year=Extract(F('date'), 'YEAR')).filter(year__in=request.year)

[MrAngry]

I could also add code for ProductModel and District but I think it is not important as those fields do not play any role here. And trying to use annotation does not work as well:

sales= Sales.all()
sales = await sales.annotate(year=Extract(F('date'), 'YEAR')).group_by('year').values('year')

tortoise.exceptions.FieldError: Unknown field "year" for model "Sales"

[long2ice]

OK, this should be a new feature