2

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!

zxygentoo
  • 213
  • 1
  • 2
  • 7

2 Answers2

4

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])
Community
  • 1
  • 1
Yuji 'Tomita' Tomita
  • 115,817
  • 29
  • 282
  • 245
0

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]
jro
  • 9,300
  • 2
  • 32
  • 37
  • 1
    What's the Post like really doesn't matter here. Giving the content_type_pk of Post as 1, Posts ids as [1,2,3,4], then all the comment of those four Posts is Comment.objects.filter(content_type__pk=1, object_id__in=[1,2,3,4], my question is how to get latest four comments for all four Posts. – zxygentoo Oct 25 '11 at 15:44