django多表关联查询

# models.py

from django.db import models

class Musician(models.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    instrument = models.CharField(max_length=100)
    dept = models.ForeignKey(Dept, on_delete=models.CASCADE)
    

class Album(models.Model):
    artist = models.ForeignKey(Musician, on_delete=models.CASCADE)
    name = models.CharField(max_length=100)
    release_date = models.DateField()
    num_stars = models.IntegerField()


class Dept(models.Model):
    name = models.CharField(max_length=50)
      

两表关联-查询Musician的Album

# 查询名字为zhangsan的musician
musician = Musician.objects.filter(first_name="san", last_name="zhang").first()

# 查询zhangsan的Album
if musician:
    albums = Album.objects.filter(artist=musician)

两表关联-Album按artist分组统计

from django.db.models import Sum
albums = Album.objects.values("name", "artist__firstname").annotate(album_num=Sum("id"))

三表关联-Album按artist的所在dept分组统计

from django.db.models import Sum

albums = Album.objects.values("name", "artist__dept__name").annotate(album_num=Sum("id"))

统计最多star的Alum

from django.db.models import Max

album = Album.objects.aggregate(Max('num_stars'))
print(album.num_stars__max)

按发行日期release_date统计album数量

from django.db.models import Sum

albums = Album.objects.values('name', 'release_date').annotate(album_num=Sum('id'))

统计平均一次发行album数量

from django.db.models import Avg

albums = Album.objects.values('name', 'release_date').annotate(album_num=Avg('id'))