57 ORM多表查询

多表查询
from django.db import models
# Create your models here.


class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name=models.CharField( max_length=32)
    age=models.IntegerField()

    # 与AuthorDetail建立一对一的关系
    authorDetail=models.OneToOneField(to="AuthorDetail",on_delete=models.CASCADE)

class AuthorDetail(models.Model):

    nid = models.AutoField(primary_key=True)
    birthday=models.DateField()
    telephone=models.BigIntegerField()
    addr=models.CharField( max_length=64)

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


class Book(models.Model):

    nid = models.AutoField(primary_key=True)
    title = models.CharField( max_length=32)
    publishDate=models.DateField()
    price=models.DecimalField(max_digits=5,decimal_places=2)

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


一.添加表记录

   1.一对多（book表和publish表）
    创建python书,给该书绑定人民出版社，以及两个作者alex,egon（假设alex的id=1,egon的id=2)

    方式一：（publish为对象时，直接传对象的方式，publish = publish某对象）

      publish_1 = models.Publish.objects.filter(name="人民出版社").first()
      egon = models.Author.objects.filter(name="egon").first()
      alex = models.Author.objects.filter(name="alex").first()
      book = models.Book.objects.create(title="python",price=333，pub_date="2017-12-12",publish=publish_1)



    方式二：（已知publish的id时，假设要添加的publish的id为 publish_id=1)
      book = models.Book.objects.create(title="python",price=333，pub_date="2017-12-12",publish_id=1)


   2.多对多关系（book表和author表）
      book.authors.add(egon,alex)  对象方式添加
      或  book.authors.add(1,2)  直接添加id 也可以
      或  book.authors.add(*[1,2]) 通过列表的方式添加，用*打散添加
      book.authors.remove(egon)   移除关系
      book.authors.clear()  清空关系
      book.authors.set([1,2])  set 的效果时先清空，后添加，里面的必须传一个列表


二.基于对象的跨表查询（相当于sql中的子查询）

   key:
      1正向查询按关联字段，反向查询按表名小写
      2.正向，指的是关联字段所在的表，开始查


   一对一查询（Author 和 AuthorDetail)

      1.正向查询（按字段：authorDetail)
         egon = Author.objects.filter(name="egon").first()
         print(egon.authorDetail.telephone)

      2.反向查询（按表名：author）
         查询所住地址在北京的作者的姓名
         authorDetail.objects.filter(addr="北京”）
         for obj in authorDetail_list:
            print(obj.author.name)

   多对多查询（author和book)
       1.正向查询（按字段：authors):
         python 所有作者的名字和手机号
         python = Book.objects.filter(title="python").first()
         authors = python.authors.all()
         for author in authors:
            print(author.name)
            print(author.authorDetail.telephone)

      2.反向查询（按表名小写 :book_set, set表示集合)
       查询egon 出过的所有书籍的名字
       egon= Author.objects.filter(name="egon").first()
       book_lst= egon.book_set.all()
       for book_obj in book_lst:
         print(book_obj.name)


三.基于双下划线的跨表查询（相当于mysql中join语句）
   key:
      1.双下滑线代表跨表
      2.双下滑线查询结构： 表名.filter(条件）.vaules(需要查询的字段)

   1.一对多查询
      查出苹果出版社出版过的所有书籍的名字和价格

      1.正向查询 （按字段：publish）
         queryset = Book.objects.filter(publish__name="苹果出版社").vaules("title","price")

      2.反向查询（按表名：book)
         queryset = Publish.objects.filter(name="苹果出版社").vaules("book__title","book__price")

   2.多对多查询（author和book）

      查询 egon 出过的所有书籍的名字

      正向查询按字段：（authors)
         queryset = Book.objects.filter(authors__name="egon").vaules("title")

      反向查询 按表名小写book
         queryset = Author.objects.filter(name="egon").vaules("book__price")

   3. 一对一 查询
      查询alex 的手机号

      正向查询：
         ret = Author.objects.filter(name="alex").vaules("authorDetail__telphone")

      反向查询：
         ret = AuthorDetail.objects.filter("author__name="alex").vaules("telephone")


四.连续跨表查询

   1.查询人民出版社出版过的所有书籍的名字以及作者的姓名
      正向查询：
         queryset = Book.objects.filter(publish__name="人民出版社").vaules("title","author__name")

      反向查询：
         queryset = Publish.objects.filter(name="人民出版社").vaules.("book__title","book__authors__name")

   2. 手机号以17开头的作者出版过的所有书籍名称以及出版社名称

      方式1：
         queryset = Book.objects.filter(authors__authordetail__telphone__startswith="17").vaules("title","publish__name")

      方式2：
         ret = Author.objects.filter(authorDetail__telphone__startswith="17").vaules("book__title","book__publish__name")

五.聚合查询与分组查询

   1.聚合

      1.需要先引入聚合函数
         from django.db.models import Avg，Max,Min,Count

      2. aggregate(*args, **kwargs)
          aggregate 的返回值是一个字典，键的名称是聚合值的标识符
      计算所有图书的平均价格，最大价格，最小价格：
         默认方式：
            dic = Book.objects.all().aggregate(Avg('price'))
            print(dic)  #{"price_avg":34}
         自定定义键值方式：
            dic = Book.objects.all().aggregate(avg_price = Avg("price")，max_price=Max('price'), min_price = Min('price'))


   2.分组
      1.单表分组查询

         emp:

         id  name age   salary    dep
         1   alex  12   2000     销售部
         2   egon  22   3000     人事部
         3   wen   22   5000     人事部

         查询每个部门名称以及对应的员工数：
            sql语句：
               select dep,count(*) from emp group by(dep)
            ORM:
               emp.objects.vaules("dep").annotate(c=Count("id"))

      2.多表分组查询
         key:
            1.annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。

            2.跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。
            3.annotate的返回值是querySet，如果不想遍历对象，可以用上valuelist
            4.格式 ： 分组的对象.objects.annotate(函数).vaules(需要的值1，函数结果）
               ret = Publish.objects.annotate(min_price=min("price")).vaules("name","min_price")
         emp:

         id  name age   salary   dep_id
         1   alex  12   2000       1
         2   egon  22   3000       2
         3   wen   22   5000       2


         dep

         id   name
         1    销售部
         2    人事部

         查询每一个部门名称以及对应的员工数
            sql:
               select dep.name,count(*) from emp left join dep on emp.dep_id=dep.id group by dep.id

            ORM:
               dep.objects.vaules("id").annotate(c = count("emp")).vaules("name","c")


      查询练习：

         1.统计每一个出版社的最便宜的书
            publish_lst = Publish.objects.annotate(min_price = Min("book__price"))
            for publish_obj in publish_lst:
               print(publish_obj.name,publish_obj.minprice)

         annotate的返回值是querySet，如果不想遍历对象，可以用上values
         ret = Publish.objects.annotate(min_price=min("price")).vaules("name","min_price")

         2.统计每一本书的作者个数
            ret = Book.objects.annotate(authorsNum=Count("authors__name"))

         3.统计每本书以py开头的书籍的作者个数
            ret = Book.objects.filter(title__startswith="py").annotate(c=count("authors__name"))


         4.统计不止一个作者的图书

            queryset = book.objects.annotate(c = count(”authors")).filter(c__gt=1)

         5.根据一本书的作者数量多少对查询集queryset进行排序
            book.objects.annotate(c=count（”authors"）).order_by ("c")

         6.查询各个作者出的书的总价格
            Author.objects.annotate(sum_price = Sum("book__price")).vaules("name","sum_price")


六 F查询与Q查询

   1.F查询
      1. 先导入 from django.db.models import F
      2.Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。
      3.修改操作也可以使用F函数

         1.查询评论数大于收藏数的书籍
            Book.objects.filter(comment_num__gt = F("keepnum"))
         2.查询评论数大于收藏数2倍的书籍
            Book.objects.filter(comment_num__gt=F("keepnum")*2)

         3.将每一本书的价格提高30元
            Book.objects.all().update(price=F("keepnum")+30)



   2.Q查询  ：在filter 多条件查询时，
      1.导入 from django.db.models import Q

      2.filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR语句)你可以使用Q 对象。
         Q(title__startswith='Py')

      3.Q 对象可以使用&（且） 和|(或) 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象。
         bookList=Book.objects.filter(Q(authors__name="yuan")|Q(authors__name="egon"))
         等同sql中：WHERE name ="yuan" OR name ="egon"

      4.查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。
      但是，如果出现Q 对象，它必须位于所有关键字参数的前面。
         bookList=Book.objects.filter(Q(publishDate__year=2016) | Q(publishDate__year=2017),title__icontains="python)

from django.shortcuts import render,HttpResponse,redirect

# Create your views here.
from app01 import models

def add(request):

    ################################## 绑定一对多关系 ##############################
    # 方式1:
    #book=models.Book.objects.create(title="linux",price=122,pub_date="2012-12-12",publish_id=1)

    # 方式2:
    # pub_obj=models.Publish.objects.filter(name="橘子出版社").first()
    # book=models.Book.objects.create(title="php",price=122,pub_date="2012-12-12",publish=pub_obj)
    # print(book.title)
    # print(book.publish_id)
    # print(book.publish) # book书籍出版社对象
    # 查询go出版社的邮箱
    # models.Publish.objects.filter(id= book.publish_id).first().email
    # book.publish.email

    ########################## 绑定多对多的关系;无非是在关系表创建记录 ##########

    # linux这本书绑定两个作者:alex,egon
    # linux=models.Book.objects.filter(title="linux").first()
    # alex=models.Author.objects.filter(name="alex").first()
    # egon=models.Author.objects.filter(name="egon").first()
    # print(linux.price)
    # print(linux.publish)
    #linux.authors.add(alex,egon)
    #linux.authors.add(1)
    #linux.authors.add(*[1,2])
    #linux.authors.remove(alex,egon)
    #linux.authors.clear()
    #linux.authors.set([1,])

    '''
    #KEY:关联属性:authors
    class Book(models.Model):
            title = models.CharField( max_length=32)
            pub_date=models.DateField()
            price=models.DecimalField(max_digits=5,decimal_places=2)
            publish=models.ForeignKey(to="Publish",to_field="id",on_delete=models.CASCADE,null=True)
            authors=models.ManyToManyField("Author",db_table="book2authors") # 创建关系表
            def __str__(self):
                return self.title

 
    '''

    ###############
    # 正向操作按字段,反向操作按表名小写

    linux = models.Book.objects.filter(title="linux").first()
    go = models.Book.objects.filter(title="go").first()
    alex = models.Author.objects.filter(name="alex").first()
    # 给alex作者绑定两本书籍: linux,go
    alex.book_set.add(linux,go)

    return HttpResponse("添加成功!")

def query(request):
    '''
    一 基于对象的跨表查询( 子查询:以上一次的查询结果作为下一次的查询条件)
       (1)一对多
                         正向查询:按字段 book.publish
         Book对象    ---------------------------------- >  Publish 对象
                      <---------------------------------
                        反向查询:按表名小写_set.all()


        (2)多对多
                       正向查询:按字段 book.authors.all()
         Book对象    ---------------------------------- >  Author 对象
                      <---------------------------------
                        反向查询:按表名小写_set.all()


        (2)一对一
                        正向查询:按字段 book.ad
         Author 对象   ---------------------------------- >  AuthorDetail 对象
                      <---------------------------------
                        反向查询:按表名小写

    二 基于双下划綫的跨表查询:

       KEY:通知ORM引擎如何跨表: 正向查询按字段,反向查询按表名小写

    :param request:
    :return:
    '''
    #################################一基于对象的跨表查询#######################
    #(1)一对多

    # 1 查询linux这本书籍的出版社的地址
    # book=models.Book.objects.filter(title="linux").first()
    # print(book.publish.city)
    # 2 查询苹果出版社出版的所有书籍
    # publish=models.Publish.objects.filter(name="苹果出版社").first()
    # queryset=publish.book_set.all()
    # print(queryset) # <QuerySet [<Book: linux>, <Book: python>]>


    # (2)多对多

    # 1 查询linux书籍的所有作者
    # linux=models.Book.objects.filter(title="linux").first()
    # queryset=linux.authors.all() # <QuerySet [<Author: alex>]>
    # print(queryset)
    # 2 查询alex作者出版过得所有书籍
    # alex=models.Author.objects.filter(name="alex").first()
    # queryset=alex.book_set.all()
    # print(queryset) # <QuerySet [<Book: linux>, <Book: go>]>

   # (3)一对一
    # 1  查询alex的手机号
    # alex = models.Author.objects.filter(name="alex").first()
    # print(alex.ad.telephone)

    # 2 查询手机号为911的作者的名字
    # ad=models.AuthorDetail.objects.filter(telephone=911).first()
    # print(ad.author.name)
    ######################基于双下划线的跨表查询(join查询)#################################
    # 1 查询linux这本书籍的出版社的地址
    '''
    SELECT app01_publish.city from app01_book INNER JOIN app01_publish 
                                  ON app01_book.publish_id = app01_publish.id 
                                  WHERE app01_book.title ="linux"
    
    '''
    # 方式1
    # queryset=models .Book.objects.filter(title="linux").values("price","publish__city")
    # print(queryset)
    # # 方式2
    # queryset=models.Publish.objects.filter(book__title="linux").values("city")
    # print(queryset)

    # 2 查询linux书籍的所有作者
    #queryset=models.Book.objects.filter(title="linux").values("authors__name")
    #queryset=models.Book.objects.filter(title__startswith="l").values("authors__name") # ********
    #print(queryset) # <QuerySet [{'authors__name': 'alex'}, {'authors__name': 'alex'}, {'authors__name': 'egon'}]>

    # queryset=models.Author.objects.filter(book__title="linux").values("name")

    # 3  查询alex的手机号

    # queryset=models.Author.objects.filter(name="alex").values("ad__telephone")
    # queryset=models.AuthorDetail.objects.filter(author__name="alex").values("telephone")
    # print(queryset) # <QuerySet [{'telephone': 110}]>

    # 连续跨表
    # 4 查询人民出版社出版过的所有书籍的名字以及作者的姓名
    # queryset=models.Book.objects.filter(publish__name="人民出版社").values("title","authors__name")
    # models.Author.objects.filter(book__publish__name="人民出版社").values("book__title","name")
    # 5 手机号以151开头的作者出版过的所有书籍名称以及出版社名称
    # queryset=models.Book.objects.filter(authors__ad__telephone__contains="1").values("title","publish__name")
    # print(queryset)
    ####################################### 分组查询 #####################################

    ############### 单表分组查询

    #queryset=models.Emp.objects.all() # select * from emp
    # queryset=models.Emp.objects.values("name") # select name from emp;
    # print(queryset)

    '''
    单表分组查询:
    #查询每一个部门名称以及对应的员工数
    
    sql:
        select dep,Count(*) from emp group by dep;        
        select dep,AVG(salary) from emp group by dep;
        
    orm:
        queryset=models.Emp.objects.values("dep").annotate(c=Count("*"))
    '''
    from django.db.models import Avg,Count,Max,Min

    # 查询每一个部门的人数
   #  queryset=models.Emp.objects.values("dep").annotate(c=Count("*"))
   #  print(queryset)  # <QuerySet [{'dep': '销售部', 'c': 1}, {'dep': '人事部', 'c': 2}]>
   #
   # # 查询每一个省份的平均薪水
   #  queryset=models.Emp.objects.values("province").annotate(avg_salary=Avg("salary"))
   #  print(queryset) # <QuerySet [{'province': '山东', 'avg_salary': 4500.0}, {'province': '河北', 'avg_salary': 5000.0}]>

    ############### 多表分组查询
    # 1 查询每一个出版社的名字和出版过的书籍的平均价格
    '''
       
        -- sql语句:
        SELECT app01_publish.name,AVG(app01_book.price) from app01_book LEFT JOIN app01_publish on 
                                 app01_book.publish_id = app01_publish.id
                                 group by app01_publish.id,app01_publish.name
    '''

    # queryset=models.Publish.objects.values("id","name").annotate(avg_price=Avg("book__price"))
    # queryset=models.Publish.objects.values("id","name","email","city").annotate(avg_price=Avg("book__price"))
    # [{"id":1,"name":"苹果出版社","eamil":"123","city":"beijing",'avg_price': 119.0},{"id":1,"name":"橘子出版社","eamil":"123","city":"beijing",'avg_price': 155.333333.0}]

    # queryset=models.Publish.objects.all().annotate(avg_price=Avg("book__price"))
    # print(queryset) #<QuerySet [<Publish: 苹果出版社>, <Publish: 橘子出版社>]>
    # for obj in queryset:
    #     print(obj.name,obj.avg_price)

    # 2 查询每一个作者的名字以及出版书籍的个数
    queryset=models.Author.objects.annotate(c=Count("book")).values("name","c")
    print(queryset) # <QuerySet [{'name': 'alex', 'c': 2}, {'name': 'egon', 'c': 2}]>

    # 3 查询每一个书籍的名称以及作者的个数
    queryset=models.Book.objects.annotate(c=Count("authors")).values("title","c")
    print(queryset)

    # 4 查询作者个数大于1 的每一本书籍的名称和作者个数
    queryset=models.Book.objects.annotate(c=Count("authors")).filter(c__gt=1).values("title","c")
    print(queryset) # <QuerySet [{'title': 'python', 'c': 2}, {'title': 'go', 'c': 2}]>

    # 5 查询书籍名称包含"h"的书籍名称和作者个数
    queryset=models.Book.objects.filter(title__contains="h").annotate(c=Count("authors")).values("title","c")

    ###################################### F查询与Q查询
    #  F查询
    from django.db.models import F,Q,Avg
    # 1 查询评论数大于100的文章
    # queryset=models.Article.objects.filter(comment_num__gt=100)
    # print(queryset)
    # 2 查询评论数大于点赞数的文章
    # queryset=models.Article.objects.filter(comment_num__gt=F("poll_num"))
    # print(queryset) # <QuerySet [<Article: 那一夜>]>
    # 3 查询点赞数大于两倍评论数
    # queryset=models.Article.objects.filter(poll_num__gt=F("comment_num")*2)
    # print(queryset)  # <QuerySet [<Article: 那一天>]>
    # 4 将所有的书籍的价格提高100元
    # models.Book.objects.all().update(price=F("price")+100)
    # Q查询
    # 5 查询价格大于300或者名称以p开头的书籍
    # Q : & | ~
    # queryset=models.Book.objects.filter(Q(title__startswith="p")&Q(price__gt=300))
    # print(queryset) # <QuerySet [<Book: python>, <Book: php>, <Book: pJS>]>
    # # 5 查询价格大于300或者不是2019年一月份的书籍
    # lq=Q(price__gt=300)|~Q(Q(pub_date__year=2019)&Q(pub_date__month=1))
    queryset = models.Book.objects.filter(q)
    # print(queryset)



































    return HttpResponse("查询成功!")





def books(request):

    queryset=models.Book.objects.all()


    return render(request,"books.html",{"queryset":queryset})


def delbook(request,id):
    models.Book.objects.filter(pk=id).delete()


    return  redirect("/books/")


def addbook(request):
    if request.method=="POST":

        data=request.POST.dict()
        data.pop("csrfmiddlewaretoken")
        data.pop("author_list")
        book=models.Book.objects.create(**data)  #  保证提交键值对的键必须和数据库表字段一致
        #  为书籍绑定作者关系
        author_list=request.POST.getlist("author_list")
        print(author_list) # ['1', '2']
        book.authors.add(*author_list)

        return redirect("/books/")
    else:

        publish_list=models.Publish.objects.all()
        author_list=models.Author.objects.all()
        return render(request,'addbook.html',locals())


def editbook(request,edit_book_id):
    edit_book = models.Book.objects.filter(pk=edit_book_id).first()
    if request.method=="POST":
        # 方式1:
        # title=request.POST.get("title")
        # price=request.POST.get("price")
        # pub_date=request.POST.get("pub_date")
        # publish_id=request.POST.get("publish_id")
        # author_list=request.POST.getlist("author_list")
        # models.Book.objects.filter(pk=edit_book_id).update(title=title,price=price,pub_date=pub_date,publish_id=publish_id)  # update只有queryset才能调用
        # edit_book.authors.set(author_list)

        #  方式2:

        data=request.POST.dict()
        data.pop("csrfmiddlewaretoken")
        author_list=data.pop("author_list")
        models.Book.objects.filter(pk=edit_book_id).update(**data)  #  保证提交键值对的键必须和数据库表字段一致
        #  为书籍绑定作者关系
        author_list=request.POST.getlist("author_list")
        edit_book.authors.set(author_list)

        return redirect("/books/")
    else:

        publish_list=models.Publish.objects.all()
        author_list=models.Author.objects.all()
        return render(request,'editbook.html',locals())