Django: Queryset using unnecessary extra tables

When using QuerySet.filter(), be sure to group operations on one table together. Otherwise, you may run into duplicate results due to tables being referenced more than once.

The following (simplified) example causes table A to be included more than once:

view sourceprint?

1.q = TestModel.objects.filter(user__firstname = 'John')

2.q = q.filter(user__surname = 'Smith')

This will cause the table "user" to be included in the SQL query twice. Using "print q.query" to debug:

view sourceprint?

1.SELECT testmodel.*

2.FROM testmodel

3.INNER JOIN user ON (testmodel.user_id = user.id)

4.INNER JOIN user T3 ON (testmodel.user_id = T3.id)

5.WHERE (user.firstname = 'John' AND

6.T3.surname = 'Smith')

Now this isn't exactly ideal, because we want the person with the name "John Smith", not a combination of anyone with the first name "John" and anyone with the last name "Smith".

Instead, we should group the filters into one so it applies to the one table.

view sourceprint?

1.q = TestModel.objects.filter(user__firstname = 'John', user__surname = 'Smith')

Which produces the following SQL query:

view sourceprint?

1.SELECT testmodel.*

2.FROM testmodel

3.INNER JOIN user ON (testmodel.user_id = user.id)

4.WHERE (user.firstname = 'John' AND

5.user.surname = 'Smith')

This will return all users with the name "John Smith".

When you DO want the query to reference the same table multiple times, use multiple another filter but remember to restrict it accordingly.

This doubling of tables is especially helpful when a system uses "tags" to help categorise information into various categories.