Menu
I'm building a food logging database in Django and I've got a query related problem.
I've set up my models to include (among other things) a Food model connected to the User model through an M2M-field "consumer" via the Consumption model. The Food model describes food dishes and the Consumption model describes a user's consumption of Food (date, amount, etc).
class Food(models.Model): food_name = models.CharField(max_length=30) consumer = models.ManyToManyField("User", through=Consumption) class Consumption(models.Model): food = models.ForeignKey("Food") user = models.ForeignKey("User") I want to create a query that returns all Food objects ordered by the number of times that Food object appears in the Consumption table for that user (the number of times the user has consumed the food).
I'm trying something in the line of:
Food.objects.all().annotate(consumption_times = Count(consumer)).order_by('consumption_times')` But this will of course count all Consumption objects related to the Food object, not just the ones associated with the user. Do I need to change my models or am I just missing something obvious in the queries?
This is a pretty time-critical operation (among other things, it's used to fill an Autocomplete field in the Frontend) and the Food table has a couple of thousand entries, so I'd rather do the sorting in the database end, rather than doing the brute force method and iterate over the results doing:
Consumption.objects.filter(food=food, user=user).count() and then using python sort to sort them. I don't think that method would scale very well as the user base increases and I want to design the database as future proof as I can from the start.
Any ideas?
762
In the Django framework, both annotate and aggregate are responsible for identifying a given value set summary. Among these, annotate identifies the summary from each of the items in the queryset. Whereas in the case of aggregate, the summary is calculated for the entire queryset.
Use Django's count() QuerySet method — simply append count() to the end of the appropriate QuerySet. Generate an aggregate over the QuerySet — Aggregation is when you "retrieve values that are derived by summarizing or aggregating a collection of objects." Ref: Django Aggregation Documentation.
In the Django model, there is one autogenerated ‘id’ field. You can use any of the fields (id name, mobile or name) to sort the queryset. Let’s sort the queryset on ‘id’ in ascending order. Use the not sign ‘-‘ to sort the queryset in reverse order aka descending order (desc).
What is annotation in Django? In general terms, annotations can be defined as commenting or adding notes with appropriate message/text. In Django, annotations are used to add additional columns to queryset objects while querying.
The annotate () the method generates count clause and it is grouped by author.id given in values ('author') method. The filter () method filters queryset and return only those authors count who has books published. This query orders field num_books in the highest order.
Perhaps something like this?
Food.objects.filter(consumer__user=user)\ .annotate(consumption_times=Count('consumer'))\ .order_by('consumption_times') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
