How Many to Many use offset and limit ?

[danielyangfei]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

class WallPaper(SQLModel,table=True):
     id: str | None = Field(default=None, primary_key=True)
     name:str | None=Field(default=None)
     url:str | None=Field(default=None)
     desinfo:str | None=Field(default=None) # 图片描述信息，用于后续 AI 进行搜索出来
     create_time:str | None=Field(default=None)
     update_time:str | None=Field(default=None)
     #tags:str | None = None # 图片的标签
     subject_id: str | None=Field(default=None,foreign_key="subject.id")
     tags:list["Tag"]=Relationship(back_populates="wallpapers", link_model=TagWallPaperLink)

class Tag(SQLModel,table=True):
    id: int | None = Field(default=None, primary_key=True)
    tagname:str | None=Field(default=None)
    wallpapers:list["WallPaper"]=Relationship(back_populates="tags", link_model=TagWallPaperLink)


class TagWallPaperLink(SQLModel,table=True):
    tag_id:int |  None = Field(default=None, foreign_key="tag.id", primary_key=True)
    wallpaper_id:str |  None = Field(default=None, foreign_key="wallpaper.id", primary_key=True)



def getBizhiByTag(session:Session):
    statement=select(Tag)
    tags=session.exec(statement).first()
    return ResInfo(
        status_code=ResCode.SUCCESS.code,
        message=ResCode.SUCCESS.message,
        data=tags.wallpapers
    )

Description

I follow the tutial , in my case ,tags.wallpapers can get a list of wallpaper,but I want to limit the number of the wallpapers to read for pagination .But I find no ways to to that

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.21

Python Version

3.11

Additional Context

No response

[danielyangfei]

I tried this way and problem solved：

def getBizhiByTag(tagid:int,page:int,page_size:int,session:Session):

# 计算分页的起始和结束索引
offset = (page - 1) * page_size
limit = page_size

 # 构建查询语句
statement = select(WallPaper).join(WallPaper.tags).where(Tag.id == tagid).limit(limit).offset(offset)

try:
    wallpapers = session.exec(statement).all()
    total_statement = select(WallPaper).join(WallPaper.tags).where(Tag.id == tagid)
    total = len(session.exec(total_statement).all())
    #count_statement = select(func.count(WallPaper.id)).where(WallPaper.tags.any(Tag.id == tagid))
    #total = session.exec(count_statement).scalar_one()

    
    return ResInfo(
        status_code=ResCode.SUCCESS.code,
        message=ResCode.SUCCESS.message,
        data={
        "wallpaper":wallpapers,
        "total":total,
        "page":page,
        "page_size":page_size
        }
    )
except Exception as e:
    raise HTTPException(status_code=500, detail=str(e))