Flask db pagination error "InstrumentedList object has no attribute limit "

Question

Is there a way to convert an InstrumentedList object and pass it to pagination?

models.py

tags = sa.Table(
    'tags',
    db.metadata,
    sa.Column('post_id', sa.Integer, sa.ForeignKey('post.id'), primary_key=True),
    sa.Column('tag_id', sa.Integer, sa.ForeignKey('tag.id'), primary_key=True)
)

class Post(db.Model):
    __tablename__ = 'post'
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    body: so.Mapped[str] = so.mapped_column(sa.Text)
    timestamp: so.Mapped[datetime] = so.mapped_column(index=True, default=lambda: 
    tags: so.Mapped[List['Tag']] = so.relationship(secondary=tags, back_populates='posts', cascade='all, delete')

class Tag(db.Model):
    __tablename__ = 'tag'
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    name: so.Mapped[str] = so.mapped_column(sa.String(16), index=True)
    posts: so.Mapped[List['Post']] = so.relationship(secondary=tags, back_populates='tags', passive_deletes=True)

routes.py

bp.route('/tags/<name>', methods=['GET'])
def tags(name):
    page = request.args.get('page', 1, type=int)
    tag = db.session.scalar(sa.select(Tag).filter(Tag.name == name.upper()))
    pagination = db.paginate(tag.posts, page=page, per_page=current_app.config['POSTS_PER_PAGE'], error_out=False)
    posts = pagination.items
    return render_template('index.html', title=_('Tags'), posts=posts, pagination=pagination)

error InstrumentedList object has no attribute limit

I tried

tag= sa.select(Tag).filter(Tag.name == name.upper())

then

pagination = db.paginate(tag, page=page, per_page=current_app.config['POSTS_PER_PAGE'], error_out=False)

but I get <function post at 0x7fee136c9d80> instead of a post object that I can loop over to display

Answer 1

If you want to apply pagination to all posts that have a specific tag, you can use the join statement in your query. The referenced and referencing primary and foreign keys are used to make a linked query. You will finally receive a pagination object which you can use as described here.

pagination = db.paginate(
    db.select(Post)
        .join(tags, tags.c.post_id == Post.id)
        .join(Tag, tags.c.tag_id == Tag.id)
        .where(sa.func.upper(Tag.name) == name.upper()), 
    page=page, 
    per_page=current_app.config['POSTS_PER_PAGE'], 
    error_out=False
)