I have created two models in Flask-Sqlalchemy. These models are Tickets
and Namespace
. Both models are connect with a foreign key. I also created marshmallow-sqlalchemy ModelSchema
definitions to use in a RESTful API.
class Ticket(db.Model):
id = db.Column(db.Integer, primary_key=True)
namespace = db.Column(
db.Integer, db.ForeignKey('namespace.id'), nullable=False)
title = db.Column(db.String)
description = db.Column(db.Text)
status = db.Column(db.String)
severity = db.Column(db.String)
class TicketSchema(ma.ModelSchema):
class Meta:
model = Ticket
include_fk = True
class Namespace(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
class NamespaceSchema(ma.ModelSchema):
class Meta:
model = Namespace
In my view I am trying to filter all tickets by given namespace. This is my query:
@app.route("/<string:name>", methods=["GET"])
def get_tickets_by_namespace(name):
tickets_query = Ticket.query.join(Namespace).filter(Namespace.name == name)
result = TicketSchema(many=True).dump(tickets_query).data
return jsonify({"tickets": result})
How do I access the name
attribute from the Namespace
model while joining and filtering two models?
When I enter the above URL using /aa
, I only get namespace_id
, I would like to also see the name of namespace:
{
"tickets": [
{
"description": "xcv",
"id": 1,
"namespace": 1,
"severity": "xcvbn",
"status": "xcvb",
"title": "xc"
},
{
"description": "xcv",
"id": 3,
"namespace": 1,
"severity": "xcvbnb",
"status": "axcvb",
"title": "axcb"
},
{
"description": "xcv",
"id": 4,
"namespace": 1,
"severity": "bnb",
"status": "axcvb",
"title": "aaaxcb"
}
]
}
Instead of using include_fk
, you want to give your models a relationship:
class Ticket(db.Model):
id = db.Column(db.Integer, primary_key=True)
namespace_id = db.Column(
'namespace',
db.Integer,
db.ForeignKey('namespace.id'),
nullable=False
)
namespace = db.relationship("Namespace", backref="tickets")
title = db.Column(db.String)
description = db.Column(db.Text)
status = db.Column(db.String)
severity = db.Column(db.String)
Relationships are automatically included, but their default field still only serialised the foreign keys.
Make sure to only define marshmallow-sqlalchemy models after all your SQLAlchemy models have loaded, as creating models will trigger a mapping configuration run (where string expressions such as the "Namespace"
string in the db.relationship()
call) are resolved. You can't create a Marshmallow model of the Ticket
model until the Namespace
model also has been created.
Next, use a Nested()
field to pull in the Namespace
attributes you want:
class TicketSchema(ma.ModelSchema):
class Meta:
model = Ticket
# don't include 'tickets' when including a namespace schema.
# you can also put this on the NestedSchema model.
namespace = ma.Nested("NamespaceSchema", exclude=("tickets",))
In the above example the exclude=("tickets",)
argument is needed to avoid the tickets
relationship on the Namespace
object to also be reflected in the resulting JSON (it'll contain only foreign keys, Marshmallow realises it already serialised the tickets). You can also add the exclude
to the NamespaceSchema
model Meta
, but then all use of that schema will exclude the tickets
field.
With the above changes, your route outputs:
{
"tickets": [
{
"description": "xcv",
"id": 1,
"namespace": {
"id": 1,
"name": "aa"
},
"severity": "xcvbn",
"status": "xcvb",
"title": "xc"
},
{
"description": "xcv",
"id": 3,
"namespace": {
"id": 1,
"name": "aa"
},
"severity": "xcvbnb",
"status": "axcvb",
"title": "axcb"
},
{
"description": "xcv",
"id": 4,
"namespace": {
"id": 1,
"name": "aa"
},
"severity": "bnb",
"status": "axcvb",
"title": "aaaxcb"
}
]
}