CODEWITHSUNDEEP
pythonmysqlsqldjango
Radolino
Radolino

Reputation: 1844

How to count the amount of objects in a django joined table?

My problem is simple: I have Users who own Assets or Assets which belong to Users If you prefer and I cannot make it to retrieve the number (count) of Assets each User has. I know this might be sound silly to most of you but I am new to python/django (coming from PHP/MySQL) and I do not know how things work here. I do not want to be engaged with raw SQL - this would be my last choice If nothing else works.

(*) I have removed all non-related raws from the code

Users

class Users(models.Model):
    firstname = models.CharField(max_length=100)
    lastname = models.CharField(max_length=100)

Assets

class Assets(models.Model):
    serial = models.CharField(unique=True, max_length=100)
    user = models.ForeignKey('Users', blank=True, null=True)

    # this is what I am playing with to retrieve the number of assets each user owns
    @classmethod
        def user_assets(self):
            return Assets.objects.filter(user=user).count()

views.py 

class UserList(ListView):
    model = Users
    def get_context_data(self, **kwargs):
        context = super(UserList, self).get_context_data(**kwargs)
        context['user_assets'] = self.model.user_assets()
        return context

template 

{% for user in object_list %}
     <tr>
         <td>{{ user.id }}</td>
         <td>
             {{ user_assets }}
         </td>
     </tr>
{% endfor %}

How can I get that number? I have read about aggregations, annotations and filters but can't really get it.

EDIT:

I am looking for a simple solution by using class based views and easily expandable (I may want to add other models later)

Upvotes: 3

Views: 2893

Answers (3)

odinho - Velmont
odinho - Velmont

Reputation: 21516

You are doing weird things. Use the related managers that django give you instead. I'll write the view as a function based view:

views.py

def users_list(request):
    object_list = Users.objects.all()
    render(request, 'mytemplate.html', { 'object_list': object_list })

You can get the counts directly in the template via the RelatedManager:

mytemplate.html

{% for user in object_list %}
 <tr>
     <td>{{ user.id }}</td>
     <td>
         {{ user.assets_set.count }}
     </td>
 </tr>
{% endfor %}

You could also annotate with a count. But learn to float before you swim :)

BTW, you should call your models "User" and "Asset", not Users and Assets.

Upvotes: 2

Pawel Miech
Pawel Miech

Reputation: 7822

You need to use select_related(), count() and pass user instance as argument to class method like so:

@classmethod
def user_assets(cls,user):
    return Assets.objects.select_related('Users').filter(user=user).count()

and then use it like so:

user = Users.objects.all()[0] # some user object (this assumes you have at least one user)
Assets.user_assets(user)

this should work fine, you can try it in the shell.

In your context this will be used like this:

user = self.model.all()[0] # or get or filter just get some particular user
context['user_assets'] = Assets.user_assets(user)

EDIT: added links, and Users.object.all() instead of Users.object.get(), also added example suited to your specific use case.

Upvotes: 1

mariodev
mariodev

Reputation: 15549

In your UserList instead using model, use this queryset:

from django.db.models import Count

class UserList(ListView):
    queryset = Users.objects.annotate(num_assets=Count('assets'))

and define your user field like so:

user = models.ForeignKey('Users', blank=True, null=True, related_name='assets')

then from template:

{{ user.num_assets }}

Also please remember, it's a good practice to use singular model names, to avoid confusion with reverse relation names..

Upvotes: 3

Related Questions