Get first record
context['branch'] = CompanyBranch.objects.filter(company=1).first()
Get second
user = User.objects.all().order_by('-id')[1]
Get Last
context['branch'] = CompanyBranch.objects.filter(company=1).last()
Latest
Entry.objects.latest('pub_date')
Earliest
Entry.objects.earliest('pub_date')
Next
previous_job = Job.objects.filter(pk__lt=this_job.id).order_by('id').last()
Previous
previous_job = Job.objects.filter(pk__gt=this_job.id).order_by('id').first()
Apply basic filtering
queryset = Employee.objects.filter(post ='Accountant')
Apply filtering with multiple fields
queryset = Employee.objects.filter(department='HR', email='[email protected]')
# or
queryset_2 = User.objects.filter(first_name__startswith='R') & User.objects.filter(last_name__startswith='D')
Apply filtering if value in a list of values
ids = [1, 3, 6, 7, 9]
Blog.objects.filter(pk__in=[1, 4, 7])
Filter using another query
Article.objects.filter(reporter__in=Reporter.objects.filter(first_name='John')).distinct()
queryset = my_queryset.reverse()
Distinct
queryset = Author.objects.distinct()
Exclude
queryset = Entry.objects.exclude(headline='Hello')
Apply filtering by chaining
queryset = Employee.objects.filter(department='HR').filter(name='Mehrab Hossain')
only() Select some fields only in a queryset?
queryset = User.objects.filter(first_name__startswith='R').only("first_name", "last_name")
defer() Select all fields but some
queryset = User.objects.filter(first_name__startswith='R').defer("first_name")
Filtering Null Fields
Book.objects.filter(author__isnull=True)
# not null
Book.objects.filter(author__isnull=False)
Filtering Empty Fields
Book.objects.filter(title__exact='')
Join Tables
you can foreign key and one-to-one values eg: article.author.name but you will hit the DB every time
artciles = Article.objects.all()
select_related() - avoid hitting the DB when accessing related models - is limited to single-valued relationships - foreign key and one-to-one.
Artciles = Article.objects.select_related('reporter')
prefetch_related() – avoid hitting the DB when accessing related models – does a separate lookup for each
relationship – prefetch many-to-many and many-to-one objects
pizzas = Pizza.objects.all().prefetch_related('toppings')
Q objects for complex queries
from django.db.models import Q
Or query
queryset = User.objects.filter(Q(first_name__startswith='R') | Q(last_name__startswith='D'))
And query
queryset = User.objects.filter(Q(first_name__startswith='R') & Q(last_name__startswith='D'))
Not query
queryset = User.objects.filter(~Q(id__lt=5))
Multiple operations
Poll.objects.get(
Q(question__startswith='Who'),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
Equivelent to
SELECT * from polls WHERE question LIKE 'Who%'
AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
filter FileField without any file
no_files_objects = MyModel.objects.filter(Q(file='')|Q(file=None))
Group records aggregate()
from django.db.models import Avg, Max, Min, Sum, Count
User.objects.all().aggregate(Avg('id'))
User.objects.all().aggregate(Max('id'))
User.objects.all().aggregate(Min('id'))
User.objects.all().aggregate(Sum('id'))
Limiting QuerySets
Last 5
Entry.objects.all()[:5]
the 6th through tenth objects (OFFSET 5 LIMIT 5):
Entry.objects.all()[5:10]
Field lookups
# = default is exact
Entry.objects.filter(blog_id=4)
# exact case-sensitive match
Entry.objects.get(headline__exact="Cat bites dog")
# iexact case-insensitive match
Blog.objects.get(name__iexact="beatles blog")
# contains case-sensitive match
Entry.objects.get(headline__contains='Lennon')
# contains case-insensitive match
Entry.objects.get(headline__icontains='Lennon')
# startswith, endswith case-sensitive match
User.objects.filter(first_name__startswith='R')
# istartswith, iendswith case-insensitive match
User.objects.filter(first_name__iendswith='R')
# Same value
from django.db.models import F
User.objects.filter(last_name=F("first_name"))
# Sub string in two fields
User.objects.annotate(first=Substr("first_name", 1, 1), last=Substr("last_name", 1, 1)).filter(first=F("last"))
Field lookups >= <= Less/More than or equal
# __lte <=
User.objects.filter(userprofile__level__lte=0)
# __lt < User.objects.filter(userprofile__level__lt=0) # __gte >=
User.objects.filter(userprofile__level__gte=0)
# __gt >
User.objects.filter(userprofile__level__gt=0)
Calling filter with a variable for field name
field_name = 'video'
Playlist.objects.filter(**{field_name: 'the needed value'})
# use lookup
field_name = 'video'
field_name_icontains = field_name + '__icontains'
Playlist.objects.filter(**{field_name_icontains: v})
Union() intersection() difference()
the three operations could be applied on queries they have the same fields (for example of the same model)
Note: you cannot apply or exclude() after an union() intersection() difference()
1. Union(*other_qs, all=False) # Uses SQL’s UNION operator to combine the results of two or more QuerySets.
qs1= qs1.union(qs2) # join 2
qs4 = qs1.union(qs2, qs3) # join 3
qs1 = Author.objects.values_list('name')
qs2 = Entry.objects.values_list('headline')
qs1.union(qs2).order_by('name')
2. Intersection(*other_qs) # Uses SQL’s INTERSECT operator to return the shared elements of two or more QuerySets. For example:
>>> qs1.intersection(qs2, qs3)
3. difference(*other_qs) # Uses SQL’s EXCEPT operator to keep only elements present in the QuerySet but not in some other QuerySets. For example:
>>> qs1.difference(qs2, qs3)
