django模型系统（三）--多对多，一对一以及跨表查询

-Many-to-Many
*** 指定了中间表，add，remove，set 都不能用，必须用中间表

两端都可以自动获得另一端的自动API访问。跟一对多的反向访问类似。但是使用的是本字段的字段名

In [112]: Course.objects.create(name='python全栈')                                                                                                                                                                    
Out[112]: <Course: python全栈>

In [113]: Course.objects.create(name='python全套')                                                                                                                                                                    
Out[113]: <Course: python全套>

In [114]: Course.objects.create(name='English')                                                                                                                                                                       
Out[114]: <Course: English>

In [115]: c1,c2,c3= Course.objects.all()                                                                                                                                                                              

In [116]: c1                                                                                                                                                                                                          
Out[116]: <Course: python全栈>

In [117]: c2                                                                                                                                                                                                          
Out[117]: <Course: python全套>

In [118]: c3                                                                                                                                                                                                          
Out[118]: <Course: English>

In [4]: c1,c2,c3= Course.objects.all()

In [5]: s1,s2,s3,s4,s5,s6 = Student.objects.all()

In [6]: c1.students.all()   #正向，students为course的字段，也是一个管理器
Out[6]: <QuerySet [<Student: 1-心蓝-0>]>

In [7]: s1.course_set.all()  #反向查询
Out[7]: <QuerySet [<Course: python全栈>]>

#如果没有中间表下面的正向添加、删除实例可以使用，这里不做演示         #指定了中间表后set,add,remove都不能用

c1.students.add(s1,s2,s3,s4,s5,s6)

c1.students.remove(s3,s4,s5,s6)

#如果没有中间表，下面的反向添加、删除实例可以使用，不做演示了    #只要是反向，就是用模型的小写加_set

s1.course_set.add(c1,c2,c3)

s1.course_set.remove(c1,c2,c3)

#加了中间表以后的操作

e =Enroll()

e.course = c1

e.student = s1

e.save()

或者

e =Enroll()

e.course_id = c1.id

e.student_id = s1.id

e.save()

2.增加

In [13]: Enroll.objects.create(student=s3,course=c3)
Out[13]: <Enroll: Enroll object (3)>

In [14]: Enroll.objects.create(student=s6,course=c2)
Out[14]: <Enroll: Enroll object (4)>

In [15]: Enroll.objects.create(student=s7,course=c1)
Out[15]: <Enroll: Enroll object (5)>

3.自定义中间表以后的删除

In [37]: Enroll.objects.all()                                                                                                                                                                                         
Out[37]: <QuerySet [<Enroll: Enroll object (1)>, <Enroll: Enroll object (2)>, <Enroll: Enroll object (3)>, <Enroll: Enroll object (4)>, <Enroll: Enroll object (5)>, <Enroll: Enroll object (6)>]>
In [38]: Enroll.objects.filter(course) 
In [38]: Enroll.objects.filter(course=1)                                                                                                                                                                              
Out[38]: <QuerySet [<Enroll: Enroll object (1)>, <Enroll: Enroll object (2)>, <Enroll: Enroll object (5)>, <Enroll: Enroll object (6)>]>

In [39]: Enroll.objects.filter(course=2)                                                                                                                                                                              
Out[39]: <QuerySet [<Enroll: Enroll object (4)>]>

In [40]: Enroll.objects.filter(course=2).delete()                                                                                                                                                                     
Out[40]: (1, {'teacher.Enroll': 1})

 

-One-to-One

正向访问：

In [8]: sd = StudentDetail.objects.create(num='20190228',college='家里蹲',student=s1)

In [9]: sd.student 
Out[9]: <Student: 1-心蓝-0>

In [10]: sd.student.name 
Out[10]: '心蓝'

In [11]: sd.student.age 
Out[11]: 0

反向（通过模型的小写）

In [12]: s1.studentdetail                                                                                                                                                                                             
Out[12]: <StudentDetail: StudentDetail object (1)>

In [13]: s1.studentdetail.num                                                                                                                                                                                         
Out[13]: '20190228'

In [14]: s1.studentdetail.college                                                                                                                                                                                     
Out[14]: '家里蹲'

3. 跨表查询（跨模型的相关字段的字段名，并且用__链接）

例一：查询男生报名了什么课程

In [19]: res = Course.objects.filter(students__sex =1)  #正向，students为字段

In [20]: print(res.query) 
SELECT `teacher_course`.`id`, `teacher_course`.`name` FROM `teacher_course` INNER JOIN `teacher_enroll` ON (`teacher_course`.`id` = `teacher_enroll`.`course_id`) INNER JOIN `teacher_student` ON (`teacher_enroll`.`student_id` = `teacher_student`.`id`) WHERE `teacher_student`.`sex` = 1

In [21]: res 
Out[21]: <QuerySet [<Course: python全栈>]>

例二：查询所有报名python课程的学生

反向关系，使用模型的小写course__name__contains

In [24]: res = Student.objects.filter(course__name__contains='python')  #course为模型的小写

In [25]: print(res.query) 
SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`grade_id`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) INNER JOIN `teacher_course` ON (`teacher_enroll`.`course_id` = `teacher_course`.`id`) WHERE `teacher_course`.`name` LIKE BINARY %python%

In [26]: res 
Out[26]: <QuerySet [<Student: 1-心蓝-0>]>

例三：查询所有报名了python课程的33期的学员

In [28]: res = Student.objects.filter(course__name__contains= 'python',grade__num__contains='33')  #course为反向，grade为正向

In [29]: print(res.query) 
SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`grade_id`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) INNER JOIN `teacher_course` ON (`teacher_enroll`.`course_id` = `teacher_course`.`id`) INNER JOIN `teacher_grade` ON (`teacher_student`.`grade_id` = `teacher_grade`.`id`) WHERE (`teacher_course`.`name` LIKE BINARY %python% AND `teacher_grade`.`num` LIKE BINARY %33%)

In [30]: res 
Out[30]: <QuerySet []>

例四：查询缴费小于3000的学员

In [31]: res = Student.objects.filter(enroll__pay__lt=3000)

In [32]: print(res.query) 
SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`grade_id`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) WHERE `teacher_enroll`.`pay` < 3000.0

In [33]: res 
Out[33]: <QuerySet [<Student: 1-心蓝-0>]>

例五：查询报名了python课程所在的班级

In [35]: res = Grade.objects.filter(student__course__name__contains= 'python')

In [36]: print(res.query) 
SELECT `teacher_grade`.`id`, `teacher_grade`.`name`, `teacher_grade`.`num` FROM `teacher_grade` INNER JOIN `teacher_student` ON (`teacher_grade`.`id` = `teacher_student`.`grade_id`) INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) INNER JOIN `teacher_course` ON (`teacher_enroll`.`course_id` = `teacher_course`.`id`) WHERE `teacher_course`.`name` LIKE BINARY %python%

In [37]: res 
Out[37]: <QuerySet [<Grade: 40期 - 进阶>]>

value 与only的区别
value返回字典
only返回对象

In [40]: res=Student.objects.values('name')

In [41]: res 
Out[41]: <QuerySet [{'name': '心蓝'}, {'name': 'litao'}, {'name': '梦洁'}, {'name': '魏明凯'}, {'name': 'litao'}, {'name': '刘洋'}]>

In [42]: type(res[0]) 
Out[42]: dict

In [43]: res[0] 
Out[43]: {'name': '心蓝'}

In [44]: res=Student.objects.only('name')

In [45]: res 
Out[45]: <QuerySet [<Student: 1-心蓝-0>, <Student: 2-litao-0>, <Student: 3-梦洁-0>, <Student: 4-魏明凯-0>, <Student: 5-litao-0>, <Student: 6-刘洋-0>]>

In [46]: res[0].id 
Out[46]: 1

In [47]: res[0] 
Out[47]: <Student: 1-心蓝-0>

 

转载于:https://www.cnblogs.com/taoge188/p/10506760.html