Many-to-many self-referential relationships in Flask database

The many-to-many relationship described in the previous article is that the two model is a many-to-many relationship, the association table joins two explicit entities , and in some cases there is only one model that has many-to-many relationships with itself. such as the concerns between users. indicates that the user is concerned about other users, only one entity, no
A second entity. If both sides of the relationship are in the same table, this relationship is called a self-referential relationship .
In the focus, the left side of the relationship is the user entity, which can be referred to as the "followers"; the right side of the relationship is also the user entity, but these are "followers". From the overview
In reading, self-referential relationships are no different from normal relationships, but they are difficult to understand. is a database diagram of a self-referential relationship that represents a concern among users.

The association table for this example is follows, where each row indicates that one user is following another user. A one-to-many relationship on the left side of the graph links the user to a set of records in the follows table, and the user is a concern. The one-to-many relationship represented on the right side of the graph links the user to a set of records in the follows table, and the user is the person who is being followed.

<span style= "FONT-SIZE:18PX;" >app/models/user.py: The implementation of class follow (DB), which focuses on relational tables. Model):    __tablename__ = ' follows '    follower_id = db. Column (db. Integer, Db. ForeignKey (' users.id '), primary_key=true)  followed_id = db. Column (db. Integer, Db. ForeignKey (' users.id '), primary_key=true)  timestamp = db. Column (db. DateTime, Default=datetime.utcnow) </span>

SQLAlchemy cannot use this association table directly, because the program does not have access to the custom fields. Instead, split the left and right sides of this many-to-many relationship into two basic one-to-many relationships, and define a standard relationship. The code is shown below.

<span style= "FONT-SIZE:18PX;" >app/models/user.py: A Many-to-many relationship implemented using two one-to-many relationships </span><p><span style= "FONT-SIZE:18PX;" ></span></p>class User (usermixin, DB. Model): # followed = Db.relationship (' Follow ', foreign_keys=[follow.follower_id],                                      backref=db.backref (' Follower ', lazy= ' joined '),                                              lazy= ' dynamic ',                                              cascade= ' all, Delete-orphan ') followers = db.relationship (' Follow ', foreign_keys=[follow.followed_id],                                       backref=db.backref (' followed ', lazy= ' joined '),                                               lazy= ' Dynamic ',                                               cascade= ' all, Delete-orphan ')

In this code, the followed and followers relationships are defined as separate one-to-many relationships. Note that in order to eliminate ambiguity between foreign keys, the specified foreign key must be foreign_keys with an optional parameter when defining the relationship. Also, the DB.BACKREF () parameter does not specify a reference relationship between the two relationships, but rather a back-follow model. The lazy parameter in the callback is specified as joined. This lazy mode enables immediate loading of related pairs from a junction query.
Like. For example, if a user is following 100 users, calling User.followed.all () returns a list that contains 100 follow instances, each of which points to the appropriate user for the follower and followed callback properties. Set to lazy= ' joined ' mode to complete these operations in a single database query. If lazy is set to the default select, the corresponding user is loaded the first time the follower and followed properties are accessed.

And each attribute requires a separate query, which means that you need to add 100 additional database queries when you get all the users you are interested in.

In these two relationships, the lazy parameter set on the User side does not work the same. The lazy parameter is set on the "one" side, and the result is the record in the "many" side. The code above uses dynamic, so the relationship property does not return the record directly, but instead returns the query object, so you can add additional filters before executing the query.

The cascade parameter configures the effect that actions performed on the parent object have on related objects. For example, the CASCADE option can be set to add the user to a database session and automatically add all the relational objects to the conversation. The default value for cascading options can meet the needs of most situations, but not for this many-to-many relationship. When you delete an object, the default cascading behavior is to set the foreign key of all related objects that the object joins to a null value. However, in the associated table, the correct behavior after deleting a record should be to delete the entity that points to the record as it effectively destroys the join. This is the effect of the Cascade option value Delete-orphan.

The value of the Cascade parameter is a comma-delimited set of cascading options, which may seem confusing, but all represents all the cascading options except Delete-orphan. Set to All,delete-orphan means to enable all default cascading options, and also to delete orphan Records.

Many-to-many self-referential relationships in Flask database