json - Linking tables in SQLAclhemy foreign_keys error [Flask] -

i got 2 models post , category.

class post(db.model):     __tablename__ = 'posts'     id = db.column(db.integer, primary_key=true)     title = db.column(db.string(80))     body = db.column(db.text)     timestamp = db.column(db.datetime, index=true, default=datetime.utcnow)     author_id = db.column(db.integer, db.foreignkey('users.id'))     body_html = db.column(db.text)     comments = db.relationship('comment', backref='post', lazy='dynamic')      category_id = db.column(db.integer, db.foreignkey('categories.id'))     category = db.relationship('category',                                backref=db.backref('posts', lazy='dynamic'))         @staticmethod     def on_changed_body(target, value, oldvalue, initiator):         allowed_tags = ['a', 'abbr', 'acronym', 'b', 'blackquote', 'code', 'em', 'i', 'li', 'ol', 'pre', 'strong',                         'ul', 'h1', 'h2', 'h3', 'p']         target.body_html = bleach.linkify(bleach.clean(             markdown(value, output_form='html'),             tags=allowed_tags, strip=true))      def to_json(self):         json_post = {             'url': url_for('api.get_post', id=self.author_id, _external=true),             'title': self.title,             'body': self.body,             'body_html': self.body_html,             'author': url_for('api.get_user', id=self.author_id,                               _external=true),             'comments': url_for('api.get_post_comments', id=self.id, _external=true),             'comment_count': self.comments.count(),             'category': self.category,             'category_id': self.category_id          }         return json_post      @staticmethod     def from_json(json_post):         body = json_post.get('body')         if body none or body == '':             raise validationerror('post not have body')         return post(body=body)  db.event.listen(post.body, 'set', post.on_changed_body)  class category(db.model):     __tablename__ = 'categories'     id = db.column(db.integer, primary_key=true)     name = db.column(db.string(80))      def to_json(self):         json_category = {             'category_id': self.id,             'category_name': str(self.name),             'category_url': url_for('api.get_category', id=self.id, _external=true),          }         return json_category      def __str__ (self):         return '%s' % self.name 

i have linked post categories adding following post model,

category_id = db.column(db.integer, db.foreignkey('categories.id')) category = db.relationship('category',                                    backref=db.backref('posts', lazy='dynamic')) 

which worked great! can choose whatever category have posts , can see them under respective categories using /category/<name>. trying create api (json) , can see models to_json functions. created view see posts , it's info

@api.route('/posts/') def get_posts():     page = request.args.get('page', 1, type=int)     pagination = post.query.paginate(         page, per_page=current_app.config['posts_per_page'],         error_out=false)     posts = pagination.items     prev = none     if pagination.has_prev:         prev = url_for('api.get_posts', page=page-1, _external=true)     next = none     if pagination.has_next:         next = url_for('api.get_posts', page=page+1, _external=true)     return jsonify({         'posts': [post.to_json() post in posts],         'prev': prev,         'next': next,         'count': pagination.total     }) 

but trying create view see posts each category category/<name>/posts/, thought need link category post, since linked post category able see posts under respective categories. tried add following category model:

post_id = db.column(db.integer, db.foreignkey('posts.id')) 

and created view see posts under category json following error.

invalidrequesterror: 1 or more mappers failed initialize - can't proceed initialization of other mappers. original exception was: not determine join condition between parent/child tables on relationship post.category - there no foreign keys linking these tables. ensure referencing columns associated foreignkey or foreignkeyconstraint, or specify 'primaryjoin' expression.

at first assumed should many-to-many relationship, it's category posts should one-to-many relationship same when linked posts categories. confused need add or fix make work?

so of @dirn got work acessing backref posts have made in post class under category backref=db.backref('posts', lazy='dynamic'))

in route category/<name>/posts/ kind of

@api.route('/category/<int:id>/posts/') def get_category_posts(id): category = category.query.get_or_404(id) page = request.args.get('page', 1, type=int) pagination = category.posts.order_by(post.timestamp.asc()).paginate( page, per_page=current_app.config['comments_per_page'], error_out=false) posts = pagination.items prev = none if pagination.has_prev: prev = url_for('api.get_category_posts', page=page - 1, _external=true) next = none if pagination.has_next: next = url_for('api.get_category_posts', page=page + 1, _external=true) return jsonify({ 'posts': [post.to_json() post in posts], 'prev': prev, 'next': next, 'count': pagination.total })

the way accessed category.posts in pagination = category.posts.order_by(post.timestamp.asc())