On Wed, Jan 18, 2012 at 1:03 AM, Salvatore Iovene <
[email protected]> 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
<[email protected]>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
