django ORM 查询关系

dongshangtong · · 362 次点击 · · 开始浏览    

模型关系:

from django.db import models


# Create your models here.


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", on_delete=models.CASCADE)


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


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)

    # 与Publish建立一对多的关系,外键字段建立在多的一方
    publish = models.ForeignKey(to="Publish", to_field="nid", on_delete=models.CASCADE)
    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个,自动创建第三张表
    authors = models.ManyToManyField(to='Author', )

1.查询python这本书籍的出版社的邮箱

  # 正向查询按字段
    v = models.Book.objects.filter(title="python教程").first()
    print(v.publish.email)
   # beijing@163.com
  1. 反向查询按 表名小写_set.all()
    # 海南出版社出版的书籍名称
    # filter 和 get 区别, filter 查出来是集合,所以经常跟first(), get 查出来是对象。
     v = models.Publish.objects.filter(name='海南出版社').first()
    # v = models.Publish.objects.get(name='海南出版社')
     for book in v.book_set.all():
         print(book.title)
# golang教程
 #nodejs 教程

3.查询golang作者的年龄

   #               按字段(authors.all())
    # 多对多   book  ----------------------->  author
    #               <----------------
    #                  book_set.all()

ath = models.Book.objects.filter(title="golang教程").first()
    for auther in ath.authors.all():
        print(auther.name, auther.age)

# 小明 10
# 莉君 12

4.查询莉君出版过的书籍名称

ath = models.Author.objects.filter(name='莉君').first()
    for book in ath.book_set.all():
        print(book.title)

# golang教程
# nodejs 教程
  1. 查询莉君的手机号
    ath = models.Author.objects.filter(name='莉君').first()
    print(ath.authorDetail.telephone)

# 18938641414
  1. 查询家在深圳的作者名字
    dais = models.AuthorDetail.objects.filter(addr='深圳市')

    for ath in dais:
        print(ath.author.name)
# 莉君
  1. 北京出版社出版的书籍名称
    ret1 = models.Publish.objects.filter(name="成都出版社").values('book__title')
    print(ret1.query)
    '''
    SELECT `app01_book`.`title` FROM `app01_publish` 
    LEFT OUTER JOIN `app01_book`
     ON (`app01_publish`.`nid` = `app01_book`.`publish_id`) WHERE `app01_publish`.`name` = 成都出版社
    '''
    # 方式2:
    ret2 = models.Book.objects.filter(publish__name="成都出版社").values("title")
    print(ret2.query)
    """
    SELECT `app01_book`.`title` FROM `app01_book`
     INNER JOIN `app01_publish` 
     ON (`app01_book`.`publish_id` = `app01_publish`.`nid`) WHERE `app01_publish`.`name` = 成都出版社
    """

7.查询手机号以188开头的作者出版过的书籍名称以及书籍对应的出版社名称

    # ret3 = models.Book.objects.filter(authors__authorDetail__telephone__startswith="188").values('title', 'publish__name')
    #
    # print(ret3.query)

    """
    SELECT `app01_book`.`title`, `app01_publish`.`name` FROM `app01_book`
     INNER JOIN `app01_book_authors` ON (`app01_book`.`nid` = `app01_book_authors`.`book_id`)
     INNER JOIN `app01_author` ON (`app01_book_authors`.`author_id` = `app01_author`.`nid`) 
     INNER JOIN `app01_authordetail` ON (`app01_author`.`authorDetail_id` = `app01_authordetail`.`nid`) 
     INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`nid`) 
     WHERE `app01_authordetail`.`telephone` LIKE BINARY 188%

    """

本文来自:简书

感谢作者:dongshangtong

查看原文:django ORM 查询关系

入群交流(该群和以上内容无关):Go中文网 QQ交流群:731990104 或 加微信入微信群:274768166 备注:入群; 公众号:Go语言中文网

362 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传