Question

我有3个模型。

class Company(models.Model):

    name = models.CharField(_("Name"), max_length = 100, blank = True, null = True)
    address = models.CharField(_("Address"), max_length = 300, blank = True, null = True)

class WebSite(models.Model):

    WEBSITE_TYPE_CHOICES = (
        ( Google+ ,  Google+ ),
        ( Facebook ,  Facebook ),
        ( Orkut ,  Orkut ),
    )
    user = models.ForeignKey(User)
    website = models.URLField(blank = True, null = True)
    website_type = models.CharField(max_length = 15, choices = WEBSITE_TYPE_CHOICES, default =  Google+ )
    company = models.ForeignKey(Company, null=True, blank=True)

class Review(models.Model):

    rating = models.FloatField(blank = True, null = True) 
    review = models.TextField(blank = True, null = True)
    company = models.ForeignKey(Company, null=True, blank=True)
    website = models.ForeignKey(Website, null=True, blank=True)

我想显示我的数据。

CompanyA:

Google - 5 reviews, Avg 3.4
Orkut - 9 reviews, Avg 4.1
Facebook - 12 reviews, Avg 4.3
Anyother - 2 reviews, Avg 2

CompanyB:

Google - 2 reviews, Avg 3
Facebook - 5 reviews, Avg 4

我已经这样做了。

    final_list = company_list = []
    websites = Website.objects.select_related().filter(user = user)

    for website in websites:
        company = website.company
        if company is not None and company not in company_list:
            company_list.append(brand)
            company_websites = websites.filter(company = company)
            info = []
            for company_website in company_websites:
                company_type = company_website.website_type
                reviews = Review.objects.filter(company = company, website = company_website)
                no_of_reviews = len(reviews)
                average = reviews.aggregate(Avg( rating ))
                info.append({"type": company_type, "no_of_reviews": no_of_reviews,
                             "average": average[ rating__avg ]})
            final_list.append({"company": company.name, "stats": info})

www.un.org/spanish/ecosoc 准则有两个问题。

It is wasting a lot of computation
It also somehow appends empty lists in the final_list (BUGGY)

FYI: I believe .annotate() can be used, but I fail to understand how can I use it based on a column, which in my case is (company__name)

Answer 1

这将使你获得数据。

Review.objects.values(company, website__website_type).annotate(review_count=Count( id ), average=Avg( rating )).order_by( company )

使用{% regroup %}在模板中产出。

Answer 2

我没有检测过这一点,这或许与你收到的其他答复不一样,但如果它帮助......。

companies = Company.objects.all()
company_ratings = []

for company in companies:
    websites = company.website_set.filter(user=user)
    new_data = []

    for website in websites:
        reviews = Reviews.objects.filter(website=website, company=company)
        ratings = [x.rating for x in reviews]
        count = len(ratings)
        new_data.append({ website : website,  count : count, 
                          average : float(sum(ratings))/count})

    company_ratings.append({ company :company,  ratings :new_data})

友情链接