ORM的分组查询你会了吗？

分组查询

setting

#在setting中写入下列内容可在控制台显示对应的SQL语句
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}

URL

from django.conf.urls import url
from django.contrib import admin

from app01 import views
urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^query/', views.query),
]

models.py

from django.db import models

# Create your models here.



class AuthorDetail(models.Model):
    nid = models.AutoField(primary_key=True)
    birthday = models.DateField()
    telephone = models.BigIntegerField()
    addr = models.CharField(max_length=64)

class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    # 与AuthorDetail建立一对一的关系
    authorDetail = models.OneToOneField(to="AuthorDetail")

class Publish(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()


class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    keepNum = models.IntegerField()
    commentNum = models.IntegerField()

    # 与Publish建立一对多的关系,外键字段建立在多的一方
    publish = models.ForeignKey(to="Publish", to_field="nid",)

    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
    authors = models.ManyToManyField(to='Author')

views

from django.shortcuts import render,HttpResponse # Create your views here. from .models import * def query(request): #查询语句 return HttpResponse("OK")

基于对象的跨表查询(子查询)

一对多：

　　正向查询按字段，反向查询按表名小写——set

 一对多：
      正向查询按字段
 Book--------------->Publish
     <---------------
      反向查询按表名小写_set

正向查：

#(1) 查询id为2的书籍对应出版社的邮箱
 obj=Book.objects.filter(nid=2).first()
 print(obj.publish.email)
 ret=obj.publish.email

对应的SQL语句：

select publihs_id from Book where nid=2

select   email    from  Publish where nid=（select publihs_id from Book where nid=2）

反向查：

#(2) 橘子出版社出版过的所有的书籍的名字
 obj=Publish.objects.filter(name="橘子出版社").first()
 ret=obj.book_set.all().values("title")

一对多：

　　正向查询按字段，反向查询按表名小写——set，或按XXX（related_name）

    
多对多：
         正向查询按字段
    Book--------------->Author
        <---------------
         反向查询按表名小写_set
         反向查询按xxx
正向查

# 查询人间失格所有作者的名字
obj=Book.objects.filter(title="人间失格").first()
ret=obj.authors.all().values("name")
print(ret)

反向查

 # 查询alex出版的书籍个数
  obj=Author.objects.filter(name="alex").first()
  ret=obj.book_set.all().count()
  print(ret)