ORM 进阶操作

2022-02-22 13:54:37

ORM多表操作

一、创建模型

作者模型：一个作者有姓名和年龄。

作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息；作者详情模型和作者模型之间是一对一的关系。

出版商模型：出版商有名称，所在城市以及邮箱。

书籍模型：书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系；一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系。

from django.db import models

class Author(models.Model):

    id = models.AutoField(primary_key=True)

    name = models.CharField(max_length=32)

    age = models.IntegerField()

    # on_delete=models.CASCADE  当删除关联表中的数据时，与之关联也删除

    authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)

class AuthorDetail(models.Model):

    id = models.AutoField(primary_key=True)

    birthday = models.DateField()

    telephone = models.BigIntegerField()

    address = models.CharField(max_length=64)

class Publisher(models.Model):

    id = models.AutoField(primary_key=True)

    name = models.CharField(max_length=32)

    city = models.CharField(max_length=32)

    email = models.EmailField()

class Book(models.Model):

    id = models.AutoField(primary_key=True)

    title = models.CharField(max_length=32)

    publishDate = models.DateField()

    price = models.DecimalField(max_digits=5, decimal_places=2)

    # 与Publish建立一对多的关系，外键字段一般建立在多的一方

    publisher = models.ForeignKey(to="Publisher", to_field="id", on_delete=models.CASCADE)

    # 与Author表建立多对多的关系，ManyToManyField通常设置在正向查询多的那一边，自动创建第三张表

    authors = models.ManyToManyField(to="Author")

二、插入测试数据

从MySQL导出的数据脚本：数据库名为orm

/*

Navicat MySQL Data Transfer

Source Server         : MyDatabase

Source Server Version : 50723

Source Host           : localhost:3306

Source Database       : orm

Target Server Type    : MYSQL

Target Server Version : 50723

File Encoding         : 65001

Date: 2018-11-21 17:00:14

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for app01_author

-- ----------------------------

DROP TABLE IF EXISTS `app01_author`;

CREATE TABLE `app01_author` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(32) NOT NULL,

  `age` int(11) NOT NULL,

  `authorDetail_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `authorDetail_id` (`authorDetail_id`),

  CONSTRAINT `app01_author_authorDetail_id_a86eedd2_fk_app01_authordetail_id` FOREIGN KEY (`authorDetail_id`) REFERENCES `app01_authordetail` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of app01_author

-- ----------------------------

INSERT INTO `app01_author` VALUES ('1', '喜洋洋', '6', '1');

INSERT INTO `app01_author` VALUES ('2', '灰太狼', '8', '2');

-- ----------------------------

-- Table structure for app01_authordetail

-- ----------------------------

DROP TABLE IF EXISTS `app01_authordetail`;

CREATE TABLE `app01_authordetail` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `birthday` date NOT NULL,

  `telephone` bigint(20) NOT NULL,

  `address` varchar(64) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of app01_authordetail

-- ----------------------------

INSERT INTO `app01_authordetail` VALUES ('1', '2012-12-12', '13711111111', '羊村');

INSERT INTO `app01_authordetail` VALUES ('2', '2010-10-10', '13522222222', '狼堡');

-- ----------------------------

-- Table structure for app01_book

-- ----------------------------

DROP TABLE IF EXISTS `app01_book`;

CREATE TABLE `app01_book` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `title` varchar(32) NOT NULL,

  `publishDate` date NOT NULL,

  `price` decimal(5,2) NOT NULL,

  `publisher_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `app01_book_publisher_id_e407867a_fk_app01_publisher_id` (`publisher_id`),

  CONSTRAINT `app01_book_publisher_id_e407867a_fk_app01_publisher_id` FOREIGN KEY (`publisher_id`) REFERENCES `app01_publisher` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of app01_book

-- ----------------------------

INSERT INTO `app01_book` VALUES ('1', '时间中的孩子', '2018-11-01', '58.00', '1');

INSERT INTO `app01_book` VALUES ('2', '遮蔽的天空', '2018-09-05', '49.00', '2');

INSERT INTO `app01_book` VALUES ('3', '凝视太阳', '2018-10-20', '42.00', '1');

-- ----------------------------

-- Table structure for app01_book_authors

-- ----------------------------

DROP TABLE IF EXISTS `app01_book_authors`;

CREATE TABLE `app01_book_authors` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `book_id` int(11) NOT NULL,

  `author_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `app01_book_authors_book_id_author_id_36f1e11a_uniq` (`book_id`,`author_id`),

  KEY `app01_book_authors_author_id_5acae95a_fk_app01_author_id` (`author_id`),

  CONSTRAINT `app01_book_authors_author_id_5acae95a_fk_app01_author_id` FOREIGN KEY (`author_id`) REFERENCES `app01_author` (`id`),

  CONSTRAINT `app01_book_authors_book_id_19c7077f_fk_app01_book_id` FOREIGN KEY (`book_id`) REFERENCES `app01_book` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of app01_book_authors

-- ----------------------------

INSERT INTO `app01_book_authors` VALUES ('1', '1', '1');

INSERT INTO `app01_book_authors` VALUES ('3', '2', '2');

INSERT INTO `app01_book_authors` VALUES ('2', '3', '1');

-- ----------------------------

-- Table structure for app01_publisher

-- ----------------------------

DROP TABLE IF EXISTS `app01_publisher`;

CREATE TABLE `app01_publisher` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(32) NOT NULL,

  `city` varchar(32) NOT NULL,

  `email` varchar(254) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of app01_publisher

-- ----------------------------

INSERT INTO `app01_publisher` VALUES ('1', '苹果出版社', '广州', 'gz@qq.com');

INSERT INTO `app01_publisher` VALUES ('2', '香蕉出版社', '深圳', 'sz@qq.com');

-- ----------------------------

-- Table structure for auth_group

-- ----------------------------

DROP TABLE IF EXISTS `auth_group`;

CREATE TABLE `auth_group` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(80) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of auth_group

-- ----------------------------

-- ----------------------------

-- Table structure for auth_group_permissions

-- ----------------------------

DROP TABLE IF EXISTS `auth_group_permissions`;

CREATE TABLE `auth_group_permissions` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `group_id` int(11) NOT NULL,

  `permission_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `auth_group_permissions_group_id_permission_id_0cd325b0_uniq` (`group_id`,`permission_id`),

  KEY `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` (`permission_id`),

  CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),

  CONSTRAINT `auth_group_permissions_group_id_b120cbf9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of auth_group_permissions

-- ----------------------------

-- ----------------------------

-- Table structure for auth_permission

-- ----------------------------

DROP TABLE IF EXISTS `auth_permission`;

CREATE TABLE `auth_permission` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `content_type_id` int(11) NOT NULL,

  `codename` varchar(100) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `auth_permission_content_type_id_codename_01ab375a_uniq` (`content_type_id`,`codename`),

  CONSTRAINT `auth_permission_content_type_id_2f476e4b_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of auth_permission

-- ----------------------------

INSERT INTO `auth_permission` VALUES ('1', 'Can add log entry', '1', 'add_logentry');

INSERT INTO `auth_permission` VALUES ('2', 'Can change log entry', '1', 'change_logentry');

INSERT INTO `auth_permission` VALUES ('3', 'Can delete log entry', '1', 'delete_logentry');

INSERT INTO `auth_permission` VALUES ('4', 'Can add permission', '2', 'add_permission');

INSERT INTO `auth_permission` VALUES ('5', 'Can change permission', '2', 'change_permission');

INSERT INTO `auth_permission` VALUES ('6', 'Can delete permission', '2', 'delete_permission');

INSERT INTO `auth_permission` VALUES ('7', 'Can add user', '3', 'add_user');

INSERT INTO `auth_permission` VALUES ('8', 'Can change user', '3', 'change_user');

INSERT INTO `auth_permission` VALUES ('9', 'Can delete user', '3', 'delete_user');

INSERT INTO `auth_permission` VALUES ('10', 'Can add group', '4', 'add_group');

INSERT INTO `auth_permission` VALUES ('11', 'Can change group', '4', 'change_group');

INSERT INTO `auth_permission` VALUES ('12', 'Can delete group', '4', 'delete_group');

INSERT INTO `auth_permission` VALUES ('13', 'Can add content type', '5', 'add_contenttype');

INSERT INTO `auth_permission` VALUES ('14', 'Can change content type', '5', 'change_contenttype');

INSERT INTO `auth_permission` VALUES ('15', 'Can delete content type', '5', 'delete_contenttype');

INSERT INTO `auth_permission` VALUES ('16', 'Can add session', '6', 'add_session');

INSERT INTO `auth_permission` VALUES ('17', 'Can change session', '6', 'change_session');

INSERT INTO `auth_permission` VALUES ('18', 'Can delete session', '6', 'delete_session');

INSERT INTO `auth_permission` VALUES ('19', 'Can add book', '7', 'add_book');

INSERT INTO `auth_permission` VALUES ('20', 'Can change book', '7', 'change_book');

INSERT INTO `auth_permission` VALUES ('21', 'Can delete book', '7', 'delete_book');

INSERT INTO `auth_permission` VALUES ('22', 'Can add author detail', '8', 'add_authordetail');

INSERT INTO `auth_permission` VALUES ('23', 'Can change author detail', '8', 'change_authordetail');

INSERT INTO `auth_permission` VALUES ('24', 'Can delete author detail', '8', 'delete_authordetail');

INSERT INTO `auth_permission` VALUES ('25', 'Can add publisher', '9', 'add_publisher');

INSERT INTO `auth_permission` VALUES ('26', 'Can change publisher', '9', 'change_publisher');

INSERT INTO `auth_permission` VALUES ('27', 'Can delete publisher', '9', 'delete_publisher');

INSERT INTO `auth_permission` VALUES ('28', 'Can add author', '10', 'add_author');

INSERT INTO `auth_permission` VALUES ('29', 'Can change author', '10', 'change_author');

INSERT INTO `auth_permission` VALUES ('30', 'Can delete author', '10', 'delete_author');

-- ----------------------------

-- Table structure for auth_user

-- ----------------------------

DROP TABLE IF EXISTS `auth_user`;

CREATE TABLE `auth_user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `password` varchar(128) NOT NULL,

  `last_login` datetime(6) DEFAULT NULL,

  `is_superuser` tinyint(1) NOT NULL,

  `username` varchar(150) NOT NULL,

  `first_name` varchar(30) NOT NULL,

  `last_name` varchar(30) NOT NULL,

  `email` varchar(254) NOT NULL,

  `is_staff` tinyint(1) NOT NULL,

  `is_active` tinyint(1) NOT NULL,

  `date_joined` datetime(6) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `username` (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of auth_user

-- ----------------------------

-- ----------------------------

-- Table structure for auth_user_groups

-- ----------------------------

DROP TABLE IF EXISTS `auth_user_groups`;

CREATE TABLE `auth_user_groups` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `group_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `auth_user_groups_user_id_group_id_94350c0c_uniq` (`user_id`,`group_id`),

  KEY `auth_user_groups_group_id_97559544_fk_auth_group_id` (`group_id`),

  CONSTRAINT `auth_user_groups_group_id_97559544_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),

  CONSTRAINT `auth_user_groups_user_id_6a12ed8b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of auth_user_groups

-- ----------------------------

-- ----------------------------

-- Table structure for auth_user_user_permissions

-- ----------------------------

DROP TABLE IF EXISTS `auth_user_user_permissions`;

CREATE TABLE `auth_user_user_permissions` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `permission_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `auth_user_user_permissions_user_id_permission_id_14a6b632_uniq` (`user_id`,`permission_id`),

  KEY `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` (`permission_id`),

  CONSTRAINT `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),

  CONSTRAINT `auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of auth_user_user_permissions

-- ----------------------------

-- ----------------------------

-- Table structure for django_admin_log

-- ----------------------------

DROP TABLE IF EXISTS `django_admin_log`;

CREATE TABLE `django_admin_log` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `action_time` datetime(6) NOT NULL,

  `object_id` longtext,

  `object_repr` varchar(200) NOT NULL,

  `action_flag` smallint(5) unsigned NOT NULL,

  `change_message` longtext NOT NULL,

  `content_type_id` int(11) DEFAULT NULL,

  `user_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `django_admin_log_content_type_id_c4bce8eb_fk_django_co` (`content_type_id`),

  KEY `django_admin_log_user_id_c564eba6_fk_auth_user_id` (`user_id`),

  CONSTRAINT `django_admin_log_content_type_id_c4bce8eb_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`),

  CONSTRAINT `django_admin_log_user_id_c564eba6_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of django_admin_log

-- ----------------------------

-- ----------------------------

-- Table structure for django_content_type

-- ----------------------------

DROP TABLE IF EXISTS `django_content_type`;

CREATE TABLE `django_content_type` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `app_label` varchar(100) NOT NULL,

  `model` varchar(100) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `django_content_type_app_label_model_76bd3d3b_uniq` (`app_label`,`model`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of django_content_type

-- ----------------------------

INSERT INTO `django_content_type` VALUES ('1', 'admin', 'logentry');

INSERT INTO `django_content_type` VALUES ('10', 'app01', 'author');

INSERT INTO `django_content_type` VALUES ('8', 'app01', 'authordetail');

INSERT INTO `django_content_type` VALUES ('7', 'app01', 'book');

INSERT INTO `django_content_type` VALUES ('9', 'app01', 'publisher');

INSERT INTO `django_content_type` VALUES ('4', 'auth', 'group');

INSERT INTO `django_content_type` VALUES ('2', 'auth', 'permission');

INSERT INTO `django_content_type` VALUES ('3', 'auth', 'user');

INSERT INTO `django_content_type` VALUES ('5', 'contenttypes', 'contenttype');

INSERT INTO `django_content_type` VALUES ('6', 'sessions', 'session');

-- ----------------------------

-- Table structure for django_migrations

-- ----------------------------

DROP TABLE IF EXISTS `django_migrations`;

CREATE TABLE `django_migrations` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `app` varchar(255) NOT NULL,

  `name` varchar(255) NOT NULL,

  `applied` datetime(6) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of django_migrations

-- ----------------------------

INSERT INTO `django_migrations` VALUES ('1', 'contenttypes', '0001_initial', '2018-11-21 03:38:47.493316');

INSERT INTO `django_migrations` VALUES ('2', 'auth', '0001_initial', '2018-11-21 03:38:56.541332');

INSERT INTO `django_migrations` VALUES ('3', 'admin', '0001_initial', '2018-11-21 03:38:58.428935');

INSERT INTO `django_migrations` VALUES ('4', 'admin', '0002_logentry_remove_auto_add', '2018-11-21 03:38:58.491335');

INSERT INTO `django_migrations` VALUES ('5', 'app01', '0001_initial', '2018-11-21 03:39:03.209544');

INSERT INTO `django_migrations` VALUES ('6', 'contenttypes', '0002_remove_content_type_name', '2018-11-21 03:39:04.114346');

INSERT INTO `django_migrations` VALUES ('7', 'auth', '0002_alter_permission_name_max_length', '2018-11-21 03:39:04.816347');

INSERT INTO `django_migrations` VALUES ('8', 'auth', '0003_alter_user_email_max_length', '2018-11-21 03:39:05.643148');

INSERT INTO `django_migrations` VALUES ('9', 'auth', '0004_alter_user_username_opts', '2018-11-21 03:39:05.689948');

INSERT INTO `django_migrations` VALUES ('10', 'auth', '0005_alter_user_last_login_null', '2018-11-21 03:39:06.126749');

INSERT INTO `django_migrations` VALUES ('11', 'auth', '0006_require_contenttypes_0002', '2018-11-21 03:39:06.173549');

INSERT INTO `django_migrations` VALUES ('12', 'auth', '0007_alter_validators_add_error_messages', '2018-11-21 03:39:06.313949');

INSERT INTO `django_migrations` VALUES ('13', 'auth', '0008_alter_user_username_max_length', '2018-11-21 03:39:06.906750');

INSERT INTO `django_migrations` VALUES ('14', 'sessions', '0001_initial', '2018-11-21 03:39:07.437151');

-- ----------------------------

-- Table structure for django_session

-- ----------------------------

DROP TABLE IF EXISTS `django_session`;

CREATE TABLE `django_session` (

  `session_key` varchar(40) NOT NULL,

  `session_data` longtext NOT NULL,

  `expire_date` datetime(6) NOT NULL,

  PRIMARY KEY (`session_key`),

  KEY `django_session_expire_date_a5c62663` (`expire_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of django_session

-- ----------------------------

orm.sql

author表：

authordetail表：

book表：

book_authors表：

publisher表：

三、基于对象的跨表查询

一对多查询（Publisher 与 Book）

正向查询（按字段：publisher）

# 查询"时间中的孩子"书籍的出版社的邮箱

models.Book.objects.filter(title="时间中的孩子").first().publisher.email

反向查询（按表名：book_set）

# 查询"苹果出版社"出版的书籍名称

pub_obj = models.Publisher.objects.filter(name="苹果出版社").first()

book_qs = pub_obj.book_set.all()

for book in book_qs:

    print(book.title)

思路解析：出版社找书，属于反向查找，所以 pub_obj.book_set，一个出版社可能出版多本书，所以用 all() 接收，即 book_set.all()，接收到的书属于QuerySet，所以要用 for 循环取到每一个具体的书对象。

一对一查询（Author 与 AuthorDetail）

正向查询（按字段：authorDetail）

# 查询"灰太狼"作者的手机号

models.Author.objects.filter(name="灰太狼").first().authorDetail.telephone

反向查询（按表名：author）

因为是一对一查询，没有 _set，查出的结果只有一个！

# 查询地址在"羊村"的作者的名字

models.AuthorDetail.objects.filter(address="羊村").first().author.name

多对多查询（Author 与 Book）

正向查询（按字段：authors）

# 查询"遮蔽的天空"的作者的名字以及作者的生日

book_obj = models.Book.objects.filter(title="遮蔽的天空").first()

authors_qs = book_obj.authors.all()

for author in authors_qs:

    print(author.name, author.authorDetail.birthday)

反向查询（按表名：book_set）

# 查询"喜洋洋"出版过的书籍的名字

author_obj = models.Author.objects.filter(name="喜洋洋").first()

book_qs = author_obj.book_set.all()

for book in book_qs:

    print(book.title)

思路解析："查询遮蔽的天空的作者的名字以及作者的生日"，这题目让人看上去会觉得这本书的作者只有一个，这样的想法是错误的，一定要牢记查询的目标结果是否是有多个的，有多个结果，那么就用 all() 方法去接收，而 all() 方法返回的必然是 QuerySet，QuerySet能 .字段名 么？不能！！！头脑一定要非常清楚每一次查的结果到底是 object，还是QuerySet。

注意：

可以通过在 ForeignKey() 和 ManyToManyField() 中设置 related_name 的值来覆写 FOO_set 的名称。

例如，在 Book model 中修改如下：

models.ForeignKey(to="Publisher", to_field="id", on_delete=models.CASCADE)

# 修改为

models.ForeignKey(to="Publisher", to_field="id", related_name="book", on_delete=models.CASCADE)

那么：

# 查询"苹果出版社"出版的书籍名称

pub_obj = models.Publisher.objects.filter(name="苹果出版社").first()

book_qs = pub_obj.book.all()

for book in book_qs:

    print(book.title)

对比上面一对一中的反向查询，如果设置了 related_name="book"，即用 book.all() 代替了 book_set.all()

四、基于双下划线的跨表查询

Django 还提供了一种直观而高效的方式在查询（lookups）中表示关联关系，它能自动确认 SQL JOIN 联系。要做跨关系查询，就使用两个下划线来链接模型（model）间关联字段的名称，直到最终链接到你想要的 model 为止。

关键点：正向查询按字段，反向查询按表名小写用来告诉ORM引擎join哪张表

一对多查询

题目：查询"苹果出版社"出版过的所有书籍的名字与价格

# 正向查询（按字段：publisher）

models.Book.objects.filter(publisher__name="苹果出版社").values("title", "price")

# 反向查询（按表名：book）

models.Publisher.objects.filter(name="苹果出版社").values("book__title", "book__price")

一对一查询

题目：查询"灰太狼"作者的手机号

# 正向查询（按字段：authorDetail）

models.Author.objects.filter(name="灰太狼").values("authorDetail__telephone")

# 反向查询（按表名：author）

models.AuthorDetail.objects.filter(author__name="灰太狼").values("telephone")

多对多查询

题目：查询"喜洋洋"出版过的所有书籍的名字

# 正向查询（按字段：authors）

models.Book.objects.filter(authors__name="喜洋洋").values("title")

# 反向查询（按表名：book）

models.Author.objects.filter(name="喜洋洋").values("book__title")

进阶（连续跨表）

题目1：查询香蕉出版社出版过的所有书籍的名字以及作者的姓名

# 正向查询

models.Book.objects.filter(publisher__name="香蕉出版社").values("title", "authors__name")

# 反向查询

models.Publisher.objects.filter(name="香蕉出版社").values("book__title", "book__authors__name")

题目2：查询手机号以135开头的作者出版过的书籍名称以及书籍对应的出版社名称

# 正向查询

models.Book.objects.filter(authors__authorDetail__telephone__startswith="").values("title", "publisher__name")

# 反向查询

models.Author.objects.filter(authorDetail__telephone__startswith="").values("book__title", "book__publisher__name")

注意：

反向查询时，如果定义了 related_name ，则用 related_name 设定的值替换表名，例如：

models.ForeignKey(to="Publisher", to_field="id", on_delete=models.CASCADE)

# 修改为

models.ForeignKey(to="Publisher", to_field="id", related_name="xxx", on_delete=models.CASCADE)

例如：查询香蕉出版社出版过的所有书籍的名字以及作者的姓名

# 反向查询

models.Publisher.objects.filter(name="香蕉出版社").values("xxx__title", "xxx__authors__name")

五、聚合查询和分组查询

聚合（aggregate）

aggregate()是 QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。

from django.db.models import Avg,Sum,Max,Min,Count

示例：

# 获取所有书的平均值

models.Book.objects.all().aggregate(Avg("price"))

# {'price__avg': 49.666667}

如果想要为聚合值指定一个名称，可以向聚合子句提供它；除此之外，可以添加多个参数：

models.Book.objects.all().aggregate(p_max=Max("price"), p_min=Min("price"))

# {'p_min': Decimal('42.00'), 'p_max': Decimal('58.00')}

分组（annotate）

1. 单表查询的分组

# 原生SQL语句，按照部门分组求平均工资

select dept,avg(salary) from employee group by dept

ORM单表 --> 查询每个部门的平均工资

models.employee.objects.values("dept").annotate(avg=Avg("salary")).values("dept", "avg")

2. 连表查询的分组

# 原生SQL语句，按照部门分组求平均工资

select dept.name,avg(salary) from employee inner join dept on (employee.dept_id=dept.id) group by dept_id;

ORM查询：

from django.db.models import Avg

models.Dept.objects.annotate(avg=Avg("employee__salary")).values("name", "avg")

# <QuerySet [{'name': '保安部', 'avg': 1900.0}, {'name': '财务部', 'avg': 2750.0}, {'name': '人事部', 'avg': 3250.0}, {'name': '教学部', 'avg': None}]>

3. 更多示例

①统计每一本书的作者个数

book_qs = models.Book.objects.annotate(c=Count("authors"))

for book in book_qs:

    print(book.c)

②统计出每个出版社卖的最便宜的书的价格

方法一：

pub_qs = models.Publisher.objects.annotate(min_p=Min("book__price"))

for pub in pub_qs:

    print(pub.min_p)

方法二：

book_qs = models.Book.objects.values("publisher__name").annotate(min_p=Min("price"))

for book in book_qs:

    print(book["min_p"])

③统计不止一个作者的图书

book_qs = models.Book.objects.annotate(c=Count("authors")).filter(c__gt=1)

for book in book_qs:

    print(book.title)

④根据一本图书作者数量的多少对查询集 QuerySet 进行排序

book_qs = models.Book.objects.annotate(c=Count("authors")).order_by("c")

for book in book_qs:

    print(book.title, book.c)

⑤查询各个作者出的书的总价格

author_qs = models.Author.objects.annotate(sum_p=Sum("book__price")).values("name", "sum_p")

for author in author_qs:

    print(author["name"], author["sum_p"])

六、F查询和Q查询

F查询

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

添加字段示例：

from django.db.models import F

①查询卖出数(sale) 大于库存数(store) 的所有书的书名

book_qs = models.Book.objects.filter(sale__gt=F("store"))

for book in book_qs:

    print(book.title)

Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。

②刷单：把每一本书的卖出数加1乘以3

models.Book.objects.update(sale=(F("sale") + 1) * 3)

③修改操作也可以使用 F 函数；比如将每一本书的价格提高10元

models.Book.objects.all().update(price=F("price") + 10)

引申：

④给所有书名后面加上 (第一版)

from django.db.models.functions import Concat

from django.db.models import Value

models.Book.objects.all().update(title=Concat(F("title"), Value("("), Value("第一版"), Value(")")))

Q查询

filter() 等方法中的关键字参数查询都是一起进行 AND 的；如果需要执行更复杂的查询（例如 OR 语句），可以使用 Q 对象。

from django.db.models import Q

示例①：对比 AND 和OR

# 查询价格小于50 且 卖出数大于300的书

models.Book.objects.filter(price__lt=50, sale__gt=300)

# 查询价格小于50 或 卖出数大于300的书

models.Book.objects.filter(Q(price__lt=50) | Q(sale__gt=300))

示例②：查询作者名是喜洋洋或灰太狼的书

book_qs = models.Book.objects.filter(Q(authors__name="喜洋洋") | Q(authors__name="灰太狼"))

for book in book_qs:

    print(book.title)

示例③：Q查询和字段查询同时存在时，字段查询要放在Q查询的后面（此时，这个字段和Q查询就是 "且" 关系）

book_qs = models.Book.objects.filter(Q(authors__name="喜洋洋") | Q(authors__name="灰太狼"), title__contains="太阳")

for book in book_qs:

    print(book.title)

示例④：查询作者名是灰太狼且出版社id不是1的书

book_qs = models.Book.objects.filter(Q(authors__name="灰太狼") & ~Q(publisher="")).values("title")

for book in book_qs:

    print(book["title"])

码农公寓