CODEWITHSUNDEEP
pythondjango
Jono Bacon
Jono Bacon

Reputation: 103

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

I am trying to figure out a tricky Django query that I hope you can help with. 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)

In my app there are many Events and each event can have multiple active venues, hence my current data structure. I would prefer if your solution is not "change your model to foo" as this is already a deployed site and I would like to keep the current model structure.

I want to write a query that returns the most popular venues but in which a venue is only counted once for each user. As an example, if I have one user with four events and they use the same venue every time, I only want to count that venue once when determining the most popular venues.

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 the popular order would be:

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

Any suggestions how I write a query to do this that works on both postgres and sqlite (in other words, doesn't rely on distinct() (not supported in sqlite))?

Thanks!

Upvotes: 4

Views: 374

Answers (4)

Travis Nickles
Travis Nickles

Reputation: 1

Here is my take on the problem. First, here is a query that will grab the Venue ids and the scores corresponding to the related 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}]

Next, the venue ids will be placed in a new list.

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

Next, the corresponding Venue objects have to be obtained.

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

Finally, a list comprehension will be performed to re-order the Venue objects based on the scores obtained earlier.

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 proper result based on the test data.

Upvotes: 0

catherine
catherine

Reputation: 22808

This is my version (finish test):

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})

templates

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

Output:

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

Upvotes: 0

Owais Lone
Owais Lone

Reputation: 698

See if something like this works

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

Upvotes: 0

monkut
monkut

Reputation: 43840

Does this work? 

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

Upvotes: 1

Related Questions