1

If the ordering field in the inner Meta class of a model is not specified does does the generated SQL query return results with items at randomized positions?

If true then pagination (slicing for offsets) will return duplicates on next pages, right?

De-Great Yartey
  • 1,574
  • 2
  • 12
  • 16
  • 1
    It simply does *not* specify an order in the SQL query, so the database has the freedom to return in any order. Typically it is not "random" in the sense that it is *always* different. It will depend on the query itself. For example a `JOIN` can change the order. If there is no order, *some* databases will return elements in reverse "edit timestamp" order, although this is *not* something you should depend upon. – Willem Van Onsem Sep 30 '18 at 18:17
  • 1
    As for pagination, it is therefore indeed better to specify an deterministic order. You can for example order on the `pk` in case no other order makes sense, since the edit of some records can change the order of the queryset, and hence of the pagination. – Willem Van Onsem Sep 30 '18 at 18:18
  • Okay I get it. But I don't want to show users an ordered results so I can avoid bias. Having the results in no order while applying pagination would have been a bigggg plus for me. Anyways, I'll develop my workaround – De-Great Yartey Sep 30 '18 at 18:24
  • you can for example order by something that appears random, like the substring of the description starting from the firth character. This *looks* random. – Willem Van Onsem Sep 30 '18 at 18:25

2 Answers2

3

It's not random but Django will not add an order by to your query. You can see it by running this command:

print(str(YourModel.objects.all()[:2].query))

You can check this answer https://stackoverflow.com/a/20050403/4175590 to see how the database will work in this case.

Oleksandr Dashkov
  • 2,249
  • 1
  • 15
  • 29
2

If the ordering field in the inner Meta class of a model is not specified does does the generated SQL query return results with items at randomized positions?

If you do not specify an order (nor through the Meta, or by an .order_by clause in the queryset), then Django will construct a query like:

SELECT model.*
FROM model

(perhaps with other clauses, but not with an ORDER BY statement).

This means that the database has the freedom to return the record in any order. It does not mean that the database will "shuffle" the records, but you simply can not say for sure in what order the records will be retrieved.

Some databases will return the record in (reverse) "edit timestamp" order, so the most recent added/edited records first. But this is not something that you should depend on (you can see this as an implementation detail).

It furthermore can also depend on the query itself if there are JOINs involved, since then the table can get ordered due to the JOINing process.

But in general the idea is that it can return the records in any order, and changes to the table, can result in changes in the ordering.

This has some implications on pagination, since if a user moves to the next page, and meanwhile the order changes, the queryset will not per se have the same order when the user fetches the next page. Therefore you better order the queryset (in one way or another). For example by the id.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555