Complex grouping and averages in Django

Question

I have a model like this:

class Author(models.Model): name = models.CharField(max_length=100) age = models.IntegerField() class Book(models.Model): published = models.DateTimeField() name = models.CharField(max_length=300, unique=True) authors = models.ForeignKey(Author) category = models.ForeignKey(Category) class Category(models.Model): name = models.CharField(max_length=100) 

I would like to consider Books every author. It was easy because I could do: Author.objects.values('name').annotate(count=Count('book')) .

I find it difficult to calculate them:

• number of unique categories for each author.
• average number of books written by an author in a category
• (This is heavy). The average duration between the first and last book written by the author in the category.

I am not an ORM query guru, and if I had to solve this problem, I would probably end up using a lot of loops and other iterations to figure it out, but I know that this is the wrong way to do this.

(I know that some of the things in this scenario are strange, for example, a unique restriction on the author’s name, but this was the best example I found to illustrate my problem.)

Any help on how I can do this?

Thanks.

Answer 1

Ouch, hard.

Since this is not information that you need to update frequently, I would suggest you add 3 fields to the Author model, and then add a post_save signal to the Book model: each time a book is created, you simply calculate these values ​​and save them in the Author model.

Then you can easily filter them, it will display very quickly, and the time for calculating it will be almost imperceptible if, in any case, saving occurs.