基于对象的跨表查询

---

跨表查询比较难！要细心的学

1.基于对象的跨表查询

ORM_MULTI/urls.py

from django.contrib import admin
from django.urls import path
from app01 import views

urlpatterns = [
    path('admin/', admin.site.urls),
    path("query/",views.query)
]

app01/models.py

from django.db import models

# 作者详情表
class AuthorDetail(models.Model):
    nid = models.AutoField(primary_key=True)
    birthday = models.DateField()
    telephone = models.BigIntegerField()
    addr = models.CharField(max_length=64)


# 作者表
class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    authordetail = models.OneToOneField(to='AuthorDetail',to_field='nid',on_delete=models.CASCADE)

    def __str__(self):
        return self.name

# 出版社表
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()

# book表
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)

    create table book_quthor{
	id int primary key quto_increment,
        book_id int,
        author_id int,
        foreign key (book_id) references book(id),
        foreign key (author_id) reference author(id)
    };
    """

    def __str__(self):
        return self.title

app01/views.py

from django.shortcuts import render,HttpResponse
from app01.models import *

def query(request):
    """跨表查询
        1 基于对象查询
        2 基于双下划线查询
        3 聚合和分组查询
        4 F 与 Q 查询
    """
    # ===============  表中数据的准备 =========================
    """
    # AuthorDetail表
    ad_1 = AuthorDetail.objects.create(birthday='2018-01-11',telephone=1234567890,addr="北京沙河")
    ad_2 = AuthorDetail.objects.create(birthday='2017-02-22',telephone=1234567890,addr="上海明珠")
    ad_3 = AuthorDetail.objects.create(birthday='2016-03-30',telephone=1234567890,addr="北京朝阳")
    ad_4 = AuthorDetail.objects.create(birthday='2015-04-02',telephone=1234567890,addr="广州")

    # Author 表
    author_1 = Author.objects.create(name='alex',age=23,authordetail=ad_1)
    author_2 = Author.objects.create(name='egon',age=24,authordetail=ad_2)
    author_3 = Author.objects.create(name='peiqi',age=25,authordetail=ad_3)
    author_4 = Author.objects.create(name='yuan',age=26,authordetail=ad_4)

    # Publish 表
    publish_1 = Publish.objects.create(name="成都中医药大学出版社",city='四川',email="123@成都中医药大学.com")
    publish_2 = Publish.objects.create(name="电子科技大学出版社",city='四川',email="123@电子科技大学.com")

    # Book 表
    book_1 = Book.objects.create(title="Python",publishDate='2018-07-20',price=12.34,publish=publish_1)
    book_2 = Book.objects.create(title="JavaScript",publishDate='2018-07-20',price=12.34,publish=publish_2)
    book_3 = Book.objects.create(title="Vue",publishDate='2018-07-20',price=12.34,publish=publish_2)
    book_4 = Book.objects.create(title="Java",publishDate='2018-07-20',price=12.34,publish=publish_1)

    # 绑定多表关系

    bo_1 = Book.objects.filter(nid="1").first()
    bo_2 = Book.objects.filter(nid="3").first()
    bo_3 = Book.objects.filter(nid="4").first()

    bo_1.authors.add(13,14)
    bo_2.authors.add(15)
    bo_3.authors.add(15)

    """

    # ===============  基于对象的跨表查询 (子查询，)===============
    #       先查询到关联或者被关联的对象，然后根据关联对象进行查询！

    # =====
    # 一对多的正向查询 按字段 查询Vue这本书的出版社的名字 多对一
    book_pub_name = Book.objects.filter(title="Vue").first().publish.name
    """原生的SQL语句
    select
        name
    from
        app01_publish
    where
        nid = (select publish_id from app01_book where title="Vue");
    """

    # 一对多的反向查询 按照表名 表名小写_set 查询电子科技大学出版社出版过的书籍
    publish = Publish.objects.filter(name="电子科技大学出版社").first()
    ret = publish.book_set.filter(title="JavaScript").first()
    print(ret.title)
    """原生SQL
    select title from app01_book where publish_id =
    (select nid from app01_publish where name="电子科技大学出版社");
    """


    # =====
    # 多对多的正向查询 查询Python书籍的所有作者的名字
    book_obj = Book.objects.filter(title="Python").first()
    author_list = book_obj.authors.all()
    print(author_list)

    # 多对多的反向查询 查询peiqi写过的所有的书籍
    author = Author.objects.get(name="peiqi")
    book_list = author.book_set.all()
    print(book_list)

    # =====
    # 一对一的正向查询 查询egon的地址
    egon = Author.objects.filter(name="egon").first()
    addr = egon.authordetail.addr
    print(addr)

    # 一对一的反向查询 查询地址为北京沙河的 Author表名字
    addr = AuthorDetail.objects.filter(addr="北京沙河").first()
    name = addr.author.name
    print(name)

    return HttpResponse("OK")


"""


A-B 假如关联属性在A表中
    正向查询：通过A查询B，正向查询按 字段
    反向查询：通过B查询A  反向查询按 表名_set 一般使用 表名_set.all() 方法
            因为反向查找大内容可能很多个，这里用_set告诉你这个查询的是个集合形式的！

一对多查询
                          book_obj.publish
    Book(关联属性：publish) --------------> Publish
                        <---------------
                          publish_obj.book_set.过滤方法() # 一般选择QuerySet返回值的方法？？？


多对多查询
    正向查询：通过A查询B，正向查询按 字段
    反向查询：通过B查询A  反向查询按 表名_set  一般使用 表名_set.all() 方法
                          book_obj.authors.all()
    Book(关联属性：authors) --------------> Authors
                        <---------------
                         author_obj.book_set.all()


一对一查询
    正向查询：通过A查询B，正向查询按 字段
    反向查询：通过B查询A  反向查询按 表名小写(没有_set了)
            应为是一对一的关系，所以反向查找的只有一个
                                  author_obj.authordetail
    Author(关联属性：authordetail) --------------> AuthorDetail
                               <---------------
                               authordetail.author
"""

注意：

你可以通过在 ForeignKey() 和ManyToManyField的定义中设置 related_name 的值来覆写 FOO_set 的名称。例如，如果 Article model 中做一下更改：

publish = ForeignKey(Book, related_name='bookList')

那么接下来就会如我们看到这般：

# 查询 人民出版社出版过的所有书籍

publish=Publish.objects.get(name="人民出版社")
book_list=publish.bookList.all()  # 与人民出版社关联的所有书籍对象集合