Inspired by http://www.djangosnippets.org/snippets/159/
This context processor provides a new variable {{ sqldebug }}, which can
be used as follows:
{% if sqldebug %}...{% endif %}
{% if sqldebug.enabled %}...{% endif %}
This checks settings.SQL_DEBUG and settings.DEBUG. Both need to be True,
otherwise the above will evaluate to False and sql debugging is considered
to be disabled.
{{ sqldebug }}
This prints basic information like total number of queries and total time.
{{ sqldebug.time }}, {{ sqldebug.queries.count }}
Both pieces of data can be accessed manually as well.
{{ sqldebug.queries }}
Lists all queries as LI elements.
{% for q in sqldebug.queries %}
<li>{{ q.time }}: {{ q }}</li>
{% endfor %}
Queries can be iterated as well. The query is automatically escaped and contains
<wbr> tags to improve display of long queries. You can use {{ q.sql }} to access
the unmodified, raw query string.
Here's a more complex example. It the snippet from:
http://www.djangosnippets.org/snippets/93/
adjusted for this context processor.
{% if sqldebug %}
<div id="debug">
<p>
{{ sqldebug.queries.count }} Quer{{ sqldebug.queries|pluralize:"y,ies" }}, {{ sqldebug.time }} seconds
{% ifnotequal sql_queries|length 0 %}
(<span style="cursor: pointer;" onclick="var s=document.getElementById('debugQueryTable').style;s.display=s.display=='none'?'':'none';this.innerHTML=this.innerHTML=='Show'?'Hide':'Show';">Show</span>)
{% endifnotequal %}
</p>
<table id="debugQueryTable" style="display: none;">
<col width="1"></col>
<col></col>
<col width="1"></col>
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">SQL</th>
<th scope="col">Time</th>
</tr>
</thead>
<tbody>
{% for query in sqldebug.queries %}<tr class="{% cycle odd,even %}">
<td>{{ forloop.counter }}</td>
<td>{{ query }}</td>
<td>{{ query.time }}</td>
</tr>{% endfor %}
</tbody>
</table>
</div>
{% endif %}
- sql
- debug
- queries
- db
- database
- contextprocessor
I know you're thinking, *what the heck could that title mean?*
I often find myself wanting to filter and order by the result of a COUNT(*) of a query using a method similar to the [entry_count example](http://www.djangoproject.com/documentation/db-api/#extra-select-none-where-none-params-none-tables-none). Writing this many times is tedious and hardcoding the table and column names made me cringe, I also wanted the counts to result from more complex queries.
This is a method you can add to your custom Manager to do this easily. It's not an ideal syntax, but it's good for the amount of code required.
Example: suppose we have some articles we want to filter and order by comments and visit logs to show the most popular...
class ArticleManager(models.Manager):
count_related = _count_related
class Article(models.Model):
pub_date = models.DateTimeField(auto_now_add=True)
objects = ArticleManager()
class Comment(models.Model):
article = models.ForeignKey(Article)
is_spam = models.BooleanField(default=False)
class Visit(models.Model):
article = models.ForeignKey(Article)
referrer = models.URLField(verify_exists=False)
search_query = models.CharField(maxlength=200)
Notice how the ArticleManager is given the `count_related` method. Now you can find the most popular like so...
Order by non-spam comments:
Article.objects.count_related(Comment.objects.filter(
is_spam=False)).order_by('-comment__count')
Order by incoming non-search-engine links:
Article.objects.count_related(Visit.objects.filter(
referrer__isnull=False, search_query__isnull=True),
'links').order_by('-links')
Order by total visits:
Article.objects.count_related(Visit).order_by('-visit__count')
Note: Doesn't work if `query` contains joins or for many-to-many relationships, but those could be made to work identically if there's demand.
- sql
- model
- db
- count
- manager
This is part of the user-registration code used on this site (see [the django-registration project on Google Code](http://code.google.com/p/django-registration/) for full source code), and shows a couple of interesting tricks you can do with manager methods.
In this case there's a separate `RegistrationProfile` model used to store an activation key and expiration time for a new user's account, and the manager provides a couple of useful methods for working with them: `create_inactive_user` creates a new user and a new `RegistrationProfile` and emails an activation link, `activate_user` knows how to activate a user's account, and `delete_expired_users` knows how to clean out old accounts that were never activated.
Putting this code into custom manager methods helps a lot with re-use, because it means that this code doesn't have to be copied over into different views for each site which uses registration, and also makes more sense in terms of design, because these are methods which need to "know about" the model and work with it, and so they belong in a place close to the model.