Nested JSON from adjacency list

Question

I have a Flask RESTful API app that has the following SQLAlchemy class with a self-referential key representative of an adjacency list:

class Medications(db.Model):
    __tablename__ = 'medications'
    id = Column(Integer, primary_key=True)
    type = Column(String(64))
    name = Column(String(64))
    parent_id = Column(Integer, ForeignKey('medications.id'))
    children = relationship("Medications")

I want nested JSON returned from the Medications class, along the lines of

"objects": [
    {
      "children": [
        {
          "id": 4, 
          "name": "Child1", 
          "parent_id": 3, 
          "type": "Leaf"
        }, 
        {
          "id": 5, 
          "name": "Child2", 
          "parent_id": 3, 
          "type": "Leaf"
        }
      ], 
      "id": 3, 
      "name": "CardioTest", 
      "parent_id": null, 
      "type": "Parent"
    }
  ], 

As per how-to-create-a-json-object-from-tree-data-structure-in-database I created the serializer class

class JsonSerializer(object):
    """A mixin that can be used to mark a SQLAlchemy model class which
    implements a :func:`to_json` method. The :func:`to_json` method is used
    in conjuction with the custom :class:`JSONEncoder` class. By default this
    mixin will assume all properties of the SQLAlchemy model are to be visible
    in the JSON output. Extend this class to customize which properties are
    public, hidden or modified before being being passed to the JSON serializer.
    """

    __json_public__ = None
    __json_hidden__ = None
    __json_modifiers__ = None

    def get_field_names(self):
        for p in self.__mapper__.iterate_properties:
            yield p.key

    def to_json(self):

        field_names = self.get_field_names()

        public = self.__json_public__ or field_names
        hidden = self.__json_hidden__ or []
        modifiers = self.__json_modifiers__ or dict()

        rv = dict()
        for key in public:
            rv[key] = getattr(self, key)
        for key, modifier in modifiers.items():
            value = getattr(self, key)
            rv[key] = modifier(value, self)
        for key in hidden:
            rv.pop(key, None)
        return rv

and subclassed this to my Medications class, as per class Medications(db.Model, JsonSerializer):

I then call Models.to_json() to get my serialized JSON output, but alas, the object is empty: {'parent_id': None, 'type': None, 'children': [], 'name': None, 'id': None}

However, as a test, if I create a Flask Restless endpoint, as per

manager = flask.ext.restless.APIManager(app, flask_sqlalchemy_db=db)
manager.create_api(Medications, methods=['GET'])

I get the following output:

"objects": [
    {
      "children": [
        {
          "id": 4, 
          "name": "Child1", 
          "parent_id": 3, 
          "type": "Leaf"
        }, 
        {
          "id": 5, 
          "name": "Child2", 
          "parent_id": 3, 
          "type": "Leaf"
        }
      ], 
      "id": 3, 
      "name": "CardioTest", 
      "parent_id": null, 
      "type": "Parent"
    }, 
    {
      "children": [], 
      "id": 4, 
      "name": "Child1", 
      "parent_id": 3, 
      "type": "Leaf"
    }, 
    {
      "children": [], 
      "id": 5, 
      "name": "Child2", 
      "parent_id": 3, 
      "type": "Leaf"
    }
  ], 

along with some pagination information.

Am curious why I am getting an empty dictionary from the method using the JsonSerializer class. I would use the Flask Restless method, but since I am using Flask as a wsgi app, it would screw up my endpoints, plus, the nodes with children: [] are not desired in the output.

Answer 1

The solution to my issue ended up being using Marshmallow with a nested schema (with a little assistance from this post creating-a-tree-from-self-referential-tables-in-sqlalchemy, ala

# create SQLAlchemy object
record = db.session.query(Medications). \
    options(joinedload_all("children", "children",
                           "children", "children",
                           "children", "children")).first()


class TestSchema(Schema):
    name = fields.Str()
    type = fields.Str()
    id = fields.Int(dump_only=True)
    parent_id = fields.Int(dump_only=True)
    children = fields.Nested('self', many=True)

schema = TestSchema()

result = schema.dump(record)

Worked like a charm, and no need to implement a recursive method to build the tree.