Flask Restplus return columns join query

I have two models that relate to each other. I want to make an inner join between the two models and return it in a specific path. The problem is that the disc only accepts the columns of a single model. Sorry for the bad writing. I want all Fields by ProcedureType.id, something like SELECT pt.name, pt.description, f.* FROM proceduretype pt INNER JOIN field f ON pt.id = f.procedure_type_id WHERE pt.id = 1. I attach the code.

Model ProcedureType

class ProcedureType(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    dependence_id = db.Column(db.Integer)
    name = db.Column(db.String(100))
    description = db.Column(db.String(500))
    requirements = db.Column(db.String(500))
    online = db.Column(db.Boolean, default=False)

    def __init__(self, dependence_id, name, description, requirements, online):
        self.dependence_id = dependence_id
        self.name = name
        self.description = description
        self.requirements = requirements
        self.online = online

Model Field

class Field(db.Model):
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    procedure_type_id = db.Column(db.Integer, db.ForeignKey(
        ProcedureType.id), nullable=False)
    name = db.Column(db.String(50), nullable=False, unique=True)
    label = db.Column(db.String(100))
    type = db.Column(db.String(100))
    default_value = db.Column(db.String(100))
    validations = db.Column(db.JSON)

    def __init__(self, procedure_type_id, name, label, type, default_value, validations):
        self.procedure_type_id = procedure_type_id
        self.name = name
        self.label = label
        self.type = type
        self.default_value = default_value
        self.validations = validations


def prueba_inner_join(id):
    return db.session.query(Field).join(ProcedureType).filter_by(id=id)


from flask import request
from flask_restplus import Resource

from .dto import ProcedureTypeDto, FieldDto
from . import service

field_ns = FieldDto.api
_field = FieldDto.field_procedure
class FieldList(Resource):
    def get(self, id):
        """List all procedure types"""
        return service.get_fields_procedure(id)


class FieldDto:
    api = Namespace('field', description='Field related operations')
    field_procedure = api.model('field_details', {
        'id': fields.Integer(readonly=True, description='The unique identifier'),
        'procedure_type_id': fields.String(required=True, description='Procedure type Id'),
        'name': fields.String(required=True, description='name'),
        'label': fields.String(required=True, description='label'),
        'type': fields.String(required=True, description='Input type'),
        'default_value': fields.String(required=True, description='Default value'),
        'validations': fields.String(required=True, description='validations'),

I want to return everything in a single json that I can organize to my liking as for example

  "procedure_name": "NAME PROCEDURE TYPE",
        "type": "text",
        "name": "name_field",
        "id": 2,
        "validations": "{'required':true}",
        "default_value": "",
        "label": "LABEL 2",
        "procedure_type_id": "1"
        "type": "text",
        "name": "name_field",
        "id": 2,
        "validations": "{'required':true}",
        "default_value": "",
        "label": "LABEL 2",
        "procedure_type_id": "1"

Thanks for your help

1 answer

  • answered 2018-09-17 06:32 Mikhail Burshteyn

    You need another model that will include your field_procedure as a nested list:

    class FieldDto:
        api = Namespace(...)
        field = api.model('field_details', ...)
        procedure = api.model('procedure', {
            'procedure_name': fields.String(...),
            'description': fields.String(...),
            'fields': fields.List(fields.Nested(field)),

    Also I noticed you don't have a relationship defined between your models.

    Add the following to your ProcedureType class, so that you'll be able to reference fields attribute on your ProcedureType instances.

    fields = db.relationship('Field')