CODEWITHSUNDEEP
pythondjangodjango-queryset
Ibolit
Ibolit

Reputation: 9720

Django select indirectly related with a condition

In my project, I have Users and they can have a certain role. However, the roles are limited by date ranges, that is a user can have an admin role from 01.01.2020 to 02.02.2020, and an unprivileged user role from 02.02.2020 to 03.03.2020.

I need to be able to return the list of users with their current roles and I want to be able to make just one query to the database. However, I can't figure out how to do that.

Here are my models: 

class User(models.Model):
    ...

class Role(models.Model):
    name = models.CharField(...)

class UserRoleQuerySet(models.QuerySet):
    def current():
        return self.filter(period__contains=date.today())

class UserRoles(models.Model):
    user = models.ForeignKey(User, related_name='roles')
    role = models.ForeignKey(Role)
    period = DateTimeRangeField()

    objects = UserRoleQuerySet.as_manager()

I need a queryset with either a prefetch_related, select_related or annotate method called on it so that when I then do something like code below, I don't get additional database trips:

users = User.objects.(some_magic_here).all()
for user in users:
    print(user.role)

UPD: To be more specific, if I don't do any annotations, prefetches or select_related, I will get a printout of all (current) user roles. However, each iteration will hit the db to select that current role. What I want is that same printout, but without additional roundtrips to the db

Upvotes: 0

Views: 521

Answers (1)

Yellowduck
Yellowduck

Reputation: 492

To get a set of objects you will need prefetch_related, although you cannot alter (filter) the query after the prefetch as Django won't be able to do the join. So you will need to provide the filter when Django is doing the prefetch:

users = Users.objects.prefetch_related(
    Prefetch(
        'roles',
        queryset=(
            UserRoles.objects
                .filter(period__contains=date.today())
                .select_related('role')
        )
    )
)

When you iterate through users, again if you alter the query it will take one extra hit to the database per iteration, so instead of doing:

for user in users:
    print(user.roles.first().role)

you will need to do something like the following to avoid further queries to the database, knowing user.roles.all() will always give you only one result:

for user in users:
    for user_role in user.roles.all():
        print(user_role.role)

Upvotes: 1

Related Questions