CODEWITHSUNDEEP
pythondjango
Ronald Langeveld
Ronald Langeveld

Reputation: 744

Django count queryset, show object instead of PK

I wrote a query to sort a count count query for biggest to smallest number. Basically a ranking system.

    users = Referrals.objects.values('friend_id').annotate(Count('friend_id')).order_by('-friend_id__count')

It returns the value in json format like this

<QuerySet [{'friend_id': 1, 'friend_id__count': 2}, {'friend_id': 5, 'friend_id__count': 1}]>

now friend_id appears to be the primary key and yes, it links to the correct one(s) in my DB.. but I have no idea to get it to let it output the actual name / email or whatever based on that PK?

This is my template where I'd like it to display the rankings.

                  {% for a in users %}
                  <tr>
                      <td>{{a}}</td>
                      <td>{{a}}</td>
                  </tr>

                  {% endfor %}

Here's my full view for what its worth.

@login_required
def statspage(request):
    users = Referrals.objects.values('friend_id').annotate(Count('friend_id')).order_by('-friend_id__count')
    print(users)

    context = {
        'users': users,
    }

    return render(request, 'stats.html', context)

One more question - is it possible to filter based on a boolean that exists in the User model?

Upvotes: 2

Views: 796

Answers (2)

anjaneyulubatta505
anjaneyulubatta505

Reputation: 11665

Try below code

users = Referrals.objects.all().annotate(
    friend_count=Count('friend_id')
).order_by('-friend_count')

When you use values it will only return the db column which you specified.

In the template you can access like

{% for user in users %}
      <tr>
        <td>{{user.first_name}}</td>
        <td>{{user.last_name}}</td>
        <td>{{user.friend_count}}</td>
      </tr>

 {% endfor %}

Upvotes: 0

Ozgur Vatansever
Ozgur Vatansever

Reputation: 52093

Assuming friend is a FK to User model that has fields named email and first_name (and last_name), you can specify those fields in the .values(...) part as follows:

Referrals.objects.values('friend_id', 'friend__email', 'friend__first_name') ...

Upvotes: 3

Related Questions