Query N rows around the row id with complex ordering in Django ORM
According to the reference id, I want to query N rows, so that the row with the reference id would be in the middle. I know how to do it when the QuerySet is simply ordered by id column, but my solution falls apart when I want to order the results by a column other than id (as ids are not sequential anymore) or by multiple columns (e.g. (title, created_at)).
For example, if I had 5 items in the table below, and wanted to query 3 items with a reference id of 3, it would select rows with id 2, 3 and 4:
| Id | Title |
|---|---|
| 1 | One |
| 2 (Selected) | Two |
| 3 (Selected) | Three |
| 4 (Selected) | Four |
| 5 | Five |
I tried using Window() function with RowNumber() expression to assign row numbers to a sorted list of items, and then query items before and after the reference id row number, which, unsurprisingly, did not work as row numbers are recomputed on each query.