select latest comments for multiple objects at once

Question

Say I have a Comment Model like this:

class Comment(models.Model):
    user = models.ForeignKey(User)
    content = models.CharField(max_length=200)
    create_time = models.DateTimeField(blank=True, default=datetime.datetime.now)

    content_type = models.ForeignKey(ContentType, verbose_name=_('content type'))
    object_id    = models.PositiveIntegerField(_('object id'), db_index=True)
    object       = generic.GenericForeignKey('content_type', 'object_id')

If it's possible for me to select latest several comments for multiple objects in the same content_type (say Posts, giving ids of those Posts) in one query?

If that's possible, how about select the oldest comment and latest 4 comments for those objects all in one query?

Thx!

Answer 1

You mention "giving ids of those Posts", but don't actually have any Post model defined (in your question at least). But, as you're asking to get all comments with the same content_type, I'm not sure how the Posts are needed.

The following query gets all Comment items that match a ContentType with a certain primary key (1), orders them by create_time and selects the first 4 items:

Comment.objects.filter(content_type__pk=1).order_by('-create_time')[:4]

Answer 2

I've had trouble with the greatest-n-per-group problem. I had to drop to raw SQL to get my results (top X questions per question category in my FAQ). There's no easy way to do this through the ORM.

This post: uses an interesting solution with SQL SQL join: selecting the last records in a one-to-many relationship

My solution involved subqueries. Since you're using generic keys, it may involve a little more head scratching.

I ended up just going back to the ORM with aggressive caching on this specific view, so I might recommend that. It's juts too much pain to keep up the SQL.

Foo.objects.raw('''
    SELECT ...
    FROM foo
    LEFT OUTER JOIN bar
    ON (foo.bar_id = bar.id ) 
    WHERE foo.id
    IN (
        SELECT subquery.id 
        FROM foo subquery 
        WHERE foo.bar_id = subquery.bar_id
        ORDER BY score DESC
        LIMIT %s
        )
    ORDER BY "baz" DESC;''', [5])