| 1 | | I filed a ticket #27388 for this same issue because Dango Rest Framework depended on the api of chaining filters as an AND. As Josh Smeaton pointed out chained filters are logically OR'd statements. I think this is so counter to intuition that it is reasonable to break backwards compatibility in an upcoming release. I think the current api is likely to cause more pain in the future than the pain that would be caused by breaking existing code. If a function gets passed a queryset it cannot assume that it can call filter and gather the results, because of the nature of OR, it will include more than desired if another filter had been previously applied. Perhaps most importantly, applying multiple joins for the purpose of ORing can lead to exponentially slow queries, because successive joins can have multiplicative factors on the number of rows. This was the original reason I filed the ticket, the successive joins resulted in a search of 1million rows for a table with originally several thousand. |
| 2 | | |
| 3 | | > I think I understand the point you're trying to make Steve. Do the docs here represent the different behaviours you're trying to put across? https://docs.djangoproject.com/en/1.10/topics/db/queries/#spanning-multi-valued-relationships |
| 4 | | > |
| 5 | | > Those docs primarily refer to multi-value relationships which I'm not certain is the use case you're trying to resolve. But to summarise: |
| 6 | | > |
| 7 | | > `.filter(multi__value__lookup=1, multi__value__other=2)` is equivalent to `(MVL=1 AND MVO=2)` because the WHERE clause targets a single join. |
| 8 | | > |
| 9 | | > {{{ |
| 10 | | > SELECT * FROM model |
| 11 | | > JOIN multi ON model.multi_id = multi.id |
| 12 | | > JOIN value on multi.value_id = value.id |
| 13 | | > WHERE value.MVL=1 AND value.MVO = 2 |
| 14 | | > }}} |
| 15 | | > |
| 16 | | > Where `.filter(multi__value__lookup=1).filter(multi__value__other=2)` is logically equivalent to `(MVL=1 OR MVO=2)` because MVL and MVO have separate joins, so they can both match independently since it's a multivalue relationship. |
| 17 | | > |
| 18 | | > {{{ |
| 19 | | > SELECT * FROM model |
| 20 | | > JOIN multi ON model.multi_id = multi.id |
| 21 | | > JOIN value on multi.value_id = value.id |
| 22 | | > JOIN multi m2 ON model.multi_id = m2.id |
| 23 | | > JOIN value v2 on multi.value_id = v2.id |
| 24 | | > WHERE value.MVL=1 AND v2.MVO = 2 |
| 25 | | > }}} |
| 26 | | > |
| 27 | | > Does this correctly summarise the behaviour? |
| | 1 | I filed a ticket #27388 for this same issue because Dango Rest Framework depended on the api of chaining filters as an AND. As Josh Smeaton pointed out chained filters are logically OR'd statements. I think this is so counter to intuition that it is reasonable to break backwards compatibility in an upcoming release. I think the current api is likely to cause more pain in the future than the pain that would be caused by breaking existing code. If a function gets passed a queryset it cannot assume that it can call filter and gather the results, because of the nature of OR, it will include more than desired if another filter had been previously applied. Perhaps most importantly, applying multiple joins for the purpose of ORing can lead to exponentially slow queries, because successive joins can have multiplicative factors on the number of rows. This was the original reason I filed the ticket, the successive joins resulted in a search of 1million rows for a table with originally several thousand. |