基于双下划线的跨表查询 进阶连续跨表查询

基于双下划线的跨表查询 ：

# 连表查询
# 基于对象的跨表查询，先查对象，通过对象再去查另一个对象（正向：字段名，反向：表名小写/表名小写_set.all()）

# 地址为山东的作者写的所有书
# author_detail=models.AuthorDetail.objects.get(addr='山东')
# author=author_detail.author
# books=author.book_set.all()
# print(books[0].name)

# （作业）地址为山东的作者写的所有书的出版社名字

### 基于双下划线的跨表查之 一对多
# 正向：字段名
# 反向:表名小写
# filter，values,values_list(写 __ 跨表)
# 练习: 查询北京出版社出版过的所有书籍的名字与价格(一对多)
# SELECT `app01_book`.`name`, `app01_book`.`price` FROM `app01_publish` LEFT OUTER JOIN `app01_book` ON (`app01_publish`.`id` = `app01_book`.`publish_id`) WHERE `app01_publish`.`name` = '北京出版社' ;
# res=models.Publish.objects.filter(name='北京出版社').values('book__name','book__price')
# print(res)
#SELECT `app01_book`.`name`, `app01_book`.`price` FROM `app01_book` INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`) WHERE `app01_publish`.`name` = '北京出版社';

# res=models.Book.objects.filter(publish__name='北京出版社').values('name','price')
# print(res)

## 多对多
# 练习: 查询egon出过的所有书籍的名字,价格(多对多)
#反向
# res=models.Author.objects.filter(name='egon').values('book__name','book__price')
# print(res)

# 正向
# res=models.Book.objects.filter(authors__name='egon').values('name','price')
# print(res)

#查询egon的手机号
# res=models.Author.objects.filter(name='egon').values('author_detail__phone')
# print(res)
# res=models.AuthorDetail.objects.filter(author__name='egon').values('phone')
# print(res)

 

 

进阶连续跨表查询：

# 连续跨表
#查询北京出版社出版过的所有书籍的名字以及作者的姓名
# res=models.Publish.objects.filter(name='北京出版社').values('book__name','book__authors__name')
# print(res)

# res=models.Book.objects.filter(publish__name='北京出版社').values('name','authors__name')
# print(res)

# res=models.Author.objects.filter(book__publish__name='北京出版社').values('book__name','name')
# print(res)

# 手机号以189开头的作者出版过的所有 书籍名称 以及 出版社名称
# res=models.AuthorDetail.objects.filter(phone__startswith='189').values('author__book__name','author__book__publish__name')
# print(res)

# SELECT `app01_book`.`name`, `app01_publish`.`name` FROM `app01_author` INNER JOIN `app01_authordetail` ON (`app01_author`.`author_detail_id` = `app01_authordetail`.`id`) LEFT OUTER JOIN `app01_book_authors` ON (`app01_author`.`id` = `app01_book_authors`.`author_id`) LEFT OUTER JOIN `app01_book` ON (`app01_book_authors`.`book_id` = `app01_book`.`id`) LEFT OUTER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`) WHERE `app01_authordetail`.`phone` LIKE '189%' ;
res=models.Author.objects.filter(author_detail__phone__startswith='189').values('book__name','book__publish__name')
print(res)