从Django过滤器调用返回的列表的默认顺序是什么？ [英] What is the default order of a list returned from a Django filter call?

问题描述

简短问题

当连接到PostgreSQL数据库时，从Django过滤器调用返回的列表的默认顺序是什么？

背景

我自己承认， 应用程序层，因为返回列表的顺序将是常量，即不使用order_by。我查询的项目列表不是按字母顺序或任何其他故意顺序。它被认为保持与他们添加到数据库的顺序相同。

这个假设对于数百个查询是正确的，但是当订单在不知不觉中改变时，我的应用程序报告了一个失败。据我所知，在这段时间里，这些记录都没有被触动，因为我是唯一维护数据库的人。为了增加混乱，当在Mac OS X上运行Django应用程序时，它仍然按预期工作，但在Win XP中，它更改了顺序。 （注意上面提到的数百个查询是在Win XP上）。

对于这一点，任何洞察都将是有帮助的，因为我在Django或PostgreSQL文档中找不到任何解释操作系统差异的文档。

示例调用

  required_tests = Card_Test.objects.using（get_database（））。filter（name__icontains = key）
  

今天与我的同事交谈之后，我已经提出了与BjörnLindqvist一样的答案。回头看，我明白为什么经常这样做是错误的。使用ORM Django，sqlalchemy或任何可以编写命令的好处之一就是无需知道或了解（详细）连接到的数据库。诚然，我恰好是这些用户之一。然而，这样做的另一方面是，如果不知道数据库的详细调试错误，这样会很麻烦，可能是灾难性的。

解决方案

有否DEFAULT ORDER ，这一点不能强调，因为每个人都这样做错误。

数据库中的表不是普通的html表，它是一组无序的元组。它经常令人惊讶的是程序员只用于MySQL，因为在特定数据库中，行的顺序通常是可预测的，因为它没有利用一些高级优化技术。例如，不可能知道哪些行将被返回，或者它们在以下任何查询中的顺序：

  select *从表限制10 
选择*从表限制10偏移10 
从表格顺序x限制10 
  

在最后一个查询中，如果列x中的所有值都是唯一的，则该顺序是可预测的。只要满足select语句的条件，RDBMS就可以按照任何顺序返回任何行。

尽管您可以在Django上添加一个默认的顺序级别，这使得它为每个非有序查询添加一个order by子句：

  class Table（models.Model）： 
 ... 
 class Meta：
 ordering = ['name'] 
  

请注意，如果由于某种原因您不需要订购行，则可能会拖动性能。

Short Question
What is the default order of a list returned from a Django filter call when connected to a PostgreSQL database?

Background
By my own admission, I had made a poor assumption at the application layer in that the order in which a list is returned will be constant, that is without using 'order_by'. The list of items I was querying is not in alphabetic order or any other deliberate order. It was thought to remain in the same order as which they were added to the database.

This assumption held true for hundreds of queries, but a failure was reported by my application when the order changed unknowingly. To my knowledge, none of these records were touched during this time as I am the only person who maintains the DB. To add to the confusion, when running the Django app on Mac OS X, it still worked as expected, but on Win XP, it changed the order. (Note that the mentioned hundreds of queries was on Win XP).

Any insight to this would be helpful as I could not find anything in the Django or PostgreSQL documentation that explained the differences in operating systems.

Example Call

required_tests = Card_Test.objects.using(get_database()).filter(name__icontains=key)

EDIT
After speaking with some colleague's of mine today, I had come up with the same answer as Björn Lindqvist.

Looking back, I definitely understand why this is done wrong so often. One of the benefits to using an ORM Django, sqlalchemy, or whatever is that you can write commands without having to know or understand (in detail) the database it's connected to. Admittedly I happen to have been one of these users. However on the flip-side of this is that without knowing the database in detail debugging errors like this are quite troublesome and potentially catastrophic.

解决方案

There is NO DEFAULT ORDER, a point that can not be emphasized enough because everyone does it wrong.

A table in a database is not an ordinary html table, it is an unordered set of tuples. It often surprises programmers only used to MySQL because in that particular database the order of the rows are often predictable due to it not taking advantage of some advanced optimization techniques. For example, it is not possible to know which rows will be returned, or their order in any of the following queries:

select * from table limit 10
select * from table limit 10 offset 10
select * from table order by x limit 10

In the last query, the order is only predictable if all values in column x are unique. The RDBMS is free to returns any rows in any order it pleases as long as it satisfies the conditions of the select statement.

Though you may add a default ordering on the Django level, which causes it to add an order by clause to every non-ordered query:

class Table(models.Model):
    ...
    class Meta:
        ordering = ['name']

Note that it may be a performance drag, if for some reason you don't need ordered rows.

这篇关于从Django过滤器调用返回的列表的默认顺序是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！