Geek Answers Handbook

How to return the most popular items in a table, but where is each item unique?

I am trying to figure out a complex Django request for which I hope you can help. I have this model:

class ActiveVenue(models.Model): event = models.ForeignKey(Event) venue = models.ForeignKey(Venue) class Venue(models.Model): name = models.CharField(max_length=200) class Event(models.Model): user = models.ForeignKey(User) name = models.CharField(max_length=200)

There are many events in my application, and each event can have several active places, therefore, my current data structure. I would prefer that your solution does not "change your model to foo", since this is an already deployed site, and I would like to keep the current structure of the model.

I want to write a query that returns the most popular places, but where the place is counted only once for each user. For example, if I have one user with four events, and each time they use the same place, I want to count this place only once when determining the most popular places.

To illustrate this, imagine this is my data:

 event: A user: bob venue: The Hill event: B user: bob venue: The Hill event: C user: bob venue: The Hill event: D user: jane venue: The Oaks event: E user: sarah venue: The Pound event: F user: david venue: The Pound event: G user: ron venue: The Oaks event: H user: erica venue: The Oaks

Here's a popular order:

 1. The Oaks 2. The Pound 3. The Hill

Any suggestions how I write a query for this that works for both postgres and sqlite (in other words, does not rely on distinct() (not supported in sqlite))?

Thanks!

+4
source share
4 answers

It works?

 from collections import Counter results = Counter([vid for vid, eid in ActiveVenue.objects.values_list("venue_id", "event_id").distinct()]
+1
source

See if something like this works

 ActiveVenue.objects.all().annotate(score=Count('event__user', distinct=True)).order_by('-score')
0
source

This is my version (test completion):

models.py

 class Venue(models.Model): name = models.CharField(max_length=200) def __unicode__(self): return self.name def ranking(self): count=0 actives = ActiveVenue.objects.filter( venue__name=self.name).values( 'event__user__username', 'venue__name').distinct() for active in actives: count += 1 return count

views.py

 def getRanking( anObject ): return anObject.ranking() def myview(request): venues = list(Venue.objects.filter()) venues.sort(key=getRanking, reverse=True) return render(request,'page.html',{'venues': venues})

Patterns

 {% for venue in venues %} {{forloop.counter}}. {{venue}}<br/> {% endfor %}

Output:

  • The oaks
  • Lb
  • The hill
0
source

Here is my solution to the problem. First, here is a query that will capture place identifiers and ratings corresponding to the respective users.

 testquery = ActiveVenue.objects.values("venue").annotate(score=Count("event__user", distinct=True)).order_by("-score")

Result

 [{'score': 3, 'venue': 2}, {'score': 2, 'venue': 3}, {'score': 1, 'venue': 1}]

Placement IDs will then be placed on the new list.

 query_ids = [item["venue"] for item in testquery] [2, 3, 1]

Then you need to get the corresponding objects of the object.

 tempresult = Venue.objects.filter(id__in=query_ids) [<Venue: The Hill>, <Venue: The Oaks>, <Venue: The Pound>

Finally, the list will be rethought to reorder Venue objects based on previous estimates.

 result = [venue for venue_id in query_ids for venue in tempresult if venue_id == venue.id] [<Venue: The Oaks>, <Venue: The Pound>, <Venue: The Hill>]

This gives the correct result based on test data.

0
source

More articles:


All Articles