Go的复选框查询

最近在业务上遇到了需要进行复选框查询的操作，公司原本的查询是简单的单一查询，所以需要将其改写为既能接收单选又能接受多选的功能模块。

下面的业务场景都是用伪代码进行演示，实际需要与自身的系统结合修改才能使用!

修改前：

请求路径

{{base_url}}/sys/leads?size=20&page=1&from=2021-01-11&to=2021-01-16&rating=中&category=债权债务——不能接受同一参数的不同值

请求参数的获取

beginDate := c.Query("from")
endDate := c.Query("to")
category := c.Query("category")

SQL——参数映射

err := repo.SelectAndCountByOwnerV2(
   "",
   beginTime, endTime,  //开始和结束的时间
   category,            //查询条件
   pagination.GetPage(c), pagination.GetPageSize(c),
   &leadList,  //自己定义的结构体——供特定的方法使用
)

SQL——orm模块

只能够接受单选条件查询的&集

func (r *LeadRepo) SelectAndCountByOwnerV2(ownerID string,
	startDate time.Time, endDate time.Time,
	category string,
	pageNum int, pageSize int, models *[]LeadList) error {

	var params []interface{}
	queryStr := `SELECT DISTINCT leads.*, 
		count(*) OVER() AS total,
		u.nickname AS staff_nickname,
		u.phone AS staff_phone,
		u.id AS staff_id,
		u.username AS staff_username,
		c.name AS company_name
		FROM leads
		FULL OUTER JOIN users AS u ON u.id = uuid(leads.assignment) 
		FULL OUTER JOIN companies AS c ON c.id = uuid(leads.owner_id)
		`
	if ownerID == "" {
		queryStr = queryStr + `where leads.owner_id is not null `
	} else {
		queryStr = queryStr + `Where leads.owner_id = ? `
		params = append(params, ownerID)
	}

	if !startDate.IsZero() && !endDate.IsZero() {
		queryStr = queryStr + "AND leads.created_at > ? AND leads.created_at <= ? "
		params = append(params, startDate, endDate)
	}
	if len(category) > 0 {
		queryStr = queryStr + "AND category = ? "
		params = append(params, category)
	}

	queryStr = queryStr + "ORDER BY leads.created_at DESC "

	queryStr = queryStr + "OFFSET ? "
	params = append(params, pageNum*pageSize)
	queryStr = queryStr + "LIMIT ? "
	params = append(params, pageSize)

	_, err := database.Db.Query(models, queryStr, params...)
	return err

}

修改后：

请求路径

{{base_url}}/sys/leads?size=20&page=1&from=2021-01-11&to=2021-01-16&rating=中&category=债权债务&category=劳动纠纷——能够接受某一参数的多个参数值

请求参数的获取

使用了 QueryArray 方法——能够将相同参数名的元素封装成一个数组，便于我们orm模块对其进行遍历拼接操作

beginDate := c.Query("from")
endDate := c.Query("to")
category := c.QueryArray("category")

SQL——参数映射

err := repo.SelectAndCountByOwnerV2(
   "",
   beginTime, endTime,  //开始和结束的时间
   category,            //查询条件
   pagination.GetPage(c), pagination.GetPageSize(c),
   &leadList,  //自己定义的结构体——供特定的方法使用
)

SQL——orm模块

传入的参数类型由 string ——>[]string ，变成了 string 数组类型，之后我们再对其进行遍历拼接操作

func (r *LeadRepo) SelectAndCountByOwnerV2(ownerID string,
	startDate time.Time, endDate time.Time,
	category []string,
	pageNum int, pageSize int, models *[]LeadList) error {

	var params []interface{}
	queryStr := `SELECT DISTINCT leads.*, 
		count(*) OVER() AS total,
		u.nickname AS staff_nickname,
		u.phone AS staff_phone,
		u.id AS staff_id,
		u.username AS staff_username,
		c.name AS company_name
		FROM leads
		FULL OUTER JOIN users AS u ON u.id = uuid(leads.assignment) 
		FULL OUTER JOIN companies AS c ON c.id = uuid(leads.owner_id)
		`
	if ownerID == "" {
		queryStr = queryStr + `where leads.owner_id is not null `
	} else {
		queryStr = queryStr + `Where leads.owner_id = ? `
		params = append(params, ownerID)
	}

	if !startDate.IsZero() && !endDate.IsZero() {
		queryStr = queryStr + "AND leads.created_at > ? AND leads.created_at <= ? "
		params = append(params, startDate, endDate)
	}
	if len(category) > 0 {
       queryStr = queryStr + "AND category IN ("
       for i := 0; i < len(category)-1; i++ {
          queryStr = queryStr + "?,"
          params = append(params, category[i])
       }
       queryStr += "?) "
       params = append(params, category[len(category)-1])
    }

	queryStr = queryStr + "ORDER BY leads.created_at DESC "

	queryStr = queryStr + "OFFSET ? "
	params = append(params, pageNum*pageSize)
	queryStr = queryStr + "LIMIT ? "
	params = append(params, pageSize)

	_, err := database.Db.Query(models, queryStr, params...)
	return err

}

最后显示的拼接SQL语句应该是这样的

SELECT *
    FROM leads
    where leads.owner_id is not null 
    AND leads.created_at > ? AND leads.created_at <= ? 
    AND category IN (?,?)
    ORDER BY leads.created_at DESC 
    OFFSET ? LIMIT ?