Association Query

When querying data involving multiple tables, using joins can be inefficient for large datasets. In GoFrame, model association queries are provided to simplify such operations.

Taking dept, emp, and hobby tables as examples: each department can have multiple employees, each employee belongs to one department, and each employee has one hobby.

Query all employees and associate their departments

• Modify entity.Emp, include association information

internal/model/entity/emp.go

type Emp struct {
    Id     uint   `json:"id"`      // ID
    DeptId uint   `json:"dept_id"` // Department ID
    Name   string `json:"name"`    // Name
    Gender int    `json:"gender"`  // Gender: 0=male 1=female
    Phone  string `json:"phone"`   // Contact phone
    Email  string `json:"email"`   // Email
    Avatar string `json:"avatar"`  // Photo

    Dept *Dept `orm:"with:id=dept_id" json:"dept"`
}

• Use With to specify the associated model query

var emps []*entity.Emp
err = dao.Emp.Ctx(ctx).With(entity.Dept{}).Scan(&emps)

Without specifying With, the Dept in the query results will be nil.

Query all employees and associate departments and hobbies

• Modify entity.Emp, include association information

internal/model/entity/emp.go

type Emp struct {
    Id     uint   `json:"id"`      // ID
    DeptId uint   `json:"dept_id"` // Department ID
    Name   string `json:"name"`    // Name
    Gender int    `json:"gender"`  // Gender: 0=male 1=female
    Phone  string `json:"phone"`   // Contact phone
    Email  string `json:"email"`   // Email
    Avatar string `json:"avatar"`  // Photo

    Dept  *Dept  `orm:"with:id=dept_id" json:"dept"`
    Hobby *Hobby `orm:"with:emp_id=id" json:"hobby"`
}

• Use With to specify the required associations

var emps []*entity.Emp
err = dao.Emp.Ctx(ctx).With(entity.Dept{}, entity.Hobby{}).Where("dept_id", 101).Scan(&emps)

Alternatively, use WithAll to associate all.

var emps []*entity.Emp
err = dao.Emp.Ctx(ctx).WithAll().Where("dept_id", 101).Scan(&emps)

Query departments and associate each department's employees

• Modify entity.Dept, include association information

internal/model/entity/dept.go

// Dept is the golang structure for table dept.
type Dept struct {
    Id     uint   `json:"id"`     // ID
    Pid    uint   `json:"pid"`    // Parent department ID
    Name   string `json:"name"`   // Department name
    Leader string `json:"leader"` // Department leader
    Phone  string `json:"phone"`  // Contact phone

    Emps []*Emp `orm:"with:dept_id=id" json:"emps"`
}

• Query

var depts []*entity.Dept
err = dao.Dept.Ctx(ctx).With(entity.Emp{}).Scan(&depts)

When performing association queries, modifications are made directly in the entity classes. However, these classes are usually generated by tools and should not be modified under normal circumstances. Therefore, when conducting association queries, it's advisable to define custom structs, retaining only the necessary fields (the fields used for association must exist).

type MyDept struct {
    g.Meta `orm:"table:dept"`
    Id     uint   `json:"id"`     // ID
    Name   string `json:"name"`   // Department name
    Leader string `json:"leader"` // Department leader
    Phone  string `json:"phone"`  // Contact phone
}

type MyEmp struct {
    g.Meta `orm:"table:emp"`
    Id     uint   `json:"id"`      // ID
    DeptId uint   `json:"dept_id"` // Department ID
    Name   string `json:"name"`    // Name
    Phone  string `json:"phone"`   // Contact phone

    Dept *MyDept `orm:"with:id=dept_id" json:"dept"`
}

var emps []*MyEmp
err = dao.Emp.Ctx(ctx).With(MyDept{}).Scan(&emps)

When defining custom structs, use g.Meta and orm tags to specify the corresponding data table.

Here's the translated markdown into English:

```markdown
# Association Query

When querying data involving multiple tables, joins can be inefficient for large datasets. GoFrame provides model association queries to simplify complex multi-table joins.

Using `dept`, `emp`, and `hobby` tables as an example: each department can have multiple employees, each employee belongs to exactly one department, and each employee has one hobby.

**Query all employees and associate their departments**

- Modify `entity.Emp`, include association information

`internal/model/entity/emp.go`

```go
type Emp struct {
    Id     uint   `json:"id"`      // ID
    DeptId uint   `json:"dept_id"` // Department ID
    Name   string `json:"name"`    // Name
    Gender int    `json:"gender"`  // Gender: 0=male 1=female
    Phone  string `json:"phone"`   // Contact phone
    Email  string `json:"email"`   // Email
    Avatar string `json:"avatar"`  // Photo

    Dept *Dept `orm:"with:id=dept_id" json:"dept"`
}

• Use With to specify the associated model query

var emps []*entity.Emp
err = dao.Emp.Ctx(ctx).With(entity.Dept{}).Scan(&emps)

If With is not specified, the Dept field in the query results will be nil.

Query all employees and associate departments and hobbies

• Modify entity.Emp, include association information

internal/model/entity/emp.go

type Emp struct {
    Id     uint   `json:"id"`      // ID
    DeptId uint   `json:"dept_id"` // Department ID
    Name   string `json:"name"`    // Name
    Gender int    `json:"gender"`  // Gender: 0=male 1=female
    Phone  string `json:"phone"`   // Contact phone
    Email  string `json:"email"`   // Email
    Avatar string `json:"avatar"`  // Photo

    Dept  *Dept  `orm:"with:id=dept_id" json:"dept"`
    Hobby *Hobby `orm:"with:emp_id=id" json:"hobby"`
}

• Use With to specify the required associations

var emps []*entity.Emp
err = dao.Emp.Ctx(ctx).With(entity.Dept{}, entity.Hobby{}).Where("dept_id", 101).Scan(&emps)

Alternatively, use WithAll to associate all.

var emps []*entity.Emp
err = dao.Emp.Ctx(ctx).WithAll().Where("dept_id", 101).Scan(&emps)

Query departments and associate each department's employees

• Modify entity.Dept, include association information

internal/model/entity/dept.go

// Dept is the golang structure for table dept.
type Dept struct {
    Id     uint   `json:"id"`     // ID
    Pid    uint   `json:"pid"`    // Parent department ID
    Name   string `json:"name"`   // Department name
    Leader string `json:"leader"` // Department leader
    Phone  string `json:"phone"`  // Contact phone

    Emps []*Emp `orm:"with:dept_id=id" json:"emps"`
}

• Query

var depts []*entity.Dept
err = dao.Dept.Ctx(ctx).With(entity.Emp{}).Scan(&depts)

When performing association queries, modifications are made directly in the entity classes. However, these classes are usually generated by tools and should not be modified under normal circumstances. Therefore, when conducting association queries, it's advisable to define custom structs, retaining only the necessary fields (the fields used for association must exist).

type MyDept struct {
    g.Meta `orm:"table:dept"`
    Id     uint   `json:"id"`     // ID
    Name   string `json:"name"`   // Department name
    Leader string `json:"leader"` // Department leader
    Phone  string `json:"phone"`  // Contact phone
}

type MyEmp struct {
    g.Meta `orm:"table:emp"`
    Id     uint   `json:"id"`      // ID
    DeptId uint   `json:"dept_id"` // Department ID
    Name   string `json:"name"`    // Name
    Phone  string `json:"phone"`   // Contact phone

    Dept *MyDept `orm:"with:id=dept_id" json:"dept"`
}

var emps []*MyEmp
err = dao.Emp.Ctx(ctx).With(MyDept{}).Scan(&emps)

When defining custom structs, use g.Meta and orm tags to specify the corresponding data table.