Group list of objects by parent property and serialize output to dict

linus 0
Viewed 20

SETUP:

I have the three SQLAlchemy classes BusinessUnit, Task and Area that are in a relationship.

class BusinessUnit(db.Model)
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    db.relationship("Task", backref="business_unit")


class Task(db.Model)
    id = db.Column(db.Integer, primary_key=True)
    id_business_unit = db.Column(db.ForeignKey('business_unit.id'), nullable=False)
    id_area = db.Column(db.ForeignKey('area.id'), nullable=False)
    name = db.Column(db.String)

    @property
    def serialize():
        return {
            'id': self.id,
            'name': self.name,
         }


class Area(db.Model)
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

First I query the Tasks and filter by Area.

tasks = db.session.query(Task).join(Area).filter(Area.id == area).all()
print(tasks)
>>> [<Task 1>, <Task 2>, <Task 3>]

QUESTION:

Then the result should be serialized and grouped by BusinessUnit. The data must be retreived as a list of dicts. Example output would look like this:

serialized_tasks = [{
                      "business_unit_name": "Business Unit A",
                      "tasks": [{
                                  "id": 1,
                                  "name: "Task 1"
                               },
                               {
                                  "id": 2,
                                  "name: "Task 2"
                               }]
                    },
                    {
                     "business_unit_name": "Business Unit B",
                     "tasks": [{
                                  "id": 3,
                                  "name: "Task 3"
                               },
                    }]

I am really stuck and don't know how to get to the desired output. Unfortunately, I can't change the db model so querying the linking table (Task) seems to be the only way.

WHAT I HAVE TRIED:

Following Bernhards answer in this post, I got to the following output. It's very close to what I need, but unfortunately this is a dict containing lists.

serialized_tasks= []
res = {}

for item in tasks:
    res.setdefault(item.business_unit.name, []).append(item.serialize)
    serialized_tasks.append(res)


print(serialized_tasks)
>>> {"Business Unit A": [{
         "id": 1,
         "name": "Task 1"
     },
     {
         "id": 2,
         "name": "Task 2"
     }],
      "Business Unit B": [{
          "id": 3,
          "name": "Task 3"
      }]
      }
sqlalchemy serialization python
1 Answers

Every time you call res.setdefault(item.business_unit.name, []) there's a chance you create an unnecessary list, which is then going to be garbage collected. You can avoid this by using defaultdict:

from collections import defaultdict

name_to_tasks = defaultdict(list)
for item in tasks:
    name_to_tasks[item.business_unit.name].append(item.serialize)

result = [
    {"business_unit_name": business_unit_name, "tasks": tasks_}
    for business_unit_name, tasks_ in name_to_tasks.items()
]

However if you don't mind using third-party libraries, which add some sugar, I'd suggest using convtools (I must confess - I'm the author) - github.

from convtools import conversion as c

# this step can be done once, so the converter is reused just like any other
# function
converter = (
    c.group_by(c.attr("business_unit", "name"))
    .aggregate(
        {
            "business_unit_name": c.attr("business_unit", "name"),
            "tasks": c.ReduceFuncs.Array(c.attr("serialize")),
        }
    )
    .gen_converter()
)

converter(tasks)
westandskif0
Related