Django ORM

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the python category.

Last Updated: 2021-05-16

ORM

  for p in Person.objects.raw('SELECT * FROM myapp_person'):
    print(p)

  # parameterized
  Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [lname])
   User.objects.filter(
    first_name__startswith='R',
    last_name__startswith='D'
   )

   from django.db.models import Q
   # alternatively
   queryset_3 = User.objects.filter(
     Q(first_name__startswith='R') &
     Q(last_name__startswith='D')
   )
   exclude()

   # alternatively
   User.objects.filter(~Q(id__lt=5))
   Hero.objects.all().values_list(
     "name", "gender"
    ).union(
   Villain.objects.all().values_list(
    "name", "gender"
    ))

   # alternatively, use `only`
   User.objects.filter(
    first_name__startswith='R'
   ).only("first_name", "last_name")
   from django.db.models import Subquery
   UserParent.objects.filter(user_id__in=Subquery(users.values('id')))
   hero_qs = Hero.objects.filter(
    # OutRef says this will be used in subquery
    category=OuterRef("pk")
    # Descending order
   ).order_by("-benevolence_factor")
   # annotate adds this field
   Category.objects.all().annotate(
     most_benevolent_hero=Subquery(
     # just get the 1st name field from the previous hero_qs query set
     hero_qs.values('name')[:1]
    )
  )
  User.objects.filter(last_name=F("first_name"))

first letter same for two columns - use Substr and annotate and F

  User.objects.annotate(first=Substr("first_name", 1, 1), last=Substr("last_name", 1, 1)).filter(first=F("last"))
  no_files_objects = MyModel.objects.filter(
    Q(file='')|Q(file=None)
  )
  # use slices - it calls limit/offset
  user = User.objects.order_by('-last_login')[1]
  Person.objects.filter(age__range=(10, 20))
  duplicates = User.objects.values(
    'first_name'
  ).annotate(name_count=Count('first_name')).filter(name_count__gt=1)

  User.objects.filter(first_name__in=[item['first_name'] for item in duplicates])
  distinct = User.objects.values(
    'first_name'
  ).annotate(
    name_count=Count('first_name')
  ).filter(name_count=1)

  # Apparently this differs from User.objects.distinct("first_name").all(), which will pull up the first record
  # when it encounters a distinct first_name.
  # performs an inner join
  Student.objects.select_related('attendance')
  best_authors = Author.objects.filter(books__bestseller=True).distinct()
  User.objects.all().aggregate(Min('id'))
  User.objects.all().aggregate(Sum('id'))
  class LevenshteinLikeZeus(Func):
    function='levenshtein'
    template="%(function)s(%(expressions)s, 'Zeus')"

  Hero.objects.annotate(like_zeus=LevenshteinLikeZeus(F("name"))).filter(like_zeus__lt=2)
  class Hero(models.Model):

  # If your fields depend on a model from a 3rd party app, which you do no
  # control, use can use signals instead.
  def save(self, *args, **kwargs):
    if not self.pk:
      Category.objects.filter(pk=self.category_id).update(hero_count=F('hero_count')+1)
    super().save(*args, **kwargs)
  User.objects.all().order_by(Lower('username'))
  Hero.objects.all().order_by(
    'category__name'
  )
  user.tweet.add(tweet)
  user.tweet.remove(tweet)
   boss = models.ForeignKey('self', on_delete=models.CASCADE)
  Person.objects.update(age=F('age')*1.5)
  python manage.py inspectdb
  class TempUser(models.Model):
    first_name = models.CharField(max_length=100)

    class Meta:
      managed = False
      db_table = "temp_user"

ORM Resources