Django学习7

分组查询

from django.db.models import Avg, Max, Min, Count, Sum
# 1.统计每个出版社出版的书籍的平均价格  (select avg(price) from app01_book GROUP BY publishs_id;)
    models.Book.objects.values('publishs_id').annotate(a=Avg('price'))
    
    models.Publish.objects.values('id').annotate(a=Avg('book__price'))

    models.Publish.objects.values('name').annotate(a=Avg('book__price'))

    # 这种写法默认按照publish的主键(id)进行分组
    models.Publish.objects.annotate(a=Avg('book__price')).values('name','a')
	
# 2.找出出版社出版的书籍的平均价格大于2的数据(相当于having过滤)
    models.Publish.objects.annotate(a=Avg('book__price')).values('name','a').filter(a__gt=2)

F查询

from django.db.models import F
    # F查询  --- 针对单张表内的字段进行操作
   
    # 1.查找书籍表中点赞数大于评论数的
    models.Book.objects.filter(good__gt=F('comment'))
	
    # 支持运算
    models.Book.objects.filter(good__gt=F('comment')+20)
    
    # 2.所有书籍的价格都增加20
    models.Book.objects.all().update(price=F('price')+20)

Q查询 ( 或 | 与 & 非~ )

from django.db.models import Q
# 1.评论数和点赞数都大于80的书籍
    models.Book.objects.filter(good__gt=80,comment__gt=80)

# 2.评论数或点赞数大于80的书籍
    models.Book.objects.filter(Q(good__gt=80)|Q(comment__gt=80))

# 3.点赞数小于等于80或评论数大于80的书籍
    models.Book.objects.filter(~Q(good__gt=80)|Q(comment__gt=80))

# 3.点赞数和评论数都小于等于80的书籍
    models.Book.objects.filter(~Q(good__gt=80)&~Q(comment__gt=80))

# Q可以嵌套,提高优先级.

ORM执行原生SQL语句

# raw (了解)
# 查询所有出版社信息
ret = models.Publish.objects.raw('select * from app01_publish;')
print(ret)  # <RawQuerySet: select * from app01_publish;>
for i in ret:
    print(i.name)

# 直接执行自定义sql
from django.db import connection
cur = connection.cursor()
cur.execute("""select * from app01_publish""")
ret = cur.fetchone()
print(ret)

打印原生sql的两种方法

# 通过connection(写上才会打印,可以单独查看)
from django.db import connection
...
print(connection.queries)

# settings.py中设置(每次orm执行的时候都会打印)
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}

ORM的坑

查询每个作者出版的所有书的最高价格以及最高价格的那本书的名称

情况一 : 设置了 sql_mode = only_full_group_by

# ORM:
models.Author.objects.annotate(m=Max('book__price')).values('name','m','book__title')
# 报错! 与sql_mode = only_full_group_by不兼容
# 设置了sql_mode = only_full_group_by,select的字段只能是分组依据的字段或聚合整理的数据

解决方案

SELECT
	t1.`NAME`,
	app01_book.title,
	t1.max_price
FROM
	(SELECT
			app01_author.id,
			app01_author. NAME,
			MAX(app01_book.price) max_price
		FROM
			app01_author
		INNER JOIN app01_book_authors ON app01_author.id = app01_book_authors.author_id
		INNER JOIN app01_book ON app01_book.id = app01_book_authors.book_id
		GROUP BY
			app01_author.id,
			app01_author.`name`) AS t1
INNER JOIN app01_book_authors ON t1.id = app01_book_authors.author_id
INNER JOIN app01_book ON app01_book_authors.book_id = app01_book.id
WHERE
	t1.max_price = app01_book.price;

# 思路:
1.先连作者表,书籍-作者表和书籍表,找到作者id,作者姓名和出版的书中最高的价格;
2.再将其作为一张大表,去连书籍-作者表和书籍表,其中连书籍表的时候用上价格和最高价格相同这个条件,来找到价格最高的书

情况二 : 没有设置 sql_mode = only_full_group_by

# 没有设置sql_mode = only_full_group_by的时候,分组后select非分组依据字段显示的内容会直接选取分组后的第一条作为展示,所以不会报错,但实际查询的数据是不正确的!

解决方案

# 思路:利用这个特点,我们先对价格进行一个降序,这样分组后它展示的第一条便恰好是我们所要查找的
SELECT
	*
FROM
	(SELECT
			app01_author.id,
			app01_book.title,
			app01_book.price
		FROM
			app01_author
		INNER JOIN app01_book_authors ON app01_author.id = app01_book_authors.author_id
		INNER JOIN app01_book ON app01_book.id = app01_book_authors.book_id
		ORDER BY
			app01_book.price DESC) AS t1
GROUP BY
	t1.id;

小结

ORM也有做不到的事, 当ORM实现不了的就要把方向转向原生SQL语句(ORM执行原生SQL语句的方法见上面)