preface

Invalid association [] : invalid association [] : invalid association [] : invalid association [] : invalid association [] : invalid association [

The Related function

noun

  1. Primary and slave tables: The ones with foreign keys are called slave tables
  2. Associated foreign key: a unique field in a primary table that is referred to by a foreign key (usually a primary key, but also an index field)

How to query foreign key in SQL

A foreign key field is an actual field that exists in the database, just like a normal field, except that the foreign key has modification restrictions. When writing an SQL statement, as in normal conditional query, fill in the foreign key value of the primary table to query the primary key value, or vice versa, the SQL query does not make a difference to the foreign key. So gorM framework, as long as you understand how it identifies the master and slave tables and foreign keys can be thought of.

Function to implement skimming

Eventually all related functions will be called:

func (scope *Scope) related(value interface{}, foreignKeys ...string) *Scope {
	toScope := scope.db.NewScope(value)
	tx := scope.db.Set("gorm:association:source", scope.Value)

	for _, foreignKey := range append(foreignKeys, toScope.typeName()+"Id", scope.typeName()+"Id") {
		fromField, _ := scope.FieldByName(foreignKey)
		toField, _ := toScope.FieldByName(foreignKey)

		iffromField ! =nil {
			ifrelationship := fromField.Relationship; relationship ! =nil {
				if relationship.Kind == "many_to_many" {
					joinTableHandler := relationship.JoinTableHandler
					scope.Err(joinTableHandler.JoinWith(joinTableHandler, tx, scope.Value).Find(value).Error)
				} else if relationship.Kind == "belongs_to" {
					for idx, foreignKey := range relationship.ForeignDBNames {
						if field, ok := scope.FieldByName(foreignKey); ok {
							tx = tx.Where(fmt.Sprintf("%v = ?", scope.Quote(relationship.AssociationForeignDBNames[idx])), field.Field.Interface())
						}
					}
					scope.Err(tx.Find(value).Error)
				} else if relationship.Kind == "has_many" || relationship.Kind == "has_one" {
					for idx, foreignKey := range relationship.ForeignDBNames {
						if field, ok := scope.FieldByName(relationship.AssociationForeignDBNames[idx]); ok {
							tx = tx.Where(fmt.Sprintf("%v = ?", scope.Quote(foreignKey)), field.Field.Interface())
						}
					}

					ifrelationship.PolymorphicType ! ="" {
						tx = tx.Where(fmt.Sprintf("%v = ?", scope.Quote(relationship.PolymorphicDBName)), relationship.PolymorphicValue)
					}
					scope.Err(tx.Find(value).Error)
				}
			} else {
				sql := fmt.Sprintf("%v = ?", scope.Quote(toScope.PrimaryKey()))
				scope.Err(tx.Where(sql, fromField.Field.Interface()).Find(value).Error)
			}
			return scope
		} else iftoField ! =nil {
			sql := fmt.Sprintf("%v = ?", scope.Quote(toField.DBName))
			scope.Err(tx.Where(sql, scope.PrimaryKeyValue()).Find(value).Error)
			return scope
		}
	}

	scope.Err(fmt.Errorf("invalid association %v", foreignKeys))
	return scope
}
Copy the code

For example, db.model (&user).related (&profile), the logic is as follows:

  1. Read the structure name from scope and toStope and concatenate itID(i.eUserIDandProfileID), and the parameter foreignKeys to form a new array for traversal
  2. Check whether the above fields exist in the two structures, if so, as a foreign key, query by case
  3. fromField.RelationshipThis is usually the case when the field type is a structureThere are
  4. – Nested structures are not automatically queried (😂)

Related(…) without structure field The query

To highlight a foreign key, assume the following structure:

type User struct {
	ID int
	Name string
}
type Profile struct {
	ID int
	Name      string
	UserDi int // Note that this is Di, not ID, to show how to correspond to a custom foreign key name
}
Copy the code

Profile: db.model (&profile,”UserDi”).profile (&profile,”UserDi”).profile (&profile,”UserDi”)

  1. Substitute in the Related source code, we know that our foreign key is not in the form of structure type name +ID, so we need to specify it manually otherwise it will not guess.
  2. Substitute in the source code, you know that it will then be checkedUserorProfilehaveUserDiField to make a distinction. So gorM guesses correctly and generates the correct SQL statement in both cases
  3. If,UserThere are also normal data fields inUserDi, which will result in a query error. Gorm determines who owns the foreign key because there is a sequential step in the code logic above (the former)UsertheUserDiIs the foreign key. Pay attention to thispit
  4. More complex cases, such as those where the foreign key points to an associated foreign key that is not a primary key, or many to many, a special case that requires an intermediate table, cannot be queried by this structure. Because at this point they haven’t touched on the structure tag, non-structure field of GORMfromField.RelationshipAre allnil, does not correspond to these complex cases

Related(…) with structure fields The query

belongs to

type User struct {
	ID int
	Name string
}
type Profile struct {
	ID int
	Name      string
	UserDi int // Note that this is Di, not ID, to show how to correspond to a custom foreign key name
	User *User `gorm:"foreignkey:UserDi"` // The pointer does not affect the query.
}
Copy the code

User = user; Db.model (&profile).related (&user,” user “) belongs_to(profile belongs to user) When I tested the official website sample db.model (&user).related (&profile), there was a problem:

  1. The official example does not specify a foreign key field parameterRelated(…) without structure field The query, GORM will detect the presence of either structureStructure type name +IDObviously it’s going to go wrong
  2. The foreign key is actually UserDi and exists in the Profile, but the foreign key information is described in the User field tag of the Profile. In this case, we need to manually specify the foreign key parameter as"User"(If you do not specify it, you will need to query the set with no structure above. The structure tag is not used.)
  3. Pay attention to the query direction. Query profile from user;db.Model(&user).Related(&profile,"User")No, this will go relatedtoField ! = nilLogic, degenerates intoRelated(…) without structure field The queryThere is no data available
  4. If,UserThere are also normal data fields inUserDi, which will result in a query error. Because we identified the foreign key as “UserDi” in the tag, gorm looks at which structure belongs to this field and decides whether it is belongs to or has one/many

has one/many

Has One is not different from Has many. Change the final query object to a struct slice (array)

type User struct {
	ID int
	Name string
	Profile *Profile `gorm:"foreignkey:UserDi"` // The pointer does not affect the query.
}
type Profile struct {
	ID int
	Name      string
	UserDi int // Note that this is Di, not ID, to show how to correspond to a custom foreign key name
}
Copy the code

Db.model (&profile,” profile “).Related(&profile,” profile”

How to distinguish between belongs to and has one/many

Look at the starting point, for example, User corresponds to the primary table, Profile pairs should be from the table, and foreign keys are always in the Profile

Related(…) without structure field Query: without this distinction, it makes no sense for him to check according to who no problem

Related(…) with structure fields Query: given the profile(from table), query the user to which it belongs. User has one/many

Preload query

Preload is used to populate the structure and its nested structure fields in one step

type User struct {
	ID int
	Name string
	Profile *Profile `gorm:"foreignkey:UserDi"` // The pointer does not affect the query.
}
type Profile struct {
	ID int
	Name      string
	UserDi int // Note that this is Di, not ID, to show how to correspond to a custom foreign key name
}
Copy the code

Select * from user where ID=1;

db.Find(&user,1)
db.Model(&user).Related(&profile,"Profile")
user.Profile = &profile
Copy the code

By default, GORM only queries a single table, that is, only populates a single structure. If you want to populate nested structures at the same time, use the Preload method:

db.Preload("Profile").Find(&user,1)
Copy the code

Used when the structure is nested in multiple layers. Field segmentation, detailed reference to the official website example will not repeat

Note: Make sure the foreign key logic is correct. In addition, it does not loop (that is, if User has a Profile field and Profile has a User field), whereas global db.set (” GORm :auto_preload”, true) causes a circular query. And preloading does not call the Related function

Association query

The Association function returns *Association, which is used to easily modify the Association

db.Find(&user,1)
db.Model(&user).Association("Profile").Clear()
Copy the code

UPDATE ‘profiles’ SET’ user_di ‘= NULL WHERE (‘ user_di’ = 1)

More examples and API references are not repeated

Note: Make sure the foreign key logic is correct and the Related function is called

thought

Unlike e-R graph design databases, ORM focuses more on the “own, belong” relationship, because this can actually affect the composition of SQL at query time. Traditional 1-to-1, 1-to-many, many-to-1, many-to-many ideas do not fully apply.

Judging from the GORM source code, there are actually only three major differences:

  1. 1 to 1, a Profile belongs to a User. You query the corresponding User according to the Profile. In many-to-one mode, multiple profiles belong to the same User. However, the User corresponding to a Profile is still queried. Their SQL writing is not so different that they are grouped together
  2. Has One /many: 1 to 1, a User has a Profile. That is, a Profile can be queried based on the User. If a User has multiple profiles in multiple pairs, profiles are still queried based on the User. In GORM, the difference is whether the Profile object passed is in the form of a slice (array). SQL writing does not differ much, so it is grouped together
  3. Many to many: a combination of the above