On the 4th day of my participation in the November Gwen Challenge, check out the details of the event: the last Gwen Challenge 2021

The API’s handling of paging may seem simple, but it’s actually dangerous. The most common form of paging is something like this

  • Page representation:/user/? Page = 1 & size = 15 & name = lee
  • Offset notation:/user/? Offset = 100 & limit = 15 & name = lee

Using page number notation is front-end friendly, but is essentially similar to offset notation. Here we will develop a simple paging interface using Jinzhu/GORm and GIN-gonic/GIN

Return to the json results paging query URL: http://dev.mojotv.cn:3333/api/ssh-log?client_ip=&page=1&size=10&user_id=0&machine_id=0

{
    "data": [{"id": 28."created_at": "2019-09-12T14:25:54+08:00"."updated_at": "2019-09-12T14:25:54+08:00"."user_id": 26."machine_id": 1."ssh_user": "mojotv.cn"."client_ip": "10.18.60.16"."started_at": "2019-09-12T14:24:05+08:00"."status": 0."remark": ""}]."ok": true."page": 1."size": 10."total": 1
}
Copy the code

1. Define a paging struct

//PaginationQ gin handler query binding struct
type PaginationQ struct {
	Ok    bool        `json:"ok"`
	Size  uint        `form:"size" json:"size"`
	Page  uint        `form:"page" json:"page"`
	Data  interface{} `json:"data" comment:"muster be a pointer of slice gorm.Model"` // save pagination list
	Total uint        `json:"total"`
}
Copy the code
  • OkIndicates that no service query error occurs
  • SizeNumber of pages displayed, usedformTag accepts gin’s URL-query parameter
  • PageCurrent page number, usedformTag accepts gin’s URL-query parameter
  • DataPaged data content
  • TotalThe total number of pages

2. Data table Model

In this example, ssh_log(SSH command log) is used to create the MYSQL table model using GORM. The urL-query parameter of GIN is accepted using the form tag as the search criteria

type SshLog struct {
	BaseModel
	UserId    uint      `gorm:"index" json:"user_id" form:"user_id"` // Form tag binds the gin url-query parameter
	MachineId uint      `gorm:"index" json:"machine_id" form:"machine_id"` // Form tag binds the gin url-query parameter
	SshUser   string    'json:"ssh_user" comment:" SSH account "'
	ClientIp  string    `json:"client_ip" form:"client_ip"` // Form tag binds the gin url-query parameter
	StartedAt time.Time `json:"started_at" form:"started_at"`
	Status    uint      'json:"status" comment:"0- unmarked 2- Normal 4- Warning 8- Danger 16- Fatal"
	Remark    string    `json:"remark"`
	Log       string    `gorm:"type:text" json:"log"`
	Machine   Machine   `gorm:"association_autoupdate:false; association_autocreate:false" json:"machine"`
	User      User      `gorm:"association_autoupdate:false; association_autocreate:false" json:"user"`
}
Copy the code

3. Define the structure for paging query search

ssh2ws/internal/h_ssh_log.go

type SshLogQ struct {
	SshLog
	PaginationQ
	FromTime string `form:"from_time"` // Search start time
	ToTime   string `form:"to_time"`  // When the search is over
}
Copy the code

This structure is provided to the GIN handler for parameter binding. The methods used are as follows:

func SshLogAll(c *gin.Context) {
	query := &model.SshLogQ{}
	err := c.ShouldBindQuery(query) // Start binding the url-query parameter to the structure
	if handleError(c, err) {
		return
	}
	list, total, err := query.Search()  // Start mysql business search query
	if handleError(c, err) {
		return
	}
	// Returns data start paging JSON
	jsonPagination(c, list, total, &query.PaginationQ)
}
Copy the code

4. Paging and search data queries

  • 1. Create the db – query
  • 2. Search for non-empty service fields
  • 3. Use the crudAll method to obtain data

model/m_ssh_log.go

type SshLogQ struct {
	SshLog
	PaginationQ
	FromTime string `form:"from_time"`
	ToTime   string `form:"to_time"`
}

func (m SshLogQ) Search(a) (list *[]SshLog, total uint, err error) {
	list = &[]SshLog{}
	/ / create the db - query
	tx := db.Model(m.SshLog).Preload("User").Preload("Machine")
	// Search for non-empty business fields
	ifm.ClientIp ! ="" {
		tx = tx.Where("client_ip like ?"."%"+m.ClientIp+"%")}// Search time period
	ifm.FromTime ! =""&& m.ToTime ! ="" {
		tx = tx.Where("`created_at` BETWEEN ? AND ?", m.FromTime, m.ToTime)
	}
	// Use the crudAll method to get data
	total, err = crudAll(&m.PaginationQ, tx, list)
	return
}
Copy the code

CrudAll method to build SQL paging data,

  1. Setting default Parameters

  2. Get the total number of searches

  3. Get the offset data

  4. Assemble JSON paging data

model/helper.go

func crudAll(p *PaginationQ, queryTx *gorm.DB, list interface{}) (uint, error) { 
    //1. Default parameters
    if p.Size < 1 { 
        p.Size = 10 
    } 
    if p.Page < 1 { 
        p.Page = 1 
    }

    //2. Number of partial searches
    var total uint err := queryTx.Count(&total).Error iferr ! =nil {
        return 0, err 
    } 
    offset := p.Size * (p.Page - 1)

    //3. Offset data
    err = queryTx.Limit(p.Size).Offset(offset).Find(list).Error iferr ! =nil { 
        return 0, err
    } 
    
    return total, err 
}

//4. Json paging data
func jsonPagination(c *gin.Context, list interface{}, total uint, query *model.PaginationQ) { 
    c.AbortWithStatusJSON(200, gin. H {" ok ":true, "data" : list, "total" : total, "page" : query. page, "size" : query. size})}Copy the code

The API’s handling of paging may seem simple, but it’s actually dangerous. The most common form of paging is something like this

  • Page representation:/user/? Page = 1 & size = 15 & name = lee
  • Offset notation:/user/? Offset = 100 & limit = 15 & name = lee

Using page number notation is front-end friendly, but is essentially similar to offset notation. Here we will develop a simple paging interface using Jinzhu/GORm and GIN-gonic/GIN

Return to the json results paging query URL: http://dev.mojotv.cn:3333/api/ssh-log?client_ip=&page=1&size=10&user_id=0&machine_id=0

{
    "data": [{"id": 28."created_at": "2019-09-12T14:25:54+08:00"."updated_at": "2019-09-12T14:25:54+08:00"."user_id": 26."machine_id": 1."ssh_user": "mojotv.cn"."client_ip": "10.18.60.16"."started_at": "2019-09-12T14:24:05+08:00"."status": 0."remark": ""}]."ok": true."page": 1."size": 10."total": 1
}
Copy the code

5. Sample code

Complete project code address