django orm
正查询和反查
呵呵
https://www.cnblogs.com/liwenzhou/p/8660826.html
从有外键关联的表中查询没有外键的表 ---正查询; 反之反查询.比如如下字段:
class Author(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
email = models.CharField(max_length=32)
author_detail = models.OneToOneField(null=True,to="AuthorDetail", on_delete=models.CASCADE,related_name="author")
class AuthorDetail(models.Model):
address = models.CharField(max_length=32)
telephone = models.IntegerField()
正查询
使用author对象查询AuthorDetail对象信息,则是正查. 反之反查询
正查询: 如下: ad指AuthorDetail对象
对象查询:
author = Author.objects.get(id=1) author.name '金庸' author.ad.address '香港'
类名查询
正查询跨表获取某个字段,注意跨表时使用字段名称,而不是表名称:
Author.objects.filter(id=1).values("author_detail__address") (0.000) SELECT `blog_authordetail`.`address` FROM `blog_author` LEFT OUTER JOIN `blog_authordetail` ON (`blog_author`.`author_detail_id` = `blog_authordetail`.`id`) WHERE `blog_author`.`id` = 1 LIMIT 21; args=(1,) (0.000) SELECT `blog_authordetail`.`address` FROM `blog_author` LEFT OUTER JOIN `blog_authordetail` ON (`blog_author`.`author_detail_id` = `blog_authordetail`.`id`) WHERE `blog_author`.`id` = 1 LIMIT 21; args=(1,) <QuerySet [{'author_detail__address': '香港'}]> (0.000) SELECT @@SQL_AUTO_IS_NULL; args=None (0.000) SELECT @@SQL_AUTO_IS_NULL; args=None (0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None (0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None (0.000) SELECT `blog_authordetail`.`address` FROM `blog_author` LEFT OUTER JOIN `blog_authordetail` ON (`blog_author`.`author_detail_id` = `blog_authordetail`.`id`) WHERE `blog_author`.`id` = 1 LIMIT 21; args=(1,) (0.000) SELECT `blog_authordetail`.`address` FROM `blog_author` LEFT OUTER JOIN `blog_authordetail` ON (`blog_author`.`author_detail_id` = `blog_authordetail`.`id`) WHERE `blog_author`.`id` = 1 LIMIT 21; args=(1,)
Author.objects.get(id=1).author_detail.address (0.016) SELECT `blog_author`.`id`, `blog_author`.`name`, `blog_author`.`age`, `blog_author`.`email`, `blog_author`.`author_detail_id` FROM `blog_author` WHERE `blog_author`.`id` = 1 LIMIT 21; args=(1,) (0.016) SELECT `blog_author`.`id`, `blog_author`.`name`, `blog_author`.`age`, `blog_author`.`email`, `blog_author`.`author_detail_id` FROM `blog_author` WHERE `blog_author`.`id` = 1 LIMIT 21; args=(1,) (0.000) SELECT `blog_authordetail`.`id`, `blog_authordetail`.`address`, `blog_authordetail`.`telephone` FROM `blog_authordetail` WHERE `blog_authordetail`.`id` = 1 LIMIT 21; args=(1,) (0.000) SELECT `blog_authordetail`.`id`, `blog_authordetail`.`address`, `blog_authordetail`.`telephone` FROM `blog_authordetail` WHERE `blog_authordetail`.`id` = 1 LIMIT 21; args=(1,) '香港'
反查询
方式一: 对象.类名_set.all()
class Article:
tags = models.ManyToManyField(Tags, blank=True)
class Tags(models.Model):
""" 标签 Tag 也比较简单,和 Category 一样。 再次强调一定要继承 models.Model 类! """
name = models.CharField('标签', max_length=100)
反查询即从Tags查询Article对象信息:
tags = Tags.objects.get(id=1) out:<Tags: java标签1> tags.article_set out:<django.db.models.fields.related_descriptors.create_forward_many_to_many_manager.<locals>.ManyRelatedManager object at 0x00000157FF7B16A0> tags.article_set.get(id=1) out: <Article: java基础>
方式二:使用使用relate_name
作用: 用于反向查询时使用,代替小写表名__set,比如:related_name='publisher';
# 验证查询操作 class Book(models.Model): nid = models.AutoField(primary_key=True) title = models.CharField(max_length=32, default=None) create_time = models.DateTimeField(auto_now=True) price = models.DecimalField(decimal_places=2, max_digits=8, default=None) publisher = models.ForeignKey(to="Publish", on_delete=models.CASCADE,related_name='publisher') authors = models.ManyToManyField(to='Author',null=True,related_name='authors') reader=models.CharField(max_length=12,null=True) def __str__(self): return str(self.nid) + ':' + self.title class Publish(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) email = models.CharField(max_length=32)
则反查询值:
跨表查询对象----对象查询
p = Publish.objects.get(nid=1) p.name '北京文化' p.publisher.all() <QuerySet [<Book: 1:book_0>, <Book: 2:book_1>, <Book: 3:book_2>, <Book: 4:book_3>, <Book: 5:book_4>, <Book: 6:book_5>, <Book: 7:book_6>, <Book: 8:book_7>, <Book: 9:book_8>, <Book: 10:book_9>, <Book: 11:book_10>, <Book: 12:book_11>, <Book: 13:book_12>, <Book: 14:book_13>, <Book: 15:book_14>, <Book: 16:book_15>, <Book: 17:book_16>, <Book: 18:book_17>, <Book: 19:book_18>, <Book: 20:book_19>, '...(remaining elements truncated)...']>
跨表查询对象中的字段-----类名查询
Publish.objects.filter(nid=1).values("publisher__title") (0.000) SELECT `blog_book`.`title` FROM `blog_publish` LEFT OUTER JOIN `blog_book` ON (`blog_publish`.`nid` = `blog_book`.`publisher_id`) WHERE `blog_publish`.`nid` = 1 LIMIT 21; args=(1,) (0.000) SELECT `blog_book`.`title` FROM `blog_publish` LEFT OUTER JOIN `blog_book` ON (`blog_publish`.`nid` = `blog_book`.`publisher_id`) WHERE `blog_publish`.`nid` = 1 LIMIT 21; args=(1,) <QuerySet [{'publisher__title': 'book_0'}, {'publisher__title': 'book_1'}, {'publisher__title': 'book_2'}, {'publisher__title': 'book_3'}, {'publisher__title': 'book_4'}, {'publisher__title': 'book_5'}, {'publisher__title': 'book_6'}, {'publisher__title': 'book_7'}, {'publisher__title': 'book_8'}, {'publisher__title': 'book_9'}, {'publisher__title': 'book_10'}, {'publisher__title': 'book_11'}, {'publisher__title': 'book_12'}, {'publisher__title': 'book_13'}, {'publisher__title': 'book_14'}, {'publisher__title': 'book_15'}, {'publisher__title': 'book_16'}, {'publisher__title': 'book_17'}, {'publisher__title': 'book_18'}, {'publisher__title': 'book_19'}, '...(remaining elements truncated)...']> (0.000) SELECT @@SQL_AUTO_IS_NULL; args=None (0.000) SELECT @@SQL_AUTO_IS_NULL; args=None (0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None (0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None (0.000) SELECT `blog_book`.`title` FROM `blog_publish` LEFT OUTER JOIN `blog_book` ON (`blog_publish`.`nid` = `blog_book`.`publisher_id`) WHERE `blog_publish`.`nid` = 1 LIMIT 21; args=(1,) (0.000) SELECT `blog_book`.`title` FROM `blog_publish` LEFT OUTER JOIN `blog_book` ON (`blog_publish`.`nid` = `blog_book`.`publisher_id`) WHERE `blog_publish`.`nid` = 1 LIMIT 21; args=(1,)
related_name和related_query_name区别:
related_name:用于代替: 表名_set
related_query_name: 反向查询时用来代替表名字
备注:
具体的对象没有update()方法,只有queryset类型才有
总结:
多对多三种方式
1) 使用django创建第三张表:
优点: 查询方便,可以利用djaogo的正向和反向查询
缺点: 第三张表中无法添加多余字段
2)自己创建第三张表,即自己维护多对多关系,但是这样查询也需要自己做关联
优点:任意定义第三张表
缺地:查询需要自己做关联
3)自己创建第三张表,但是查询时保留django的正向和反向查询
总结:
方式3)如下:
自己创建第三张关系表
查询技巧
查询某个字段
分组
技巧:
models.Employee.object.all() ---等价于: select * from Employee
models.Employee.object.values("name","age") ---等价于 select name,age from Employee
annotate函数:
A: annotate函数以前一个values()中的字段进行分组,以后一个values()函数进行挑选字段
B: 碰到反查 :表名__字段名 ---对应sql : inner join
C: 碰到正查 :关联字段名__字段名 ---对应sql : inner join
D:如果是Employee.object.all() .annotat()则是按照表的id进行group by
实例2:
如下:
posted on 2020-02-18 19:24 zhulibin2012 阅读(101) 评论(0) 编辑 收藏 举报