views:

169

answers:

2

The following query does what I'd like it to do, however, I have no idea if it's efficient. I went through the Django aggregation documentation, threw it together, looked at the query and tilted my head sideways like a confused dog.

What the query actually does is get published Entry's "name" and "name_slug" that have one or more approved comments and orders the results by the latest comment's "date_published" field. The results are a list of recently active Entry's.

So a few questions. (1) Is there anything you see in the query that's just a plain no-no. (2) Is there a way that I can see the RAW SQL that's querying the database?

Models:

class Entry(models.Model):
    name = models.CharField(max_length=200, unique=True)
    name_slug = models.SlugField(max_length=200, blank=True, editable=False, unique=True)
    date_published = models.DateTimeField()
    is_published = models.BooleanField(default=False)

class Comment(models.Model):
    entry = models.ForeignKey('Entry')
    date_published = models.DateTimeField()
    approved_choices = (('N', 'No'), ('Y', 'Yes'), ('M', 'Needs Moderation'),)
    approved = models.CharField(max_length=1, choices=approved_choices, default='N')

Query:

active_entry_list = Entry.objects
    .values('name', 'name_slug')
    .filter(is_published=True, comment__approved='Y')
    .annotate(latest_comment=Max('comment__date_published'),comments=Count('comment'))
    .filter(comments__gte=1)
    .order_by('-latest_comment')
    [:6]
+5  A: 

2) Yes, if settings.DEBUG is true, the raw sql queries are stored in django.db.connection.queries.

http://blog.michaeltrier.com/2007/8/11/display-the-sql-django-orm-is-generating

edgars
+1  A: 

I've reduced the query to this:

active_entry_list = Entry.objects
    .values('name', 'name_slug')
    .filter(is_published=True, comment__approved='Y')
    .annotate(latest_comment=Max('comment__date_published'))
    .order_by('-latest_comment')
    [:6]

If it's filtering for comment__approved='Y' then there's no need to count the comments and filter them again with comments__gte=1.

I'll look into django.db.connection.queries. It seams simple. Thanks!

Ty