django ORM 练习

django ORM 练习

models.py

class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()

    author_detail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE, db_constraint=False)


class AuthorDetail(models.Model):
    birthday = models.DateField()
    telephone = models.BigIntegerField()
    address = models.CharField(max_length=64)


class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()


class Book(models.Model):
    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)

    # 与Publish建立一对多的关系,外键字段建立在多的一方
    publish = models.ForeignKey(to="Publish", on_delete=models.CASCADE, db_constraint=False)
    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
    authors = models.ManyToManyField(to='Author', )

views.py

def a(request):
    # 苹果出版社出版的书籍名称
    # res = Book.objects.filter(publish__name="苹果出版社").values("title")
    # print(res)

    # res = Publish.objects.filter(name="苹果出版社").values("book__title")
    # print(res)

    # 查询leo的手机号
    # res = Author.objects.filter(name="leo").values("author_detail__telephone")
    # print(res)
    #
    # res = AuthorDetail.objects.filter(author__name="leo").values("telephone")
    # print(res)

    # 查询手机号以183开头的作者出版过的书籍名称以及书籍对应的出版社名称
    # res = Book.objects.filter(authors__author_detail__telephone__startswith="183").values('title', 'publish__name')
    # print(res)

    # ------------------ 聚合 ------------------
    # 查询所有书籍的价格
    # res = Book.objects.all().aggregate(price_sum=Sum('price'))
    # print(res)

    # 查询作者的平均年龄
    # res = Author.objects.all().aggregate(avg_age=Avg('age'))
    # print(res)

    # ------------------ 分组 ------------------
    # 查询每一个作者名字以及出版过的书籍的最高价格
    # res = Author.objects.values("name").annotate(max_price=Max("book__price")).values("max_price", "name")
    # print(res)

    # 查询每一个出版社出版过的书籍的平均价格
    # res = Publish.objects.values("name").annotate(avg_price=Avg("book__price")).values("avg_price", "name")
    # print(res)

    # 查询每一本书籍的作者个数
    # res = Book.objects.values("title").annotate(count=Count("authors__name")).values("count", "title")
    # print(res)

    # 统计不止1个作者的图书名称
    res = Book.objects.values("title").annotate(count=Count("authors__name")).filter(count__gt=1).values("title", "count")
    print(res)

    return HttpResponse('ok')