weird issue with await prefetching

[xalteropsx]

sorry for the tag little trouble on prefetch also look like dead community i cant get any idea what missing here

@YAGregor

[xalteropsx]

how does the model relationship work ? if we wait and return it doesnt return relationship model rather pass an generator object

[xalteropsx]

from tortoise.models import Model
from tortoise import fields, Tortoise, run_async
import asyncio

class Tournament(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()

    def __str__(self):
        return self.name

class Event(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()
    tournament = fields.ForeignKeyField(
        "models.Tournament", related_name="events")
    participants = fields.ManyToManyField(
        "models.Team", related_name="events", through="event_team")

    def __str__(self):
        return self.name

class Team(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()

    def __str__(self):
        return self.name

async def init():
    await Tortoise.init(db_url="postgres://postgres:480@localhost:5432/mytest", modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()

async def create_tournament(name):
    tournament = Tournament(name=name)
    await tournament.save()
    await Event.create(name="Without participants", tournament=tournament)
    event = await Event.create(name="Test", tournament=tournament)
    participants = []
    for i in range(2):
        team = await Team.create(name="Team {}".format(i + 1))
        participants.append(team)
    await event.participants.add(*participants)
    async for team in event.participants:
        pass
    for team in event.participants:
        pass

    selected_events = await Event.filter(participants=participants[0].id).prefetch_related("participants", "tournament")
    data = await Event.all().prefetch_related("participants")
    data2 = await Event.all()
    print(data[0].participants)

    print(data[0].participants)
    # await Tournament.filter(events__name__in=["Test", "Prod"]).order_by("-events__participants__name").distinct()

if __name__ == "__main__":
    run_async(init())
    asyncio.run(create_tournament("New Tournament"))

[xalteropsx]

we cannot do anything with these data unless we do await but the problem remain the same how do we return relationship using prefetching

[YAGregor]

I can explain, when python execute await Event.all(), in the sql level, the database driver execute select * from event, and retrieve all events from database, but it did not retrieve any data from tournament table, For the python expression event.tournament, if you want to retrieve data form tournament, driver must execute select * from tournament where id = ?, but execute sql is a block IO operation, in python asyncio, you must add await in front of every IO operation.

if you dont want add await for each event object, try await Event.all().select_related('tournament').prefetch_related('...'), this generate sql select * from event left join tournament ....

[YAGregor]

BTW, I'd glad to help anyone who use tortoise, but I've decide to left tortoise, no offensive to tortoise, as you said the community is no longer active, and I read the source code of tortoise found that I dont like its design

[xalteropsx]

same here but time was less i need to represent project singapore next month their is a event exhibition for startup company

thnx man u save my life really again :( i cant thank u so much

but i didnt able to understand the logic of prefetch what it purpose

[YAGregor]

prefetch is for making query faster, because every time python evaluate await event.tournament, the database driver communicate with database for one time (which cost a lot of time), if u write

for event in events:  # assume there is ten events
    await event.tournament

then sql statements would execute, that is very slow, instead of

for event in events.select_related("tournament"):  # assume there is ten events
    await event.tournament

only one sql statement would be executed, that is select * from event left join tournament ...., it might be about ten more times faster that the version which didn't use prefetch/select_related. @xalteropsx

[xalteropsx]

oh i see thnx i will read it little bit more and test it with large some of query with cprofile

[xalteropsx]

i try

[xalteropsx]

i want to return the object but it doenst return

[xalteropsx]

@YAGregor

[xalteropsx]

[xalteropsx]

feel free to drop answer

[RicarTang]

You can use the "from_orm" method of the pydantic model object to serialize the pre-selected data, as long as you make sure that the pre-selected parameters of the model class are the same as the associated parameters of the model model

    query_list = (
        await TestSuite.all()
        .prefetch_related("testcase")
        .offset(limit * (page - 1))
        .limit(limit)
    )

    testsuites_list = [
        testsuite_schema.TestSuiteTo.from_orm(testsuite) for testsuite in query_list
    ]

[xalteropsx]

@RicarTang thnx bro work like charm awesome >.< also thnx to yagregor who gave idea to default off to do both >.<

[RicarTang]

that's great!-------- 原始邮件 --------发件人： xalteropsx @.>日期： 2023年11月6日周一 15:07收件人： tortoise/tortoise-orm @.>抄送： RicarTang @.>, Mention @.>主 题： Re: [tortoise/tortoise-orm] weird issue with await prefetching (Issue #1509) @YAGregor @RicarTang

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: @.***>

[xalteropsx]

@YAGregor when i bulk post object i dont get id back why is that

@router.post("/users")
async def create_user(users: List[UserIn_PY]):
    # try:
    async with in_transaction():

user_dicts = [dict(user) for user in users] user_obj = await Users.bulk_create([Users(**user) for user in user_dicts])

        user_obj = await Users.bulk_create([Users(**dict(user)) for user in users])
        return user_obj

[YAGregor]

@YAGregor when i bulk post object i dont get id back why is that

@router.post("/users")
async def create_user(users: List[UserIn_PY]):
    # try:
    async with in_transaction():

~ user_dicts = [dict(user) for user in users] user_obj = await Users.bulk_create([Users(**user) for user in user_dicts]) ~

        user_obj = await Users.bulk_create([Users(**dict(user)) for user in users])
        return user_obj

U just can't get id back with bulk create, it's by design

[xalteropsx]

but i able to get the id in sqlalchmey with bulk why it cannot ? it make it really difficult to make the array append if we update something on vue or react if the id was not their it make really difficult to target its path

[YAGregor]

U can't with sqlalchemy, too.except u create one by one, bulk create insert many lines in one sql statement, one sql statement returns one number, it can't get id back because the way it works

[xalteropsx]

nvm u look right haha its same as add