Reorder of SQLAlchemy Query results based on external ranking
The results of an ORM query (eg, MyObject.query()) need to be ordered according to a ranking algorithm that is based on values not within the database (ie from a separate search engine). This means 'order_by' will not work, since it only operates on fields within the database.
But, I don't want to convert the query results to a list, then reorder, because I want to maintain the ability to add further constraints to the query. Eg:
results = MyObject.query()
results = my_reorder(results)
results = results.filter(some_constraint)
Is this possible to accomplish via SQLAlchemy?
I am afraid you will not be able to do it, unless the ordering can be derived from the fields of the object's table(s) and/or related objects' tables which are in the database.
But you could return from your code the tuple (query, order_func/result) . In this case the query can be still extended until it is executed, and then resorted. Or you could create a small Proxy -like class, which will contain this tuple, and will delegate the query-extension methods to the query, and query-execution methods ( all(), __iter__ , ...) to the query and apply ordering when executed.
Also, if you could calculate the value for each MyObject instance beforehand, you could add a literal column to the query with the values and then use order_by to order by it. Alternatively, add temporary table, add rows with the computed ordering value, join on it in the query and add ordering. But I guess these are adding more complexity than the benefit they bring.
上一篇: SQLAlchemy：获得最大的平均值