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:
q = TestModel.objects.filter(user__firstname = 'John')
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:
SELECT testmodel.*
FROM testmodel
INNER JOIN user ON (testmodel.user_id = user.id)
INNER JOIN user T3 ON (testmodel.user_id = T3.id)
WHERE (user.firstname = 'John' AND
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.
q = TestModel.objects.filter(user__firstname = 'John', user__surname = 'Smith') Which produces the following SQL query:
SELECT testmodel.*
FROM testmodel
INNER JOIN user ON (testmodel.user_id = user.id)
WHERE (user.firstname = 'John' AND
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.
