Support annotate F() expression cross model relation

[YAGregor]

Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

to reproduce:

import asyncio

from tortoise import Model, fields, Tortoise
from tortoise.expressions import F


async def test():
    return ""


class A(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=255, default="name_of_a")


class B(Model):
    id = fields.IntField(pk=True)
    a = fields.ForeignKeyField("models.A")
    name = fields.CharField(max_length=255, default="name_of_b")


async def main():
    await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()

    a = await A.create()
    b = await B.create(a=a)

    b2 = await B.annotate(a_name=F("a__name"), ya_b_name=F("name")).get(id=1)

    print(b2.a_name, b2.ya_b_name)  # in django orm we got b2.a_name == "name_of_a", but tortoise treat F(a__name") as string seems that F only works on fields in queryset's model its self

    await Tortoise.close_connections()


if __name__ == '__main__':
    asyncio.run(main())

Describe the solution you'd like A clear and concise description of what you want to happen.

F() be able to reference fields of foreign key's model

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Additional context Add any other context about the feature request here.

[YAGregor]

btw, I don't think F extend pypika's field is a good idea,I tried to implement this feature but seem its hard to extend feature. Is your code struct imitate Django ORM? I suggest to organize query like tree, and "dump" the tree recursively just like peewee

[fehimaltinisik]

Have you find a solution or an alternative approach to the problem?

I'm facing a similar issue. Trying to refer foreign key model's field. Following snippet didn't yield any result:

class Stop(Model):
    stop_id = fields.IntField(pk=True)
    stop_code = fields.CharField(max_length=32)
    stop_name = fields.CharField(max_length=255)


class StopTime(Model):
    stop: fields.ForeignKeyRelation['Stop'] = fields.ForeignKeyField(
        model_name='models.Stop',
        related_name='stop_times',
        source_field='stop_id'
    )

record = await StopTime.select_related('stop').annotate(stop_name=F('stop__stop_name').first()
print(record.stop_name)

[YAGregor]

Have you find a solution or an alternative approach to the problem?

I'm facing a similar issue. Trying to refer foreign key model's field. Following snippet didn't yield any result:

class Stop(Model):
    stop_id = fields.IntField(pk=True)
    stop_code = fields.CharField(max_length=32)
    stop_name = fields.CharField(max_length=255)


class StopTime(Model):
    stop: fields.ForeignKeyRelation['Stop'] = fields.ForeignKeyField(
        model_name='models.Stop',
        related_name='stop_times',
        source_field='stop_id'
    )

record = await StopTime.select_related('stop').annotate(stop_name=F('stop__stop_name').first()
print(record.stop_name)

no, I think it's almost impossible, to implement this, its code need a big refactor