tortoise
pgsql group by error
Describe the bug Using group_by causes an error when two tables have the same field
To Reproduce `class Message(BaseModelMixin): createtime=fields.DatetimeField(auto_now_add=True) from_user=fields.ForeignKeyField('models.User',null=True,on_delete=fields.SET_NULL,related_name='from_user') ...
class User(BaseModelMixin): createtime=fields.DatetimeField(auto_now_add=True) ...
await Message.filter(to_user=request.user,is_read=0).annotate(count=Count("id"))
.group_by('from_user__id','from_user__nickname','content','createtime').order_by('-createtime')
.values('from_user__id','from_user__nickname','content','createtime')`
the sql is:
SELECT "message__from_user"."id" "from_user__id","message__from_user"."nickname" "from_user__nickname","message"."content" "content","message"."createtime" "createtime","message"."is_read" "is_read" FROM "message" LEFT OUTER JOIN "user" "message__from_user" ON "message__from_user"."id"="message"."from_user_id" WHERE "message"."to_user_id"=1 AND "message"."is_read"=0 GROUP BY "from_user__id","from_user__nickname","content","createtime","is_read" ORDER BY "message"."createtime" DESC
the error is :
tortoise.exceptions.OperationalError: 字段关联 "createtime" 是不明确的
If changed to the following, it will work fine:
... GROUP BY ... message."createtime" ORDER BY message."createtime" DESC
So, what's the right way to write it
