Re: Please help with complex aggregation/annotation issue
On Wed, Jan 18, 2012 at 1:03 AM, Salvatore Iovene <
salvatore.iov...@gmail.com> wrote:
> Hi,
> I apologize for the lousy title, but I really didn't know how to summarize
> the problem I'm facing. I have the following model:
>
> class MessierMarathon(models.Model):
> messier_number = models.IntegerField()
> image = models.ForeignKey(Image)
> nominations = models.IntegerField(default = 0)
> nominators = models.ManyToManyField(User, null=True)
>
> def __unicode__(self):
> return 'M %i' % self.messier_number
>
> class Meta:
> unique_together = ('messier_number', 'image')
> ordering = ('messier_number', 'nominations')
>
>
> A typical content for this model would be:
>
>Image A: 5 nominations for messier_number 1
>Image B: 4 nominations for messier_number 1
>Image C: 6 nominations for messier_number 2
>...and so on.
>
> I would like to formulate a query that returns me one image for each
> messier_number, picking the one with the most nominations. So, in the
> previous example, the query would return images A and C. The image B would
> not be returned because image A has more nominations for messier_number 1.
>
> The images returned must be sorted by messier_number, and of course may
> repeat. (An image might contain more that one Messier object, and get
> highest nomination counts for both).
>
The other edge case that you need to consider (and this is the one that
makes this more than a simple aggregation query) -- what should be returned
if two images have the same number of nominations for a given
messier_number? Are both of them returned, or is there another tie-breaking
criterion?
It's easy to get the highest nomination count for each messier number:
MessierMarathon.objects.values('messier_number').annotate(Max('nominations'))
What is harder is getting a unique image attached to each messier_number,
given that value for nominations.
A simple solution, which unfortunately requires a database hit for each
messier_number, would be:
highest_counts =
MessierMarathon.objects.values('messier_number').annotate(Max('nominations'))
top_images = dict((x['messier_number'],
MessierMarathon.objects.filter(messier_number=x['messier_number'],
nominations=x['nominations'])[0].image) for x in highest_counts)
You might be better off writing raw SQL for it, though; you could probably
get it all with one (convoluted) query.
--
Regards,
Ian Clelland
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: Please help with complex aggregation/annotation issue
Each user nominates just one Messier number/image pair?
Do you need to keep track of the users to verify that each one votes only
once?
On Wed, Jan 18, 2012 at 4:03 AM, Salvatore Iovene <
salvatore.iov...@gmail.com> wrote:
> Hi,
> I apologize for the lousy title, but I really didn't know how to summarize
> the problem I'm facing. I have the following model:
>
> class MessierMarathon(models.Model):
> messier_number = models.IntegerField()
> image = models.ForeignKey(Image)
> nominations = models.IntegerField(default = 0)
> nominators = models.ManyToManyField(User, null=True)
>
> def __unicode__(self):
> return 'M %i' % self.messier_number
>
> class Meta:
> unique_together = ('messier_number', 'image')
> ordering = ('messier_number', 'nominations')
>
>
> A typical content for this model would be:
>
>Image A: 5 nominations for messier_number 1
>Image B: 4 nominations for messier_number 1
>Image C: 6 nominations for messier_number 2
>...and so on.
>
> I would like to formulate a query that returns me one image for each
> messier_number, picking the one with the most nominations. So, in the
> previous example, the query would return images A and C. The image B would
> not be returned because image A has more nominations for messier_number 1.
>
> The images returned must be sorted by messier_number, and of course may
> repeat. (An image might contain more that one Messier object, and get
> highest nomination counts for both).
>
> Can anybody please help with this?
> Thanks in advance,
> Salvatore.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/django-users/-/GB-T19nk21cJ.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Please help with complex aggregation/annotation issue
Hi,
I apologize for the lousy title, but I really didn't know how to summarize
the problem I'm facing. I have the following model:
class MessierMarathon(models.Model):
messier_number = models.IntegerField()
image = models.ForeignKey(Image)
nominations = models.IntegerField(default = 0)
nominators = models.ManyToManyField(User, null=True)
def __unicode__(self):
return 'M %i' % self.messier_number
class Meta:
unique_together = ('messier_number', 'image')
ordering = ('messier_number', 'nominations')
A typical content for this model would be:
Image A: 5 nominations for messier_number 1
Image B: 4 nominations for messier_number 1
Image C: 6 nominations for messier_number 2
...and so on.
I would like to formulate a query that returns me one image for each
messier_number, picking the one with the most nominations. So, in the
previous example, the query would return images A and C. The image B would
not be returned because image A has more nominations for messier_number 1.
The images returned must be sorted by messier_number, and of course may
repeat. (An image might contain more that one Messier object, and get
highest nomination counts for both).
Can anybody please help with this?
Thanks in advance,
Salvatore.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/GB-T19nk21cJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
