model to dict -> subquery fields

[yash2001cp]

from database.db_session import db
from datetime import datetime
from peewee import *
from playhouse.postgres_ext import BinaryJSONField
from playhouse.shortcuts import model_to_dict

class User(Model):
    id = BigAutoField()
    name = CharField(unique=True, index=True)
    status = CharField(index=True, default="active")

    class Meta:
        database = db

class Audit(Model):
    id = BigAutoField()
    object = CharField(index=True)
    action = CharField(index=True)
    object_id = BigIntegerField(index=True)
    performed_by_id = BigIntegerField(index=True)
    data = BinaryJSONField(null=True)
    created_at = DateTimeField(default =datetime.now, index=True)

    class Meta:
        database = db

def main():
    User.drop_table()
    Audit.drop_table()
    User.create_table()
    Audit.create_table()

    user = User.create(name = "A")

    Audit.create(
        object = User._meta.table_name,
        object_id = user.id,
        data = {"name": "A"},
        action = "create",
        performed_by_id = 11
    )
    Audit.create(
        object = User._meta.table_name,
        object_id = user.id,
        data = {"status": "inactive"},
        action = "update",
        performed_by_id = 14
    )

    Latest = Audit.alias()

    latest_query = (
            Latest.select(
                Latest.object_id, fn.MAX(Latest.created_at).alias("max_created_at")
            )
            .group_by(Latest.object_id)
            .alias("latest_audit")
    )

    query = (
        User.select(
            User,
            Audit.performed_by_id,
        )
        .join(Audit, on=(User.id == Audit.object_id))
        .join(
            latest_query,
            on=(
                (Audit.object_id == latest_query.c.object_id)
                & ((Audit.created_at == latest_query.c.max_created_at))
            ),
        )
    )

    u = query.first()
    data = model_to_dict(u, fields_from_query=query)
    print(data["performed_by_id"])

getting key error; i am using prefetch so using model_to_dict kinda became mandatory (haven't added that here ) ; am i missing something ?? using model_to_dict(u, only = [User.id,Audit.performed_by_id]) doesn't work as well

[coleifer]

The issue has to do with Peewee putting the performed_by_id on u.audit.performed_by_id. Since there is no true foreign-key there, the introspection in model_to_dict() is not finding it. The workaround is to do something like this:

# Add call to ".objects()" in order to put the performed_by_id directly onto the User obj.
query = (User
         .select(User, Audit.performed_by_id,)
         .join(Audit, on=(User.id == Audit.object_id))
         .join(latest_query,
               on=((Audit.object_id == latest_query.c.object_id) &
                   (Audit.created_at == latest_query.c.max_created_at)))
         .objects())

# Specify the extra attr we want here:
u = query.first()
data = model_to_dict(u, extra_attrs=['performed_by_id'])
print(data)

The model_to_dict is not a panacea and can only do so much. You're far better off just writing your own serialization for anything beyond the most trivial case.