find_one with update ignores sorting

[KarlCMB]

Describe the bug
When using update immediately after find_one, the sorting order is ignored.
If I only use "find_one", the order is correct though

To Reproduce

reference_product = await Product.find_one(
Product.state == "new", sort=[+Product.priority]
)
updated_product = await Product.find_one(
Product.state == "new", sort=[+Product.priority]
).update(
{"$set": {"state":"processing"}}, 
response_type=UpdateResponse.NEW_DOCUMENT
)

assert reference_product.id == updated_product.id, "Not matching"

Expected behavior
I expect the order is correct

[roman-right]

Hi @KarlCMB ,
This is the MongoDB limitation. The code you wrote is translated into updateOne query, which doesn't support sorting: https://www.mongodb.com/docs/manual/reference/method/db.collection.updateOne/ .

But I'll think if it can be supported with a few workarounds. For now I'd suggest fetching and updeting after.

[KarlCMB]

Hi @roman-right
thank you for the answer.
If anyone faces the same challenge:
I copied your code from here to solve this issue.

[slingshotvfx]

Would it be possible to implement findAndModify?

I have a queue-style use case much like this but I'd love to be able to find, update, and return the most recent document all in one atomic query.

edit: I see find_and_modify is deprecated in pyMongo, but find_one_and_update might work

[KarlCMB]

@slingshotvfx just do a regular find and update with limit=1

[slingshotvfx]

@slingshotvfx just do a regular find and update with limit=1

That works, but returns a PyMongo UpdateResult so you still have to do a second query right after to read the just-updated item from the database.

[KarlCMB]

You can use the UpdateResponse to control the behaviour

[slingshotvfx]

UpdateMany doesn't take a response_type argument unfortunately.

So you can find_one and update, return the updated item, but that ignores sort order, or you can find and update with limit=1 which works with sorting but doesn't return the updated item or even the updated item id. Unless I'm missing something?