ORM多表查询

一、多表查询方式

• 基于对象查询
• 基于双下划线查询

二、模型对象

class Book(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=30)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    pub_date = models.DateField()
    publish = models.ForeignKey('Publish',to_field="id",on_delete=models.CASCADE,null=True)
    author = models.ManyToManyField('Author',db_table='book2author')

class Publish(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=30)
    city = models.CharField(max_length=60)
    email = models.CharField(max_length=50)

class Author(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=30)
    age = models.IntegerField()
    ad = models.OneToOneField('AuthorDetail',on_delete=models.CASCADE,null=True)

class AuthorDetail(models.Model):
    birthday = models.DateField()
    telephone = models.IntegerField()
    addr = models.CharField(max_length=64)

三、基于对象查询

3.1一对多查询

通过取出某张基表来匹配出某个条件，之后看是正跨还是反跨得到对应的值，这里其实是分两步实现的。

3.1.1 一对多正跨

• 查询linux出版社的地址
• views.py

def query(request):
    linux = Book.objects.filter(title='Linux').first()
    print(linux.publish.city)
    return HttpResponse("查询成功")

3.1.2 一对多反跨

• 查询橘子出版社出版的书籍
• views.py

def query(request):
    publish = Publish.objects.filter(name='橘子出版社').first()
    print(publish.book_set.all().values("title"))
    return HttpResponse("查询成功")

3.2多对多查询

3.2.1 多对多正跨

• 查询linux书籍的所有作者
• views.py

def query(request):
    book = Book.objects.filter(title='Linux').first()
    print(book.author.all().values("name"))
    return HttpResponse("查询成功")

3.2.2 多对多反跨

• 查询alex作者出版过的所有书籍
• views.py

def query(request):
    alex = Author.objects.filter(name='alex').first()
    print(alex.book_set.all().values("title"))
    return HttpResponse("查询成功")

3.3一对一查询

3.3.1 一对一正跨

• 查询alex的手机号
• views.py

def query(request):
    alex = Author.objects.filter(name='alex').first()
    print(alex.ad.telephone)
    return HttpResponse("查询成功")

3.3.2 一对一反跨

• 查询手机号为234的名字
• views.py

def query(request):
    tel = AuthorDetail.objects.filter(telephone=234).first()
    print(tel.author.name)
    return HttpResponse("查询成功")

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

4.1一对多查询

4.1.1 一对多正跨

• 查询linux出版社的地址
• views.py

def query(request):
    linux = Book.objects.filter(title='Linux').values("publish__city")
    print(linux)
    return HttpResponse("查询成功")

4.1.2 一对多反跨

• 查询橘子出版社出版的书籍
• views.py

def query(request):
    publish = Publish.objects.filter(name='橘子出版社').values("book__title")
    print(publish)
    return HttpResponse("查询成功")

4.2多对多查询

4.2.1多对多正跨

• 查询linux书籍的所有作者
• views.py

def query(request):
    linux = Book.objects.filter(title='Linux').values("author__name")
    print(linux)
    return HttpResponse("查询成功")

4.2.2多对多反跨

• 查询alex作者出版过的所有书籍
• views.py

def query(request):
    alex = Author.objects.filter(name='alex').values("book__title")
    print(alex)
    return HttpResponse("查询成功")

4.3一对一查询

4.3.1 一对一正跨

• 查询alex的手机号
• views.py

def query(request):
    alex = Author.objects.filter(name='alex').values("ad__telephone")
    print(alex)
    return HttpResponse("查询成功")

4.3.2 一对一反跨

• 查询手机号为234的名字
• views.py

def query(request):
    tel = AuthorDetail.objects.filter(telephone=234).values("author__name")
    print(tel)
    return HttpResponse("查询成功")

五、基于双下划线连续跨表

对于要跨多张表也有具体实现方法(对于sql语法则是多个jion on)

• 查询橘子出版社出版过的所有书籍的名字以及作者的姓名

def query(request):
    book = Book.objects.filter(publish__name='橘子出版社').values("title","author__name")
    print(book)
    return HttpResponse("查询成功")

• 查询橘子出版社出版过的所有书籍的名字以及作者的姓名

def query(request):
    obj = Publish.objects.filter(name='橘子出版社').values("book__title","book__author__name")
    print(obj)
    return HttpResponse("查询成功")