1

Django ORM will map this queryset

TestModel.objects.filter(module="test", deleted=False)

to this raw sql:

select coll1, coll2, coll3 from test_module where (TestModel.deleted=0 and TestModel.module="test")

No matter where you put the "deleted" in the filter paras, it takes the first place it the where clause.

My first guess is that Django put all bool/tinyint field before others, but test shows not. Seems Django just put the field named "deleted" in front of others. Quite odd, but why?

Because I am adding indexes to mysql tables, the order really matters.

Shady Xu
  • 5,527
  • 2
  • 14
  • 18

1 Answers1

1

The keyword parameters are handled as a dictionary, which have arbirtary ordering. The deleted key just happens to come first in that ordering:

>>> {'module': 'Test', 'deleted': False}
{'deleted': False, 'module': 'Test'}

Ordering in a dictionary is determined by the slot to which they hash in the underlying hash table. A small dictionary starts with 8 slots, and 'deleted' hashes to slot 0, 'module' to slot 6:

>>> hash('module') % 8
6
>>> hash('deleted') % 8
0

In other words, it is entirely a coincidence that 'deleted' is listed first, due to the specific implementation details of Python dictionaries. Django does not decide to list that column first on purpose.

The order doesn't matter here, the SQL database doesn't care what comes first either.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • Thanks. But When I filter(module="test", deleted=False, trashed=False), deleted still comes first, even before trashed. – Shady Xu Dec 25 '13 at 02:34
  • Why is that a surprise? What makes you think `'trashed'` would be slotted before `'deleted'`? It could have been, but it isn't. There are other keys that *may* be slotted in hash slot `0` too, and then `deleted` might not be first. If you create a larger number of keys, the order could change too. – Martijn Pieters Dec 25 '13 at 02:38
  • There is **nothing in Django** that puts `deleted` first on purpose. It is a *coincidence* that it is listed first only due to the specific implementation details of Python dictionaries. – Martijn Pieters Dec 25 '13 at 02:39
  • I am not saying deleted always comes first, I am trying to find the regulation of it. The order matters because I am adding mysql indexes. – Shady Xu Dec 25 '13 at 04:00
  • No, for indexes the order doesn't matter either. See http://stackoverflow.com/questions/15479928/why-the-order-in-python-dictionaries-is-arbitrary for more information on dict ordering. – Martijn Pieters Dec 25 '13 at 09:11