Nested schema for an association table

Question

I'm having trouble serializing my Joke data with a schema when I've added an association table.

Here is my model it allows users to enter a joke and also a tag for categorizing the joke:

class Joke(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    joke_name = db.Column(db.String(40), nullable=False)
    joke = db.Column(db.String(5000), nullable=False)
    joke_owner = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    joke_category = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=True)
    joke_created = db.Column(db.DateTime, nullable=True)
    joke_edited = db.Column(db.DateTime, nullable=True)
    tags = db.relationship('JokeTag', secondary=tag_joke, backref=db.backref('jokes', lazy='dynamic'), lazy='dynamic',
            primaryjoin=tag_joke.c.joke_id==id,
            secondaryjoin=tag_joke.c.joketag_id==JokeTag.id)


class JokeTag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tag = db.Column(db.String(25), nullable=False)
    tag_owner = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

Here is my association table that connects the two on a many-to-many basis:

tag_joke = db.Table('tag_joke', 
        db.Column('joke_id', db.Integer, db.ForeignKey('joke.id')),
        db.Column('joketag_id', db.Integer, db.ForeignKey('joke_tag.id'))
        )

I am trying to serialize the data by using the following schema:

class TagJokeSchema(ma.Schema):
    class Meta:
        fields = ('joke_id', 'joketag_id')



class JokeSchema(ma.Schema):
    class Meta:
        fields = ('id', 'joke_name', 'joke', 'joke_owner', 'joke_category', 'joke_created', 'joke_edited', 'tags')

        tags=ma.Nested(TagJokeSchema, many=True)

I am doing something wrong with the schema because when I try to serialize the data with this:

allnotes = Joke.query.filter_by(joke_owner=1).all()
result = jokes_schema.dump(allnotes)

I get this TypeError:

TypeError: Object of type AppenderBaseQuery is not JSON serializable

When I try to access the jokes and tags from the python command line I am able to access the Joke and corresponding tags. Although when I try and print a joke it does look a little crazy:

Joke('35','No reason','Finance people get buff for no reason.  Just to work on Excel spreadsheets.','1','55', None, None, SELECT joke_tag.id AS joke_tag_id, joke_tag.tag AS joke_tag_tag, joke_tag.tag_owner AS joke_tag_tag_owner 
FROM joke_tag, tag_joke 
WHERE tag_joke.joke_id = %s AND tag_joke.joketag_id = joke_tag.id)

I was hoping to get a cleaner print with tags nested in there if they have them.

edit for @Greg0ry: The printout of allnotes looks like this:

[Joke('25','Test Joke','Knock, Knock, Whos There, test joke','1','55', None, None, SELECT joke_tag.id AS joke_tag_id, joke_tag.tag AS joke_tag_tag, joke_tag.tag_owner AS joke_tag_tag_owner FROM joke_tag, tag_joke WHERE tag_joke.joke_id = %s AND tag_joke.joketag_id = joke_tag.id), Joke('35','No reason','Finance people get buff for no reason.  Just to work on Excel spreadsheets.','1','55', None, None, SELECT joke_tag.id AS joke_tag_id, joke_tag.tag AS joke_tag_tag, joke_tag.tag_owner AS joke_tag_tag_owner FROM joke_tag, tag_joke WHERE tag_joke.joke_id = %s AND tag_joke.joketag_id = joke_tag.id)]

I also have this within my Joke class that explains the printout, I got this from a youtube tutorial I found I believe (Corey Shafer):

    def __repr__(self):
        return f"Joke('{self.id}','{self.joke_name}','{self.joke}','{self.joke_owner}','{self.joke_category}', {self.joke_created}, {self.joke_edited}, {self.tags})"

as far as db.Model, I may not understand what you're looking for but I have this:

app = Flask(__name__)
db = SQLAlchemy(app)

Hopefully there's a way to guide me a little more on what you're looking for?

Also if I look for a particular joke by doing

jokequery = Joke.query.filter_by(joke_owner=1, id=595).first()

I get

Joke('595','test joke','knock knock whos there test joke','1','475', None, None, SELECT joke_tag.id AS joke_tag_id, joke_tag.tag AS joke_tag_tag, joke_tag.tag_owner AS joke_tag_tag_owner 
FROM joke_tag, tag_joke 
WHERE tag_joke.joke_id = %s AND tag_joke.joketag_id = joke_tag.id)

obviously the last part of that looks pretty bad. This is because tags is an AppenderBaseQuery

but if I run this:

jokequery.tags.all()

the output looks great and is a list which is JSON serializable:

[JokeTag('45', 'stackoverflow', '1')]

But how do I set this up so that I can send in that list to be serialized and not the query?

Answer 1

The problem is the lazy='dynamic' setting on the relationship which

... will return a pre-configured Query object for all read operations ...

We can remove this setting (and also replace the legacy backref):

# Some columns removed for brevity and readability.

class JokeTag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tag = db.Column(db.String(25), nullable=False)
    tag_owner = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    jokes = db.relationship('Joke', secondary='tag_joke', back_populates='tags')


class Joke(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    joke_name = db.Column(db.String(40), nullable=False)
    joke = db.Column(db.String(5000), nullable=False)
    joke_owner = db.Column(
        db.Integer, db.ForeignKey('user.id'), nullable=False
    )
    tags = db.relationship(
        'JokeTag',
        secondary=tag_joke,
        back_populates='jokes'
    )

If you would prefer to retain the lazy='dynamic' setting, you can post-process the serialised output to get the result of the query:

...
class JokeSchema(ma.Schema):
    class Meta:
        fields = (
            'id',
            'joke_name',
            'joke',
            'joke_owner',
            'joke_category',
            'joke_created',
            'joke_edited',
            'tags',
        )

        tags = ma.fields.Nested(TagJokeSchema, many=True)

    # Assuming ma in the question is Marshmallow

    @ma.post_dump
    def dump_tags(self, obj, **kw):
        obj['tags'] = [t.name for t in obj['tags'].all()]
        return obj