How to serialize the complex query (peewee)

Question

I am using the peewee as ORM and my goal is to serialize the result of the complex query whcih also contains subqueries:

        machine_usage_alias = RecordDailyMachineUsage.alias()
        subquery = (
            machine_usage_alias.select(
                machine_usage_alias.machine_id,
                fn.MAX(machine_usage_alias.date).alias('max_date'),
            )
            .group_by(machine_usage_alias.machine_id)
            .alias('machine_usage_subquery')
        )

        record_subquery = RecordDailyMachineUsage.select(
            RecordDailyMachineUsage.machine_id, RecordDailyMachineUsage.usage
        ).join(
            subquery,
            on=(
                (RecordDailyMachineUsage.machine_id == subquery.c.machine_id)
                & (RecordDailyMachineUsage.date == subquery.c.max_date)
            ),
        )

        query = (
            Machine.select(
                Machine.id,  # 0
                Machine.name,
                Machine.location,
                Machine.arch,
                Machine.platform,
                Machine.machine_version,
                Machine.status,
                record_subquery.c.usage.alias('usage'),
                fn.GROUP_CONCAT(Tag.name.distinct()).alias('tags_list'),
                fn.GROUP_CONCAT(Project.full_name.distinct()).alias('projects_list'),
            )  # 10
            .join(MachineTag)
            .join(Tag)
            .switch(Machine)
            .join(MachineProject)
            .join(Project)
            .join(
                record_subquery,
                JOIN.LEFT_OUTER,
                on=(Machine.id == record_subquery.c.machine_id),
            )
            .where((Machine.id != 0) & (Machine.is_alive == 1))
            .group_by(Machine.id)
        )

I've tried to use the method model_to_dict:

jsonify({'rows': [model_to_dict(c) for c in query]})

But this way gives me the columns and and values from the Machine model only. My aim is include all the columns from the select query.

Answer 1

It turned out that I had to use the dicts method of the query and jsonify the result.

      machine_usage_alias = RecordDailyMachineUsage.alias()
        subquery = (
            machine_usage_alias.select(
                machine_usage_alias.machine_id,
                fn.MAX(machine_usage_alias.date).alias('max_date'),
            )
            .group_by(machine_usage_alias.machine_id)
            .alias('machine_usage_subquery')
        )

        record_subquery = RecordDailyMachineUsage.select(
            RecordDailyMachineUsage.machine_id, RecordDailyMachineUsage.usage
        ).join(
            subquery,
            on=(
                (RecordDailyMachineUsage.machine_id == subquery.c.machine_id)
                & (RecordDailyMachineUsage.date == subquery.c.max_date)
            ),
        )

        query = (
            Machine.select(
                Machine.id,  # 0
                Machine.name,
                Machine.location,
                Machine.arch,
                Machine.platform,
                Machine.machine_version,
                Machine.status,
                record_subquery.c.usage.alias('usage'),
                fn.GROUP_CONCAT(Tag.name.distinct()).alias('tags_list'),
                fn.GROUP_CONCAT(Project.full_name.distinct()).alias('projects_list'),
            )  # 10
            .join(MachineTag)
            .join(Tag)
            .switch(Machine)
            .join(MachineProject)
            .join(Project)
            .join(
                record_subquery,
                JOIN.LEFT_OUTER,
                on=(Machine.id == record_subquery.c.machine_id),
            )
            .where((Machine.id != 0) & (Machine.is_alive == 1))
            .group_by(Machine.id)
        ).dicts()
return jsonify({'rows': [c for c in query]})