Group by for many to many table problem

[acast83]

Hi, I am encountering an issue with how Tortoise ORM handles grouping in queries involving many-to-many tables. Specifically, when I attempt to group by a single field in a many-to-many intermediary table, the SQL query produced by Tortoise ORM includes additional fields in the GROUP BY clause, leading to incorrect aggregation results.

this is the model class

class Recipe2Ingredient(Model):
    class Meta:
        app = "recipes"
        table = "recipes_recipe2ingredient"

    id = fields.UUIDField(pk=True)
    recipe = fields.ForeignKeyField(
        'recipes.Recipe',
        related_name='ingredients'
    )
    ingredient = fields.ForeignKeyField(
        'recipes.Ingredient',
        related_name='recipes'
    )

this is the query

query = models.Recipe2Ingredient.annotate(
                count=Count("ingredient_id")
            ).group_by("ingredient_id").order_by("-count").limit(5).prefetch_related("ingredient")

I expected this query to produce raw sql query:

SELECT "ingredient_id", COUNT("ingredient_id") AS "count"
FROM "recipes_recipe2ingredient"
GROUP BY "ingredient_id"
ORDER BY "count" DESC
LIMIT 5

but I got

'SELECT "recipe_id","id","ingredient_id",COUNT("ingredient_id") "count" FROM "recipes_recipe2ingredient" 
GROUP BY "recipe_id","id","ingredient_id" 
ORDER BY COUNT("ingredient_id") DESC
 LIMIT 5'

My main problem is with the grouping part because instead of getting GROUP BY "ingredient_id" I got GROUP BY "recipe_id","id","ingredient_id" and this is totally incorrect.

Is there something I was doing wrong or is this is a tortoise bug? If so, can you please fix this bug.

Thanks