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]