Django ORM 多表查詢範例程式碼

# 構建表結構
from django.db import models
 
# 表app01_publish
class Publish(models.Model):
    name = models.CharField(max_length=20)
    addr = models.CharField(max_length=20)
 
# 表app01_author_detail
class Author_Detail(models.Model):
    tel = models.CharField(max_length=20)
 
# 表app01_author
class Author(models.Model):
    name = models.CharField(max_length=20)
    age = models.IntegerField()
 
    # 表app01_author一對一表app01_authordetail
    detail = models.OneToOneField(to='Author_Detail',to_field='id',unique=True,on_delete=models.CASCADE)
 
# 表app01_book
class Book(models.Model):
    title = models.CharField(max_length=20)
    price = models.DecimalField(max_digits=8, decimal_places=2)
    pub_date = models.DateField(auto_now_add=True)
 
    # 表app01_book多對一表app01_publish，引數to指定模型名，引數to_field指定要關聯的那個欄位
    publish = models.ForeignKey(to='Publish',to_field='id',on_delete=models.CASCADE)
 
    # 我們自己寫sql時，針對書籍表與作者表的多對關係，需要自己建立新表，而基於django的orm，下面這一行程式碼可以幫我們自動建立那張關係表
    authors=models.ManyToManyField(to='Author')
    # 變數名為authors，則新表名為app01_book_authors，若變數名為xxx，則新表名為app01_book_xxx
 

# 新增資料
 
import os
 
if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "book_sys.settings")
    import django
    django.setup()
 
    from app01.models import *
    # 1、先新增沒有外來鍵欄位的 author_detail表 pubulish表
    Author_Detail.objects.create(tel='123456789')
    Author_Detail.objects.create(tel='987654321')
    Author_Detail.objects.create(tel='000000000')
 
    Publish.objects.create(name='北方出版社',addr='北京')
    Publish.objects.create(name='南方出版社',addr='南京')
    Publish.objects.create(name='東方出版社',addr='上海')
    Publish.objects.create(name='西方出版社',addr='西安')
 
    # 2、新增 author表 book表
    Author.objects.create(name='frank',age=31 ,detail_id=1)
    Author.objects.create(name='lili',age=29 ,detail_id=2)
    Author.objects.create(name='tank',age=42 ,detail_id=3)
 
    Book.objects.create(title='三國演義',price=200 ,publish_id=1)
    Book.objects.create(title='三國志',price=198.5 ,publish_id=2)
    Book.objects.create(title='紅樓夢',price=255.43 ,publish_id=2)
    Book.objects.create(title='西遊記',price=300.5 ,publish_id=3)
    Book.objects.create(title='西廂記',price=213.4 ,publish_id=4)
    Book.objects.create(title='水滸傳',price=199 ,publish_id=1)
 
    # 3、最後操作 author_book表，由於使用的是 ManyToMany 欄位自動生成的，所以要基於外來鍵所在的表進行操作
    book_obj1=Book.objects.filter(pk=1).first()
    book_obj1.authors.add(1,2)
 
    book_obj2 = Book.objects.filter(pk=2).first()
    book_obj2.authors.add(1)
 
    book_obj3 = Book.objects.filter(pk=3).first()
    author_obj1 = Author.objects.filter(pk=1).first()
    author_obj2 = Author.objects.filter(pk=2).first()
    book_obj3.authors.add(author_obj1,author_obj2)
 
    book_obj4 = Book.objects.filter(pk=4).first()
    book_obj4.authors.add(3,2)
 
    book_obj5 = Book.objects.filter(pk=5).first()
    book_obj5.authors.add(3)
 
    book_obj6 = Book.objects.filter(pk=6).first()
    book_obj6.authors.add(1,3)

book_obj = Book.objects.filter(pk=5).first()
res = book_obj.publish
print(res)
# Publish object
print(res.name)
# 西方出版社

book_obj = Book.objects.filter(pk=3).first()
res = book_obj.authors
print(res)
# app01.Author.None

book_obj = Book.objects.filter(pk=3).first()
res = book_obj.authors
 
res1 = book_obj.authors.all()
print(res1)
# <QuerySet [<Author: Author object>, <Author: Author object>]>

author_obj = Author.objects.filter(name='lili').first()
res = author_obj.detail
print(res)
print(res.tel)

publish_obj=Publish.objects.filter(name='東方出版社').first()
print(publish_obj)
# Publish object
 
res = publish_obj.book_set
print(res)
# app01.Book.None
 
res1 = res.all()
print(res1)
# <QuerySet [<Book: Book object>]>
for obj in res1:
  print(obj.title)

author_obj = Author.objects.filter(name='lili').first()
res = author_obj.book_set
print(res)
# app01.Book.None
res1 = res.all()
print(res1)
# <QuerySet [<Book: Book object>, <Book: Book object>, <Book: Book object>]>
for obj in res1:
  print(obj.title, end='')
# 三國演義,紅樓夢,西遊記,

detail_obj = Author_Detail.objects.filter(tel='000000000').first()
print(detail_obj)
# Author_Detail object
res = detail_obj.author
print(res)
# Author object
print(res.name)
# tank

res = Author.objects.filter(name='frank').values('detail__tel')
print(res)
# <QuerySet [{'detail__tel': '123456789'}]>
 
print(res.first())
# {'detail__tel': '123456789'}

res = Book.objects.filter(title='三國演義').values('publish__name')
print(res)
print(res.first())
 
# <QuerySet [{'publish__name': '北方出版社'}]>
# {'publish__name': '北方出版社'}

res = Book.objects.filter(title='三國演義').values('authors__name')
print(res)
# <QuerySet [{'authors__name': 'frank'}, {'authors__name': 'lili'}]>

res = Author_Detail.objects.filter(tel='123456789').values('author__name')
print(res)
# <QuerySet [{'author__name': 'frank'}]>

res = Publish.objects.filter(name='北方出版社').values('book__title')
print(res)
# <QuerySet [{'book__title': '三國演義'}, {'book__title': '水滸傳'}]>

res = Author.objects.filter(name='lili').values('book__title')
print(res)
# <QuerySet [{'book__title': '三國演義'}, {'book__title': '紅樓夢'}, {'book__title': '西遊記'}]>

res = Publish.objects.filter(book__pk= 4)
print(res)
 
# <QuerySet [<Publish: Publish object>]>
res = Publish.objects.filter(book__title= '西遊記')
print(res)
# 結果相同

# 需求1：查詢北京出版社出版過的所有書籍的名字以及作者的姓名、手機號
# 方式一：基表為Publish
res=Publish.objects.filter(name='北方出版社').values_list('book__title','book__authors__name','book__authors__author_detail__tel')
 
# 方式二：基表為Book
res=Book.objects.filter(publish__name='北方出版社').values_list('title','authors__name','authors__author_detail__tel')
 
# 迴圈列印結果均為
for obj in res:
    print(obj)
 
 
 
# 需求2：查詢手機號以186開頭的作者出版過的所有書籍名稱以及出版社名稱
# 方式一：基表為AuthorDetail
res=AuthorDetail.objects.filter(tel__startswith='186').values_list('author__book__title','author__book__publish__name')
 
# 方式二：基表為Book
res=Book.objects.filter(authors__author_detail__tel__startswith='186').values_list('title','publish__name')
 
# 方式三：基表為Publish
res=Publish.objects.filter(book__authors__author_detail__tel__startswith='186').values_list('book__title','name')
 
# 迴圈列印結果均為
for obj in res:
    print(obj)