Foreign keys and table relationships

Foreign key:

In MySQL, there are two types of table engines: InnoDB and myisam. If the InnoDB engine is used, foreign key constraints are supported. The existence of foreign keys makes the ORM framework extremely powerful when dealing with table relationships. So let's first introduce the use of foreign keys in Django.

Class is defined as class ForeignKey(to,on_delete,**options). The first parameter refers to which model is referenced. The second parameter refers to how to handle this field when the model data referenced by foreign keys is deleted, such as CASCADE and SET_NULL, etc. Here is a practical case to illustrate. For example, there are two models: a User and an Article. A User can publish multiple articles. An Article can only have one Author and can be referenced through foreign keys. The relevant example code is as follows:

class User(models.Model):
    username = models.CharField(max_length=20)
    password = models.CharField(max_length=100)


class Article(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()

    author = models.ForeignKey("User",on_delete=models.CASCADE)

 

ForeignKey is used to define the relationship between models. That is, in the instance of article, the corresponding User model can be operated through the author attribute. This is very convenient to use. The example code is as follows:

article = Article(title='abc',content='123')
author = User(username='Zhang San',password='111111')
article.author = author
article.save()

# Modify the value on article.author
article.author.username = 'Li Si'
article.save()

 

Why can you access the corresponding user object through the author after using ForeignKey. Therefore, at the bottom, Django adds an attribute name to the Article table_ ID field (for example, the field name of author is author_id). This field is a foreign key that records the primary key of the corresponding author. In the future, when you access through article.author, you actually use author first_ ID to find the corresponding data, and then extract the data in the user table to form a model.

If you want to reference the model of another app, you should use app. Model when passing the to parameter_ Name. Taking the above example as an example, if the User and Article are not in the same app, the example code when referencing is as follows

# The user model is in the user app
class User(models.Model):
    username = models.CharField(max_length=20)
    password = models.CharField(max_length=100)

# The article model is in the article app
class Article(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()

    author = models.ForeignKey("user.User",on_delete=models.CASCADE)

 

If the foreign key of the model refers to its own model, the to parameter can be 'self' or the name of the model. In forum development, general comments can be secondary comments, that is, comments can be made on another comment, so you need to use foreign keys to reference yourself when defining the model. The example code is as follows:

class Comment(models.Model):
    content = models.TextField()
    origin_comment = models.ForeignKey('self',on_delete=models.CASCADE,null=True)
    # perhaps
    # origin_comment = models.ForeignKey('Comment',on_delete=models.CASCADE,null=True)

 

Foreign key deletion:

If a model uses foreign keys. What kind of operation should be carried out after the other party's model is deleted. You can use on_delete to specify. The types that can be specified are as follows:

1. CASCADE: CASCADE operation. If the data corresponding to the foreign key is deleted, the data will also be deleted.
2. PROTECT: protected. That is, as long as this data refers to the data of the foreign key, the data of the foreign key cannot be deleted.
3. SET_NULL: set to null. If the data of the foreign key is deleted, set this field to null on this data. If this option is set, the premise is to specify that this field can be empty.
4. SET_DEFAULT: sets the default value. If the data of the foreign key is deleted, this field will be set as the default value on this data. If you set this option, you must specify a default value for this field.
5. SET(): if the data of the foreign key is deleted. Then the value in the SET function will be obtained as the value of the foreign key. The SET function can receive an object that can be called (such as a function or method). If it is an object that can be called, the result of the object call will be returned as a value.
6. DO_ Noting: do not take any action. Everything depends on database level constraints.

The above options are only at the Django level, and the data level is still RESTRICT!

 

Table relationship:

The relationships between tables are associated through foreign keys. The relationship between tables is nothing more than three relationships: one to one, one to many (many to one), many to many, etc. The following will discuss the application scenarios and implementation methods of the three relationships.

One to many:

1. Application scenario: for example, the relationship between the article and the author. An article can only be written by one author, but one author can write more than one article. The relationship between the article and the author is a typical many to one relationship.

2. Implementation method: one to many or many to one, which are implemented through ForeignKey. It is still explained by the article and the author's case.

 class User(models.Model):
     username = models.CharField(max_length=20)
     password = models.CharField(max_length=100)

 class Article(models.Model):
     title = models.CharField(max_length=100)
     content = models.TextField()
     author = models.ForeignKey("User",on_delete=models.CASCADE)

In the future, you can use the following code to specify the author for the Article object:

 

article = Article(title='abc',content='123')
author = User(username='zhiliao',password='111111')
# To save to the database first
author.save()
article.author = author
article.save()

And if you want to get all the articles under a user in the future, you can use article_set. The example code is as follows:

user = User.objects.first()
# Get all articles written by the first user
articles = user.article_set.all()
for article in articles:
    print(article)

 

one-on-one:

1. Application scenario: for example, a User table and a User information table. In the actual website, you may need to save a lot of User information, but some information is not often used. If all information is stored in one table, the query efficiency may be affected. Therefore, some infrequent information of users can be stored in another table, which is called UserExtension. However, the User table User and the User information table UserExtension are typical one-to-one.

2. Implementation method: Django provides a special Field called OneToOneField for one-to-one operation. The example code is as follows:

 class User(models.Model):
     username = models.CharField(max_length=20)
     password = models.CharField(max_length=100)

 class UserExtension(models.Model):  
     birthday = models.DateTimeField(null=True)  
     school = models.CharField(blank=True,max_length=50)  
     user = models.OneToOneField("User", on_delete=models.CASCADE)

A one-to-one relationship mapping is added to the user extension model. In fact, the bottom layer adds a user to the UserExtension table_ ID to associate with the user table, and the foreign key data must be unique in the table to ensure one-to-one.

 

 

Many to many:

1. Application scenario: for example, the relationship between articles and labels. An article can have multiple tags, and a tag can be referenced by multiple articles. Therefore, the relationship between tags and articles is a typical many to many relationship.

2. Implementation method: Django provides a special Field for this many to many implementation. It's called ManyToManyField. Take articles and labels as examples. The example code is as follows:

 class Article(models.Model):
     title = models.CharField(max_length=100)
     content = models.TextField()
     tags = models.ManyToManyField("Tag",related_name="articles")

 class Tag(models.Model):
     name = models.CharField(max_length=50)

At the database level, Django actually creates an intermediate table for this many to many relationship. This intermediate table defines two foreign keys respectively, which are referenced to the primary keys of the article and tag tables.

 

related_name and related_query_name:

related_name:

Take user and article as examples. If an article wants to access the corresponding author, it can be accessed through the author. But if you have a user object and want to get all the articles through this user object, what should you do? At this time, you can use user.article_set. The rule of this name is that the model name is lowercase_ set. The example code is as follows:

user = User.objects.get(name='Zhang San')
user.article_set.all()

If you don't want to use lowercase model names_ If you want to use other names, you can specify related when defining the model_ name. The example code is as follows:

class Article(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()
    # Pass related_name parameter, which can be accessed later using articles when referring to the direction
    author = models.ForeignKey("User",on_delete=models.SET_NULL,null=True,related_name='articles')

Later, when referring to the direction. Using articles, you can access the author's article model. The example code is as follows:

user = User.objects.get(name='Zhang San')
user.articles.all()

If you do not want to use backreferences, you can specify related_name='+'. The example code is as follows:

class Article(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()
    # Pass related_name parameter, which can be accessed later using articles when referring to the direction
    author = models.ForeignKey("User",on_delete=models.SET_NULL,null=True,related_name='+')

You will not be able to pass through user.article_set to access the article model.

related_query_name:

When searching for data, you can use filter to filter. When filtering with filter, you can specify not only what conditions a certain attribute on the model should meet, but also what attributes the associated model should meet. For example, if you want to get all users who have written the title abc, you can write this:

users = User.objects.filter(article__title='abc')

If you set related_ The name is articles because the inverted filter name will use related_name, the code of the above example will be changed as follows:

users = User.objects.filter(articles__title='abc')

You can use related_query_name changes the inverted name of the query to another name. Like article. The example code is as follows:

class Article(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()
    # Pass related_name parameter, which can be accessed later using articles when referring to the direction
    author = models.ForeignKey("User",on_delete=models.SET_NULL,null=True,related_name='articles',related_query_name='article')

Then you can use the following code when performing reverse filtering search:

users = User.objects.filter(article__title='abc')

 

 

Specify foreign key fields:

class Game(models.Model):
    GAME_AREAS = (

    )
    GAME_VALIDS = (
        (0, 'invalid'),
        (1, 'Effective'),
    )
    appid = models.IntegerField(primary_key=True, unique=True, verbose_name="Game logo")
    name = models.CharField(max_length=32, verbose_name="Game name")
    area = models.CharField(max_length=32, choices=GAME_AREAS, verbose_name="region")
    is_valid = models.SmallIntegerField(choices=GAME_VALIDS, verbose_name="Is it valid")

    def __str__(self):
        return self.name

    class Meta:
        db_table = "common_game"
        verbose_name = verbose_name_plural = "Game table"


class GameService(models.Model):
    appid = models.ForeignKey(Game, db_column='appid', null=True, on_delete=models.SET_NULL, verbose_name='game')  # Specify db_column field