YouTip

The relationships between tables can be categorized into the following three types: * **One-to-One**: One person corresponds to one ID card number, with the data field set to unique. * **One-to-Many**: One family has multiple people, generally implemented through a foreign key. * **Many-to-Many**: One student has many courses, and one course has many students, generally implemented through a third table. !(#) ### Creating Models Next, let's look at multiple tables and multiple instances. ## Example class Book(models.Model): title = models.CharField(max_length=32) price = models.DecimalField(max_digits=5, decimal_places=2) pub_date = models.DateField() publish = models.ForeignKey("Publish", on_delete=models.CASCADE) authors = models.ManyToManyField("Author") class Publish(models.Model): name = models.CharField(max_length=32) city = models.CharField(max_length=64) email= models.EmailField() class Author(models.Model): name = models.CharField(max_length=32) age = models.SmallIntegerField() au_detail = models.OneToOneField("AuthorDetail", on_delete=models.CASCADE) class AuthorDetail(models.Model): gender_choices =( (0,"Female"), (1,"Male"), (2,"Confidential"), ) gender = models.SmallIntegerField(choices=gender_choices) tel = models.CharField(max_length=32) addr = models.CharField(max_length=64) birthday = models.DateField() **Explanation:** * 1. The EmailField data type is for email formats. It inherits from CharField at the bottom layer and is encapsulated, equivalent to varchar in MySQL. * 2. Django version 1.1 does not require cascade deletion: on_delete=models.CASCADE, but Django 2.2 does. * 3. Generally, cascade updates are not required. * 4. Foreign keys are set in the "many" side of a one-to-many relationship: models.ForeignKey("related class name", on_delete=models.CASCADE). * 5. OneToOneField = ForeignKey(..., unique=True) sets up a one-to-one relationship. * 6. If a model class has a foreign key, when creating data, you must first create the data for the model class associated with the foreign key. Otherwise, when creating data for the model class containing the foreign key, the data for the associated model class of the foreign key will not be found. ### Table Structure **Book Table Book**: title, price, pub_date, publish (foreign key, many-to-one), authors (many-to-many) **Publishing House Table Publish**: name, city, email **Author Table Author**: name, age, au_detail (one-to-one) **Author Details Table AuthorDetail**: gender, tel, addr, birthday Here is a description of the table relationships: [!(#)](#) ### Inserting Data We execute the following SQL insert operations in MySQL: insert into app01_publish(name,city,email) values ("Huashan Publishing", "Huashan", "hs@163.com"), ("Mingjiao Publishing", "Heimu Cliff", "mj@163.com") # First insert multiple records into the authordetail table insert into app01_authordetail(gender,tel,addr,birthday) values (1,13432335433,"Huashan","1994-5-23"), (1,13943454554,"Heimu Cliff","1961-8-13"), (0,13878934322,"Heimu Cliff","1996-5-20") # Then insert data into author, so that author can find authordetail insert into app01_author(name,age,au_detail_id) values ("Linghu Chong",25,1), ("Ren Woxing",58,2), ("Ren Yingying",23,3) !(#) * * * ## ORM - Adding Data ### One-to-Many (Foreign Key ForeignKey) **Method 1:** Passing an object. The return value's data type is an object, a book object. **Steps:** * a. Get the publishing house object. * b. Pass the publishing house object to the book's publish attribute. ## app01/views.py File Code: def add_book(request): # Get the publishing house object pub_obj = models.Publish.objects.filter(pk=1).first() # Pass the publishing house object to the book's publish attribute book = models.Book.objects.create(title="", price=200, pub_date="2010-10-10", publish=pub_obj) print(book,type(book)) return HttpResponse(book) !(#) Method 2: Passing the object's id (since the data passed is usually an id, passing the object's id is common). In a one-to-many relationship, the class that sets the foreign key attribute (the "many" table) has a field name displayed in MySQL as: **foreign_key_attribute_name_id**. The return value's data type is an object, a book object. **Steps:** * a. Get the id of the publishing house object. * b. Pass the id of the publishing house object to the book's related publish_id field. ## app01/views.py File Code: def add_book(request): # Get the publishing house object pub_obj = models.Publish.objects.filter(pk=1).first() # Get the id of the publishing house object pk = pub_obj.pk # Pass the id of the publishing house object to the book's related publish_id field book = models.Book.objects.create(title="Chongling Swordplay", price=100, pub_date="2004-04-04", publish_id=pk) print(book,type(book)) return HttpResponse(book) !(#) ### Many-to-Many (ManyToManyField): Adding Data in the Third Relationship Table **Method 1:** Passing objects. No return value. **Steps:** * a. Get the author objects. * b. Get the book object. * c. Use the add method to pass author objects to the book object's authors attribute. ## app01/views.py File Code: def add_book(request): # Get the author objects chong = models.Author.objects.filter(name="Linghu Chong").first() ying = models.Author.objects.filter(name="Ren Yingying").first() # Get the book object book = models.Book.objects.filter(title="").first() # Use the add method to pass author objects to the book object's authors attribute book.authors.add(chong, ying) return HttpResponse(book) !(#) **Method 2:** Passing object ids. No return value. **Steps:** * a. Get the ids of the author objects. * b. Get the book object. * c. Use the add method to pass the ids of the author objects to the book object's authors attribute. ## app01/views.py File Code: def add_book(request): # Get the author object chong = models.Author.objects.filter(name="Linghu Chong").first() # Get the id of the author object pk = chong.pk # Get the book object book = models.Book.objects.filter(title="Chongling Swordplay").first() # Use the add method to pass the id of the author object to the book object's authors attribute book.authors.add(pk) !(#) ### Related Manager (Object Invocation) **Prerequisites:** * Many-to-Many (both directions have a related manager). * One-to-Many (only the object of the "many" class has a related manager, i.e., only in the reverse direction). **Syntax Format:** Forward: attribute name Reverse: lowercase class name + _set **Note:** One-to-many can only be reversed. **Common Methods:** **add()**: Used for many-to-many relationships. Adds the specified model object to the related object set (relationship table). **Note:** add() in a one-to-many (i.e., foreign key) relationship can only pass objects (*QuerySet data type), not ids (*). * usage: # Method 1: Pass objects book_obj = models.Book.objects.get(id=10) author_list = models.Author.objects.filter(id__gt=2) book_obj.authors.add(*author_list)# Add author objects with id > 2 to this book's author set # Method 2: Pass object ids book_obj.authors.add(*[1,3])# Add author objects with id=1 and id=3 to this book's author set return HttpResponse("ok") !(#) Reverse: lowercase table name_set ying = models.Author.objects.filter(name="Ren Yingying").first() book = models.Book.objects.filter(title="Chongling Swordplay").first() ying.book_set.add(book) return HttpResponse("ok") !(#) **create()**: Creates a new object and simultaneously adds it to the related object set. Returns the newly created object. pub = models.Publish.objects.filter(name="Mingjiao Publishing").first() wo = models.Author.objects.filter(name="Ren Woxing").first() book = wo.book_set.create(title="Star Sucking Technique", price=300, pub_date="1999-9-19", publish=pub) print(book,type(book)) return HttpResponse("ok") !(#) **remove()**: Removes the specified model object from the related object set. For ForeignKey objects, this method only exists when null=True (can be null). No return value. ## Example author_obj =models.Author.objects.get(id=1) book_obj = models.Book.objects.get(id=11) author_obj.book_set.remove(book_obj) return HttpResponse("ok") !(#) **clear()**: Removes all objects from the related object set, deleting the association but not the objects themselves. For ForeignKey objects, this method only exists when null=True (can be null). No return value. # Clear all authors associated with Dugu Nine Swords book = models.Book.objects.filter(title="").first() book.authors.clear() * * * ## ORM Queries Cross-table queries based on objects. Forward: attribute name Reverse: lowercase class name_set ### One-to-Many Query the city of the publishing house for the book with primary key 1 (forward). ## Example book = models.Book.objects.filter(pk=10).first() res = book.publish.city print(res,type(res)) return HttpResponse("ok") !(#) Query the names of books published by Mingjiao Publishing (reverse). Reverse: object.lowercase_class_name_set (pub.book_set) can jump to the related table (book table). **pub.book_set.all()**: Retrieves all book objects from the book table in a QuerySet, iterating through to get each book object. ## Example pub = models.Publish.objects.filter(name="Mingjiao Publishing").first() res = pub.book_set.all() for i in res: print(i.title) return HttpResponse("ok") !(#) ### One-to-One Query Linghu Chong's phone number (forward). Forward: object.attribute (author.au_detail) can jump to the related table (author details table). ## Example author = models.Author.objects.filter(name="Linghu Chong").first() res = author.au_detail.tel print(res,type(res)) return HttpResponse("ok") !(#) Query the names of all authors whose address is Heimu Cliff (reverse). For one-to-one reverse, use **object.lowercase_class_name** directly, without adding _set. Reverse: object.lowercase_class_name (addr.author) can jump to the related table (author table). ## Example addr = models.AuthorDetail.objects.filter(addr="Heimu Cliff").first() res = addr.author.name print(res,type(res)) return HttpResponse("ok") !(#) ### Many-to-Many Query the names and phone numbers of all authors of "" (forward). Forward: **object.attribute (book.authors)** can jump to the related table (author table). The author table does not have author phone numbers, so again use **object.attribute (i.au_detail)** to jump to the related table (author details table). ## Example book = models.Book.objects.filter(title="").first() res = book.authors.all() for i in res: print(i.name, i.au_detail.tel) return HttpResponse("ok") !(#) Query the names of all books written by Ren Woxing (reverse). ## Example author = models.Author.objects.filter(name="Ren Woxing").first() res = author.book_set.all() for i in res: print(i.title) return HttpResponse("ok") !(#) * * * ## Cross-Table Queries Based on Double Underscores ### Forward: attribute_name__cross_table_attribute_name Reverse: lowercase_class_name__cross_table_attribute_name ### One-to-Many Query all book names and prices published by Publishing. ## Example res = models.Book.objects.filter(publish__name=" Publishing").values_list("title","price") !(#) Reverse: Retrieve data across tables via **lowercase_class_name__cross_table_attribute_name (book__title, book__price)**. ## Example res = models.Publish.objects.filter(name=" Publishing").values_list("book__title","book__price") return HttpResponse("ok") !(#) ### Many-to-Many Query the names of all books written by Ren Woxing. Forward: Retrieve data across tables via attribute_name__cross_table_attribute_name (authors__name): res = models.Book.objects.filter(authors__name="Ren Woxing").values_list("title") Reverse: Retrieve data across tables via lowercase_class_name__cross_table_attribute_name (book__title): res = models.Author.objects.filter(name="Ren Woxing").values_list("book__title") !(#) ### One-to-One Query Ren Woxing's phone number. Forward: Retrieve data across tables via **attribute_name__cross_table_attribute_name (au_detail__tel)**. res = models.Author.objects.filter(name="Ren Woxing").values_list("au_detail__tel") !(#) Reverse: Retrieve data across tables via **lowercase_class_name__cross_table_attribute_name (author__name)**. res = models.AuthorDetail.objects.filter(author__name="Ren Woxing").values_list("tel") !(#)