sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause

[mohitmittal94]

trafficstars

Hi,

I am using flask-rest-jsonapi and connecting it with MySQL server database. I am able to get a list of persons and computers (mentioned in your example), however when I try to use pagination filter page[number], it throws the following error. sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause

However, along with the page[number], if I also pass sort=name, it works.

After debugging I found the that we need to give a default sorting. I couldn't find any place in the library to put a default sort. However, I fixed it by placing the following code to the Person and Computer model definition:

__mapper_args__ = {
    "order_by":id
}

Now, if you don't specify a sort in the header, it by default sorts the result by id, so if you just provide the page[number] in the header, it would automatically also have a default sorting mechanism. This has fixed the issue for me.

Regards Mohit Mittal