Querysets can do that?

Querysets and django's ORM are two of the most overlooked parts of this extremely popular full stack python web framework, most people only use the basic commands to get and filter records from the database and then perform all sort of data processing and aggregations in python.

Just by reading the ORM documentation you can realize how powerful and complete it is. In this talk I give a quick overview of some of the basic and not so known features available on django ORM, giving examples and putting everything together at the end of the talk.

Transcript

1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Querysets can do that? of course!, and much more! Israel Fermin Montilla Tech Lead @ Careem November 18, 2018
2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . from iferminm import more_data ▶ Tech Lead @ Careem ▶ Venezuelan living in Dubai, UAE ▶ T: @iferminm ▶ blog: http://iffm.me
3. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . What will we see in this talk? ▶ Understanding Querysets ▶ The known ▶ The not so known ▶ WTF?
4. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . class Captain(models.Model): name last_name city join_date
5. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . class Trip(models.Model): captain passenger city vehicle booking_time pick_up drop_off start_time end_time status route km
6. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . class Vehicle(models.Model): captain make model year specs
7. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . What does a query return?
8. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . What does a query return? ▶ .get(): Single instance (django.db.models.Model) ▶ .all(), .filter(), .exclude(): Iterable object, django.db.models.Queryset
9. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets
10. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets
11. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets Querysets are lazy!
12. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets Querysets are lazy! caps = Captain.objects.filter(name='Mohamed') print([c.get_full_name(), c.city.name, c.join_date for c in caps])
13. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets
14. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets Querysets are like LEGO
15. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets Querysets are like LEGO caps = Captain.objects.all() if location: caps = caps.filter(city=location) if need_vehicles: caps = caps.prefetch_related('vehicle_set')
16. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets When does django evaluates the queryset?
17. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets When does django evaluates the queryset? ▶ Iteration, i.e. for element in queryset:
18. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets When does django evaluates the queryset? ▶ Iteration, i.e. for element in queryset: ▶ Slicing with step, i.e. ModelClass.objects.all()[4:40:3]
19. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets When does django evaluates the queryset? ▶ Iteration, i.e. for element in queryset: ▶ Slicing with step, i.e. ModelClass.objects.all()[4:40:3] ▶ Caching and pickling
20. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding Querysets When does django evaluates the queryset? ▶ Iteration, i.e. for element in queryset: ▶ Slicing with step, i.e. ModelClass.objects.all()[4:40:3] ▶ Caching and pickling ▶ repr() ▶ len() ▶ list() ▶ bool()
21. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Known Queryset Some other thing ▶ all ▶ filter ▶ exclude ▶ order_by ▶ reverse ▶ distinct ▶ annotate ▶ get ▶ first ▶ last ▶ latest ▶ first ▶ earliest ▶ exists ▶ count ▶ aggregate
22. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Known Queryset Some other thing ▶ all ▶ filter ▶ exclude ▶ order_by ▶ reverse ▶ distinct ▶ annotate ▶ get ▶ first ▶ last ▶ latest ▶ first ▶ earliest ▶ exists ▶ count ▶ aggregate Use First Use Last
23. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Known Ex: Getting a Captain’s first trip cancellation
24. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Known Ex: Getting a Captain’s first trip cancellation first_cancellation = Trip.objects.filter( captain=captain, status='canceled', ).order_by('-booking_time').first()
25. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Known Ex: Getting a Captain’s first trip cancellation first_cancellation = Trip.objects.filter( captain=captain, status='canceled', ).order_by('-booking_time').first() first_cancellation = Trip.objects.filter( captain=captain, status='canceled', ).latest('booking_time')
26. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . aggregate vs annotate
27. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . aggregate vs annotate aggregate: compute the value for the whole set
28. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . aggregate vs annotate aggregate: compute the value for the whole set Trip.objects.filter( city__name='dubai', ).aggregate(Avg('km')) {'km__avg': Decimal(32.9800)}
29. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . aggregate vs annotate aggregate: compute the value for the whole set Trip.objects.filter( city__name='dubai', ).aggregate(Avg('km')) {'km__avg': Decimal(32.9800)} annotate: performs the computation for each element in the result set
30. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . aggregate vs annotate aggregate: compute the value for the whole set Trip.objects.filter( city__name='dubai', ).aggregate(Avg('km')) {'km__avg': Decimal(32.9800)} annotate: performs the computation for each element in the result set Captain.objects.filter( city__name='dubai', ).prefetch_related('trip_set').annotate( Avg('trip__km'), Count('trip'), )
31. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . aggregate vs annotate aggregate: compute the value for the whole set Trip.objects.filter( city__name='dubai', ).aggregate(Avg('km')) {'km__avg': Decimal(32.9800)} annotate: performs the computation for each element in the result set Captain.objects.filter( city__name='dubai', ).prefetch_related('trip_set').annotate( Avg('trip__km'), Count('trip'), ) It adds custom fields to each instance in the resulting queryset: trip__km__avg and trip__count
32. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known ▶ select_related ▶ prefetch_related ▶ defer ▶ only ▶ using ▶ values ▶ values_list
33. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known trip = Trip.objects.filter(captain=cap).first() trip.captain.name # DB hit trip.passenger.phone # DB hit trip.vehicle.numberplate # DB hit
34. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known trip = Trip.objects.filter(captain=cap).first() trip.captain.name # DB hit trip.passenger.phone # DB hit trip.vehicle.numberplate # DB hit trip = Trip.objects.filter( captain=cap, ).select_related( 'captain', 'passenger', 'vehicle', ).first()
35. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known cap = Captain.objects.first() cap.trip_set.all() cap.vehicle_set.all()
36. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known cap = Captain.objects.first() cap.trip_set.all() cap.vehicle_set.all() cap = Captain.objects.prefetch_related( 'vehicle_set', 'trip_set', ).first() cap.vehicle_set.all() cap.trip_set.filter(vehicle__make='tesla') # DB hit
37. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known Sometimes I want a subset of the reverse-related queryset lookup = Trip.objects.filter(vehicle__make='tesla') cap = Captain.objects.prefetch_related( 'vehicle_set', Prefetch('trip_set', queryset=lookup), ).first()
38. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known Sometimes I want a subset of the reverse-related queryset lookup = Trip.objects.filter(vehicle__make='tesla') cap = Captain.objects.prefetch_related( 'vehicle_set', Prefetch('trip_set', queryset=lookup), ).first() cap = Captain.objects.prefetch_related( 'vehicle_set', 'trip_set', Prefetch('trip_set', queryset=lookup, to_attr='tesla_trips'), ).first()
39. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known only: returns only the given fields Trip.objects.filter( city__name='casablanca', ).only( 'captain', 'passenger', 'booking_date', ) defer: returns everything except the given fields Trip.objects.filter( city__name='casablanca', ).defer( 'route', )
40. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Not so known values: returns the given fields as a list of dictionaries Trip.objects.filter( city__name='casablanca', ).values( 'captain__name', 'passenger__name', 'booking_date', ) values_list: returns the given fields as a list of tuples: Trip.objects.filter( city__name='casablanca', ).values_list( 'captain__name', 'passenger__name', 'booking_date', )
41. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced ▶ Q() ▶ F() ▶ Conditional expressions
42. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced By default, django ANDs all the filters Q() expressions are used to wrap query constraints. Ex. All captains except the ones in Doha
43. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced By default, django ANDs all the filters Q() expressions are used to wrap query constraints. Ex. All captains except the ones in Doha Captain.objects.exclude(city__name='doha')
44. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced By default, django ANDs all the filters Q() expressions are used to wrap query constraints. Ex. All captains except the ones in Doha Captain.objects.exclude(city__name='doha') Captain.objects.filter(~Q(city__name='doha'))
45. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced By default, django ANDs all the filters Q() expressions are used to wrap query constraints. Ex. All captains except the ones in Doha Captain.objects.exclude(city__name='doha') Captain.objects.filter(~Q(city__name='doha')) Captain.objects.filter( Q(city__name='lahore') | Q(city__name='islamabad') )
46. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced F() expressions are used to reference values within the queryset
47. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced F() expressions are used to reference values within the queryset Ex. All the trips where the captain is still in the same city
48. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced F() expressions are used to reference values within the queryset Ex. All the trips where the captain is still in the same city Trip.objects.select_related( 'captain', ).filter( city=F('captain__city'), )
49. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced Conditional expressions When() objects are used to encapsulate conditions and their result to be used in conditional expressions Case() expressions are like if-elif-else statements in Python. The conditions are provided as When() objects
50. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced Conditional updates class Captain(models.Model): SILVER = 'S' GOLD = 'G' CAREEM = 'C' name last_name city join_date tier
51. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced Conditional updates six_months_ago = today() - timedelta(months=6) one_year_ago = today() - timedelta(years=1) two_years_ago = today() - timedelta(years=2) Captain.objects.update( tier=Case( When( join_date__lte=six_months_ago, then=Value(Captain.SILVER) ), When( join_date__lte=one_years_ago, then=Value(Captain.GOLD) ), When( join_date__lte=two_years_ago, then=Value(Captain.CAREEM) ), ) )
52. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced Conditional aggregation Captain.objects.aggregate( silver=Count('pk', filter=Q(tier=Captain.SILVER)), gold=Count('pk', filter=Q(tier=Captain.GOLD)), careem=Count('pk', filter=Q(tier=Captain.CAREEM)), )
53. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced Conditional annotation
54. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Advanced Conditional annotation I want to give Captains a bonus based on the number of trips or distance covered in their current city in the last six months
55. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Captain.objects.prefetch_related( 'trip_set', ).filter( city=F('trip__city'), trip__booking_date__gte=six_months_ago, ).annotate( Count('trip'), Sum('trip__km'), ).annotate( bonus=Case( When( Q(trip__count__lte=600) | Q(trip__km__sum__gte=10000), then=Value('5%'') ), When( Q(trip__count__gt=600) | Q(trip__km__sum=190000), then=Value('15%') ) ) ).values('captain__id', 'captain__name', 'trip__count', 'trip__km__sum', 'bonus')
56. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Questions? **** We’re hiring **** **** israel.montilla@careem.com ****