GORM Preloading and self referencing

Preloading

Start with the traditional foreign key constraints

In traditional database design , If we want to create a master table and a slave table , You have to add foreign key constraints when creating slave table statements （ It is usually added when creating ）.

[CONSTRAINT] [ Foreign key constraint name ] FOREIGN KEY( Foreign key field name ) REFERENCES  Main table name ( Primary key field name )

For example, there is now a department table and an employee table , There is a one to many relationship between them , The Department table is the main table , The employee table is from table . When creating an employee table , We have to add foreign key constraints to it ：

#  First create the main table ：department surface 
create table department(
	id int primary key auto_increment,
	dep_name varchar(20),
	dep_location varchar(20)
);

#  Create from table  employee  And add foreign key constraints  emp_depid_fk
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int, --  The foreign key corresponds to the primary key of the primary table 
	--  Create foreign key constraints 
	constraint emp_depid_fk foreign key (dep_id) references department(id)
);

But there are some problems with this , Because the design of data sheet is usually in the initial stage of project development , We are unlikely to design the data sheet perfectly from the beginning , The relationship between data tables may be adjusted later , Adding foreign key constraints is undoubtedly to write the relationship between data tables in advance （ If no cascading operation is added ）, The coupling degree is relatively low .

ORM database

ORM The database takes this into account , We use GORM Frame example . When creating a data table , You don't have to add any constraints to the data table , Instead, it postponed the task to the creation of models When , Reduced coupling , Later, you can directly modify the code without modifying the database .

Let's take an example , Suppose you want to create three data tables now , The student form and grade sheet are many to one , There is one to many between the grade sheet and the curriculum ：

USE test01;
CREATE TABLE student(
	sno VARCHAR(20),
	NAME VARCHAR(20),
	age INT
);
CREATE TABLE grade (
	sno VARCHAR(20),
	grade INT,
	cno VARCHAR(20)
);
CREATE TABLE course (
	cno VARCHAR(20),
	cname VARCHAR(20)
);

INSERT INTO student VALUES("01", " Zhang San ", 19);
INSERT INTO student VALUES("02", " Li Si ", 20);
INSERT INTO student VALUES("02", " Wang Wu ", 21);

INSERT INTO grade VALUES("01", 90, "0001");
INSERT INTO grade VALUES("01", 80, "0002");
INSERT INTO grade VALUES("01", 30, "0003");
INSERT INTO grade VALUES("02", 80, "0001");
INSERT INTO grade VALUES("02", 50, "0002");
INSERT INTO grade VALUES("02", 60, "0003");

INSERT INTO course VALUES("0001", " mathematics ");
INSERT INTO course VALUES("0002", " Chinese language and literature ");
INSERT INTO course VALUES("0003", " English ");

We didn't specify any constraints when creating the data table , Instead, it postponed the work to the creation of models When ：

type student struct {
    
	Sno string `gorm:"primary_key"`
	Name string
	Age int
	Grades []grade `gorm:"foreignKey:Sno;association_foreignkey:Sno"`
}
func (student) TableName() string {
    
	return "student"
}

type grade struct {
    
	Sno string
	Grade int
	Cno string
}
func (grade) TableName() string {
    
	return "grade"
}

type course struct {
    
	Cno string `gorm:"primary_key"`
	Cname string
	Grades []grade `gorm:"foreignKey:Cno;association_foreignkey:Cno"`
}
func (course) TableName() string {
    
	return "course"
}

In the main table , We created a slice that maps the struct from the table , Then use the structure tag To specify the fields of foreign key constraints , Use this way to express the relationship between the master table and the slave table .

Preloading

Create good models in the future , We try to use query statements to query the information in the table , For example, query the information of Zhang San in the student table ：

var st student
db.Where("Name=?", " Zhang San ").Find(&st)
// {01  Zhang San  19 []}

We can find out , Although the student number 、 The name and age are printed out , But the information from the table is not printed , Although in general , The information from the table does not need to be printed , But we are student The structure declares Grades Field , The value of this field should be printed like other fields .

At this time, you need to use preloading ：

db.Preload("Grades").Where("Name=?", " Zhang San ").Find(&st)
// {01  Zhang San  19 [{01 90 0001} {01 80 0002} {01 30 0003}]}

Preload What is specified in is the name of the slice field in the mapping structure .

Custom preload

We can also customize preloading , For example, we just want to show Zhang San's math scores ：

db.Preload("Grades", func(db *gorm.DB) *gorm.DB {
    
		//  Native sql sentence ,  there sno and cno It's a field in the database , Not a field in the structure 
		return db.Where("sno=? and cno=?", "01", "0001")
	}).Where("Name=?", " Zhang San ").Find(&st)
// {01  Zhang San  19 [{01 90 0001}]}

Quote from

If you have an employee list now ：

CREATE TABLE employee (
	id INT,
	lid INT,
	NAME VARCHAR(20)
);
INSERT INTO employee VALUES(1, 0, " Leader ");
INSERT INTO employee VALUES(2, 1, " staff 1");
INSERT INTO employee VALUES(3, 1, " staff 2");

The three fields are employee number 、 Leader number and employee name , Of a piece of data lid It could be another piece of data id, That is, one employee may be the leader of another employee , The data in a table form a one to many relationship , This is self citation .

stay models It can be written in this way tag：

type employee struct {
    
	Id int
	Lid int
	Name string
	Employees []employee `gorm:"foreignkey:Lid;association_foreignkey:Id"`
}
func (employee) TableName() string {
    
	return "employee"
}

Then we can use preload to print the leader's information ：

var emp employee
db.Preload("Employees").Where("Name=?", " Leader ").Find(&emp)
fmt.Println(emp)
// {1 0  Leader  [{2 1  staff 1 []} {3 1  staff 2 []}]}

Reference material ：

GORM Chinese document