django queryset用法总结

str(User.objects.all().query)

qs1 = User.objects.filter(first_name="John", is_active=True)
qs2 = User.objects.filter(first_name="John").filter(is_active=True)
qs3 = User.objects.filter(first_name="John") & User.objects.filter(is_active=True)
qs4 = User.objects.filter(Q(first_name="John") & Q(is_active=True)

qs1 = User.objects.filter(Q(first_name="John") | Q(first_name="Jane"))
qs2 = User.objects.filter(first_name="John") | User.objects.filter(first_name="Jane")

qs1 = User.objects.filter(~Q(first_name="John"))
qs2 = User.objects.exclude(first_name="John")

qs = User.objects.filter(pk__in=[1, 4, 7])

qs = User.objects.filter(first_name__isnull=True)
qs = User.objects.filter(first_name__isnull=False)

%  ： 任意多个字符
_  ： 一个字符

qs = User.objects.filter(first_name__startswith="Jo")  前匹配 Jo%
qs = User.objects.filter(last_name__endswith="yan")    后匹配 %yan
qs = User.objects.filter(last_name__contains="oh")     包含 或 中间匹配   %oh%
qs = User.objects.filter(last_name__regex=r"^D.e$")    D_e  （3个字符，前后固定，中间任意 例如 D1e Dxe等）

用法总结：startswith, istartswith, endswith, iendswith, contains, icontains, regex, iregex 

s = User.objects.filter(id__gt=3)     大于
qs = User.objects.filter(id__lt=4)    小于

qs = User.objects.filter(id__lte=4)    小于等于

gte >=   
lte <=. 
More on this here

import pytz
from django.utils import timezone 
start = timezone.make_aware(timezone.datetime(year=2019, month=1, day=1), pytz.utc) 
end = timezone.make_aware(timezone.datetime(year=2019, month=12, day=31), pytz.utc)

qs = User.objects.filter(date_joined__range=[start, end])

qs = User.objects.all()[:10]   
等同于：
SELECT ... FROM "auth_user" LIMIT 10

 qs = User.objects.order_by(‘date_joined‘, ‘-first_name‘)
 等同于“
 SELECT ... FROM "auth_user" ORDER BY "auth_user"."date_joined" ASC,  "auth_user"."first_name" DESC

 qs = User.objects.all()， User.object.filter() 得到的不是single object instance, 而是queryset

User.objects.all()[0]    异常： 无数据 IndexError
User.objects.get(pk=1)   可能的异常：无数据 DoesNotExist exception  或者 多个数据 MultipleObjectsReturned exception
user = User.objects.order_by(‘first_name‘).last()

user = User.objects.order_by(‘date_joined‘, ‘-first_name‘).first()

try: 
   user = User.objects.order_by(‘date_joined‘, ‘-first_name‘)[0] 
except IndexError: 
   user = None