django-aggregation

Is it "better" to have an update field or COUNT query?

In a Django App I'm working on I've got this going on: class Parent(models.Model): name = models.CharField(...) def num_children(self): return Children.objects.filter(parent=self).count() def avg_child_rating(self): return Child.objects.filter(parent=self).aggregate(Avg('rating')) class Child(models.Model)...

Django GROUP BY strftime date format

I would like to do a SUM on rows in a database and group by date. I am trying to run this SQL query using Django aggregates and annotations: select strftime('%m/%d/%Y', time_stamp) as the_date, sum(numbers_data) from my_model group by the_date; I tried the following: data = My_Model.objects.values("strftime('%m/%d/%Y', ...

Django equivalent of COUNT with GROUP BY

I know Django 1.1 has some new aggregation methods. However I couldn't figure out equivalent of the following query: SELECT player_type, COUNT(*) FROM players GROUP BY player_type; Is it possible with Django 1.1's Model Query API or should I just use plain SQL? ...

Is there any way to get aggregates returned in the same order they came in?

I have a list of db columns that are in a particular order. Those columns are passed to a queryset and will be made into an aggregate: for field in columns.as_list(): if field in AGG_FIELDS: args.append(Sum(field)) overall_totals = MyModel.objects.filter(user=request.user).aggregate(*args) This works fine, except that the...

How to create annotation on filtered data in Django ORM?

I have the following models: class ApiUser(models.Model): apikey = models.CharField(max_length=32, unique=True) class ExtMethodCall(models.Model): apiuser = models.ForeignKey(ApiUser) method = models.CharField(max_length=100) #method name units = models.PositiveIntegerField() #how many units method call cost ...

Display Django values() on Foreign Key in template as object instead of its id

I have a queryset in Django that calls Model.objects.values('item')... where 'item' is a Foreign Key. class Words(models.Model): word = models.CharField() class Frequency(models.Model): word = models.ForeignKey(Words) ... So this returns the item id and displays as an id in the template. How do I show the actual item value in ...

How to obtain a count of objects per auth.user?

I have a Project model similar to: class Project(models.Model): ... lead_analyst=models.ForeignKey(User, related_name='lead_analyst') ... I want to use django aggregation to return all users with a count of projects per user. Something like: models.User.objects.annotate(project_count=Count('project_set')) Only that does...

django: time range based aggregate query

Hi there! I have the following models, Art and ArtScore: class Art(models.Model): title = models.CharField() class ArtScore(models.Model): art = models.ForeignKey(Art) date = models.DateField(auto_now_add = True) amount = models.IntegerField() Certain user actions results in an ArtScore entry, for instance whenever y...

ProgrammingError when aggregating over an annotated & grouped Django ORM query

I'm trying to construct a query to get the "average, maximum and minimum number of items purchased per user". The data source is this simple sales records table: class SalesRecord(models.Model): id = models.IntegerField(primary_key=True) user_id = models.IntegerField() product_code = models.CharField() pr...

Django model manager didn't work with related object when I do aggregated query

Hi, all. I'm having trouble doing an aggregation query on a many-to-many related field. Here are my models: class SortedTagManager(models.Manager): use_for_related_fields = True def get_query_set(self): orig_query_set = super(SortedTagManager, self).get_query_set() # FIXME `used` is wrongly counted ret...

Django aggregation in templates?

I'm thinking a bit about the concept of Django aggregates. I don't quite "get" how they can be used in my case. Basically i have a three-tier hierarchy of objects in my model, and the lowest object (Bar) contain values I want to aggregate. class Bar(models.Model): amount = models.FloatField() class Foo(models.Model): bars = mod...

Django aggregation query on related one-to-many objects

Here is my simplified model: class Item(models.Model): pass class TrackingPoint(models.Model): item = models.ForeignKey(Item) created = models.DateField() data = models.IntegerField() class Meta: unique_together = ('item', 'created') In many parts of my application I need to retrieve a set of Item's and a...

Django Include Aggregate Sums of Zero

I'm working on a Django timesheet application and am having trouble figuring out how to include aggregate sums that equal zero. If I do something like: entries = TimeEntry.objects.all().values("user__username").annotate(Sum("hours")) I get all users that have time entries and their sums. [{'username': u'bob' 'hours__sum':49}, {'user...

Django aggregation on a date range

Hi all, I have been lurking and learning in here for a while. Now i have a problem that somehow i cannot see an easy solution. In order to learn django i am bulding an app that basically keeps track of booked items. What I would like to do is to show how many days per month for a selected year one item has been booked. i have the follow...

Django - Can you use property as the field in an aggregation function?

I know the short answer because I tried it. Is there any way to accomplish this though (even if only on account of a hack)? class Ticket(models.Model): account = modelfields.AccountField() uuid = models.CharField(max_length=36, unique=True) created = models.DateTimeField(auto_now_add=True) class Meta: ordering =...

Django: Count objects in a particular month

I have this model: class Person(models.Model): city = models.CharField(max_length=20, blank=True) added_date = models.DateField(default=datetime.date.today) I want to create a template/view that has a table of months and the number of people added that month (ie, 5 in january, 10 in february, 8 in march, etc.). I have a similar ...