1

I have a model Post, and a model Vote. Vote (form django-voting) is essentially just a pointer to a Post and -1, 0, or 1.

There is also Tourn, which is a start date and an end date. A Post made between the start and end of a Tourn is submitted to that tournament.

For the sake of rep calculation, I'm trying to find the top 3 winners of a tournament. This is what I have:

    posts = Post.objects.filter(status=2, created_at__range=(tourn.start_date, tourn.end_date))

    start = tourn.start_date - timedelta(days=1)
    end = tourn.end_date + timedelta(days=1)
    qn = connection.ops.quote_name
    ctype = ContentType.objects.get_for_model(Post)

    posts.extra(select={'score': """
            SELECT SUM(vote)
            FROM %s
            WHERE content_type_id = %s
            AND object_id = %s.id
            AND voted_at > DATE(%s)
            AND voted_at < DATE(%s)
        """ % (qn(Vote._meta.db_table), ctype.id, qn(Post._meta.db_table), start, end)},
                order_by=['-score'])

    if tourn.limit_to_category:
        posts.filter(category=tourn.category)

    if len(posts) >= 1:
        tourn_winners_1.append(posts[0].author)
        resp += " 1: " + posts[0].author.username + "\n"

    if len(posts) >= 2:
        tourn_winners_2.append(posts[1].author)
        resp += " 2: " + posts[1].author.username + "\n"

    if len(posts) >= 3:
        tourn_winners_3.append(posts[2].author)
        resp += " 3: " + posts[2].author.username + "\n"

It seems simple enough, but for some reason the results are wrong.

The query that gets made is thus:

SELECT "blog_post"."id", "blog_post"."title", "blog_post"."slug", "blog_post"."a
uthor_id", "blog_post"."creator_ip", "blog_post"."body", "blog_post"."tease", "b
log_post"."status", "blog_post"."allow_comments", "blog_post"."publish", "blog_p
ost"."created_at", "blog_post"."updated_at", "blog_post"."markup", "blog_post"."
tags", "blog_post"."category_id" FROM "blog_post" WHERE ("blog_post"."status" =
2  AND "blog_post"."created_at" BETWEEN 2008-12-21 00:00:00 and 2009-01-04 00:00
:00) ORDER BY "blog_post"."publish" DESC

It seems that posts.extra() isn't getting applied to the query at all...

defrex
  • 15,735
  • 7
  • 34
  • 45
  • Can you post the results of calling 'print posts.query' after you've fully constructed your query set? I think it might help to see the final SQL that has been strung together. – Joe Holloway Feb 13 '09 at 13:30

1 Answers1

3

I think you need to assign posts to the return value of posts.extra():

posts = posts.extra(select={'score': """
                    SELECT SUM(vote)
                    FROM %s
                    WHERE content_type_id = %s
                    AND object_id = %s.id
                    AND voted_at > DATE(%s)
                    AND voted_at < DATE(%s)
                """ % (qn(Vote._meta.db_table), ctype.id, qn(Post._meta.db_table), start, end)},
                        order_by=['-score'])
strager
  • 88,763
  • 26
  • 134
  • 176
  • this was right, and worked. However I'm still now getting the right order. :( – defrex Feb 13 '09 at 21:56
  • @defrex, Is it not being sorted at all? Or is it being sorted improperly (wrong direction)? Be more specific, please. – strager Feb 13 '09 at 22:05
  • It's not being sorted at all. The score for every post is None, so nothing ranks higher then anything else. – defrex Feb 13 '09 at 22:23
  • @defrex, Try testing your subquery and see what you get in return. That may help you track your bug down. – strager Feb 13 '09 at 22:28
  • lol. okay, so your original answer was correct. The reason I wasn't getting anything back for score was because my dev environment was using a version of the db imported after the end of the last tournament, and the import reset all the dates. – defrex Feb 13 '09 at 22:57
  • Is that a too complex query to be implemented with the recently added aggregate support in Django's ORM? – akaihola Feb 14 '09 at 11:58
  • it's a batch job, so I'm not to concerned. – defrex Feb 14 '09 at 21:14