Ian Ward's email:
first name at this domain
wardi on OFTC, freenode and github
Django comes with an amazingly powerful database API that does a very good job of hiding database-specific SQL. Sometimes, though, the API doesn't do exactly what you expect. I ran into this when developing the code that displays the “Tags” that have stories posted.
Fortunately, there is a fairly easy way to get down to the SQL and see what is happening when something goes wrong.
My Article model has a ManyToMany field to my Tag model. So my first attempt at getting all the tags that had stories posted for the current site looked something like:
tags = Tag.objects.filter(articles__site=settings.SITE_ID)
Unfortunately, this returned all the tag objects instead of just the ones I wanted.
I didn't know why all the tags were being returned, so I modified the query to add an intentionally invalid item:
tags = Tag.objects.filter(articles__site=settings.SITE_ID) tags = tags.extra(where=["break_my_sql_please"])
Then, when I executed the page I was given the usual Django traceback page, where I expanded the last “local vars” link to find that the SQL that was being executed included the following:
... "blag_tag" LEFT OUTER JOIN "blag_article_tags" ...
The “LEFT OUTER JOIN” meanes that tags would be returned even if the WHERE clause eliminates the articles that must be present. If there were no matching articles then the article would be NULL, so I worked around this problem by adding an extra filter:
tags = Tag.objects.filter(articles__site=settings.SITE_ID, articles__isnull=False)
And everything started working as intended.