How can I create a Schema in Marshmallow to reverse-nest queried data?

Question

Sorry if this sounds silly, but i'm trying to get all the books for an author. This is what I have:

class Author(db.Model):
  __tablename__='author'

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


class Book(db.Model):
  __tablename__='book'

  id = db.Column(db.Integer, primary_key=True)
  title = db.Column(db.String)
  author_id = db.Column(db.Integer, ForeignKey('author.id'))

  author_rel = relationship('Author', lazy='joined'), backref='book')

and I have my schemas:

class BookSchema(Schema):
  id = fields.Int()
  name= field.Str()

class BookSchema(Schema):
  id = fields.Int()
  title = field.Str()
  author = fields.Nested(Author)

So I can retrieve the books with the author and the authors. What I need here is to add a nested field with all the books of each author... I've been trying, but failing to do so. Is there an automatic way to get this?

I'm trying to join the tables in the query, but also failing to do it:

session.query(Author, Book).join(Book, Author.id == Book.author_id).all()

This gives me a (Author, Book) tuple, and I cannot map that into a concise json... How could I do that?

Answer 1

EDIT:

Ok, so apparently I didn't understand the concept of a relationship haha I could avoid all this trouble by simply adding a relationship to my Author entity:

class Author(db.Model):
    __tablename__='author'

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

    books = relationship('Book', lazy='joined', backref='author_book')

class Book(db.Model):
    __tablename__='book'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)
    author_id = db.Column(db.Integer, ForeignKey('author.id'))

    author = relationship('Author', lazy='joined', backref='book_author')

Then I could just populate my marshmallow schema normally and be happy

==================================================

The solution I used was:

session = Session()

        author_objects = session.query(Author).all()
        schema = AuthorSchema(many=True)
        author_list = schema.dump(author_objects).data

        book_objects = session.query(Book).all()
        schema = BookSchema(many=True)
        book_list = schema.dump(book_objects).data

        for index, author in enumerate(author_list):
            author_list[index]['books'] = [book for book in book_list if book['author_id'] == author['id']]

        session.close()
        return jsonify(author_list)

This feels kinda manual for me, I think there should be a better way to do this automatically using schemas. It's, after all, based on a relationship that exists.

This works, but would be slow for long lists... I preferred to do this using sql-alchemy + marshmallow directly...

Ideas?