Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

possible to filter the queryset after querying? django

Sorry if the question sounds weird. I am just wondering if there is possible to make new queryset when I already have a queryset.

For example here...

 everyone = User.objects.filter(is_active=True)  # this would of course return all users that's active  not_deleted = User.objects.filter(is_active=True, is_deleted=False)  # return user that's active and not deleted  is_deleted = User.objects.filter(is_active=True, is_deleted=True)  # return user that's active and is already deleted 

What my question is...for not_deleted and is_deleted they both have active is true with is the same as everyone is there a possible way to use everyone and then somehow filter out is_deleted=True or is_deleted=False? So then I believe the querying would be faster and better if this is possible right?

All three variables everyone, not_deleted and is_deleted will then be used for something else.

Hopefully I made my question quiet clear.

Thanks in advance.

like image 355
Dora Avatar asked Jul 21 '17 02:07

Dora


People also ask

Can I filter a QuerySet Django?

The filter() method is used to filter you search, and allows you to return only the rows that matches the search term.

How can I filter a Django query with a list of values?

The sql query will be like SELECT * FROM mytable WHERE ids=[1, 3, 6, 7, 9] which is not true. You have to use in operator for this so you query will be like SELECT * FROM mytable WHERE ids in (1, 3, 6, 7, 9) for that Django provide __in operator.


Video Answer


2 Answers

Yes, you can reuse existing querysets.

everyone = User.objects.filter(is_active=True) active_not_deleted = everyone.filter(is_deleted=False) active_is_deleted = everyone.filter(is_deleted=True) 

This is not really making anything faster though, in fact, this code block won't even execute a query against the database because Django QuerySets are lazily evaluated. What I means is that it won't send the query to the database until you actually need the values. Here's an example that will talk to the database.

everyone = User.objects.filter(is_active=True)  # Building SQL... active_not_deleted = everyone.filter(is_deleted=False)  # Building SQL... active_is_deleted = everyone.filter(is_deleted=True)  # Building SQL...  # Example of the whole queryset being evaluated for user in everyone:     # This will execute the query against the database to return the list of users     # i.e. "select * from user where is_active is True;"     print(user)  # Example of using iterator to evaluate one object at a time from the queryset. for user in active_not_deleted.iterator():     # This will execute the query for each result, so it doesn't     # load everything at once and it doesn't cache the results.     # "select * from user where is_active is True and is_deleted is False limit 1 offset 0;"     # The offset is incremented on each loop and another query is sent to retrieve the next user in the list.     print(user) 

Recommend reading:

  • https://docs.djangoproject.com/en/1.11/topics/db/queries/#querysets-are-lazy
  • https://docs.djangoproject.com/en/1.11/ref/models/querysets/#iterator
  • https://docs.djangoproject.com/en/1.11/topics/db/queries/#caching-and-querysets

As an addition to this answer, you could make a single query and then filter in Python if you really wanted. Mind you, you could not do subsequent filtering on the lists because they are not QuerySets.

everyone = User.objects.filter(is_active=True) active_not_deleted = list(filter(lambda user: user.is_deleted is False), list(everyone)) active_is_deleted = list(filter(lambda user: user.is_deleted is True), list(everyone)) 

In this last example, everyone is a queryset, and active_not_deleted and active_is_deleted are Python lists of User objects. The everyone queryset will only be evaluated once in the first list(everyone) call, and then the results are cached.

like image 170
A. J. Parr Avatar answered Sep 23 '22 16:09

A. J. Parr


1. chain filter method

not_deleted = User.objects.filter(active=True).filter(is_deleted=False) 

@Cory Madden already answered. User.objects.filter(active=True) returns Queryset. So you can add filter method. active_users.filter(is_deleted=False)

2. using Q method

from django.db.models import Q  not_deleted = User.objects.filter(Q(active=True) & Q(is_deleted=False) 

It is easier to manage your complicated queryset. What if you want to filter userID is not 3? you can use Q simplye like User.objects.filter(Q(active=True) & ~Q(id = 3))


Answer for your comment,

Using Q or not, it has same raw query.

SELECT ... FROM ...  WHERE ("auth_user"."active" = True AND "auth_user"."is_deleted" = False) 

Database performance is relating to how often you hit database to extract data or if you use a heavy method like 'Join' when you extract something by FK relationship. So Using Q or not doesn't give you performance difference, because it has same query sentence.

Additionally,

user = User.objects.filter(active=True) not_deleted = User.objects.filter(active=True).filter(is_deleted=False)  user = User.objects.filter(active=True) not_deleted = user.filter(is_deleted=False) 

would not give you performance difference.

Queryset is lazy. user and not_deleted variables have just queryset string. It doesn't hit the database right away when you define variable like above. Anyway, you will hit three times for each variable.

like image 45
Jayground Avatar answered Sep 25 '22 16:09

Jayground