Offset query issue while using MSSQL

[bigg01]

Error while query the Model :
sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause

Why?
MSSQL requires an order_by when using an offset.

Setup:

• fastapi
• MSSQL Server 2019
• PyODBC and SqlAlchemy

Original code:

sqladmin/sqladmin/models.py

Line 261 in 2205de1

query = select(cls.model).limit(page_size).offset((page - 1) * page_size)

My change:
https://github.com/bigg01/sqladmin/blob/main/sqladmin/models.py#L262

#  sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause
        query = select(cls.model).order_by("id").limit(page_size).offset((page - 1) * page_size)

I think in general it would make sense to add an option for ordering ?
What do you think ?

Regards

[aminalaee]

Hey, Thank you for reporting this.

Yes I think that makes sense.
Generally the LIMIT and OFFSET wouldn't be consistent if we don't use an ORDER BY with it. I think Django has a warning about that too.

Anyway, feel free to create a PR for this but we should probably use select(cls.model).order_by(cls.pk_column) instead of hard-coding the id column.