改进代码var req []*Total res := db.Scopes(models.Paginate(page, pageSize)).Raw("SELECT * FROM flaws WHERE facility_id = ? "+ "UNION SELECT * FROM issues WHERE facility_id = ? "+ "UNION SELECT * FROM records WHERE facility_id = ? "+ "UNION SELECT * FROM qualities WHERE facility_id = ? "+ "UNION SELECT * FROM inspections WHERE facility_id = ?", code, code, code, code, code).Find(&req) if res.RowsAffected == 0 { resp.Code = 400 resp.Msg = "记录不存在" ctx.JSON(400, resp) return } resp.Data = req ctx.JSON(200, resp)
时间: 2024-01-09 11:04:36 浏览: 34
可以将这段代码进行重构,使其更加简洁和易读。可以使用以下方式重构:
```
var total []*Total
db.Where("facility_id = ?", code).Scopes(
models.Paginate(page, pageSize),
).Find(&total)
if len(total) == 0 {
ctx.JSON(400, Response{
Code: 400,
Msg: "记录不存在",
Data: nil,
})
return
}
ctx.JSON(200, Response{
Code: 200,
Msg: "成功获取记录列表",
Data: total,
})
```
这样可以将查询语句进行简化,同时也可以将错误响应和成功响应的代码进行分离,使得代码更加易于维护。
相关问题
改进代码var req []*Total res := db.Scopes(models.Paginate(page, pageSize)).Raw("SELECT * FROM flaws WHERE facility_id = ? "+ "UNION SELECT * FROM issues WHERE facility_id = ? "+ "UNION SELECT * FROM records WHERE facility_id = ? "+ "UNION SELECT * FROM qualities WHERE facility_id = ? "+ "UNION SELECT * FROM inspections WHERE facility_id = ?", code, code, code, code, code).Find(&req)
可以尝试使用预编译语句来优化 SQL 查询性能,同时使用占位符来避免 SQL 注入攻击。改进后的代码如下所示:
```go
stmt, err := db.DB().Prepare("SELECT * FROM flaws WHERE facility_id = ? " +
"UNION SELECT * FROM issues WHERE facility_id = ? " +
"UNION SELECT * FROM records WHERE facility_id = ? " +
"UNION SELECT * FROM qualities WHERE facility_id = ? " +
"UNION SELECT * FROM inspections WHERE facility_id = ? " +
"LIMIT ? OFFSET ?")
if err != nil {
// 处理错误
}
defer stmt.Close()
req := make([]*Total, 0)
offset := (page - 1) * pageSize
limit := pageSize
rows, err := stmt.Query(code, code, code, code, code, limit, offset)
if err != nil {
// 处理错误
}
defer rows.Close()
for rows.Next() {
var item Total
err := rows.Scan(&item)
if err != nil {
// 处理错误
}
req = append(req, &item)
}
if err := rows.Err(); err != nil {
// 处理错误
}
res := db.Model(&Total{}).Where("facility_id = ?", code).Count(&Total{})
// 处理结果
```
这里我们使用了 `db.DB().Prepare()` 方法来预编译 SQL 查询语句,并使用占位符 `?` 来代替变量,从而避免了 SQL 注入攻击。
同时,我们也将分页查询的 `LIMIT` 和 `OFFSET` 参数移到了 SQL 查询语句中,以避免使用 `Scopes()` 方法产生额外的查询开销。在查询结果时,我们使用 `stmt.Query()` 方法来执行查询,并使用 `rows.Scan()` 方法将查询结果映射到结构体中。
最后,我们使用 `db.Model().Where().Count()` 方法来查询总记录数,并将结果返回给调用者。
改进代码var req []*Total res := db.Scopes(models.Paginate(page, pageSize)).Raw("SELECT * FROM flaws WHERE facility_id = ? "+ "UNION SELECT * FROM issues WHERE facility_id = ? "+ "UNION SELECT * FROM records WHERE facility_id = ? "+ "UNION SELECT * FROM qualities WHERE facility_id = ? "+ "UNION SELECT * FROM inspections WHERE facility_id = ?", code, code, code, code, code).Find(&req)
这段代码可以优化为使用GORM的联合查询(Union)功能,避免使用Raw SQL语句。
可以尝试这样改进代码:
```
err := db.Where("facility_id = ?", code).
Select("id, created_at, updated_at, field1, field2, NULL as field3, NULL as field4, NULL as field5").
Table("flaws").
Union(db.Where("facility_id = ?", code).
Select("id, created_at, updated_at, field1, NULL as field2, field3, field4, NULL as field5").
Table("issues")).
Union(db.Where("facility_id = ?", code).
Select("id, created_at, updated_at, field1, NULL as field2, NULL as field3, NULL as field4, field5").
Table("records")).
Union(db.Where("facility_id = ?", code).
Select("id, created_at, updated_at, field1, field2, NULL as field3, NULL as field4, NULL as field5").
Table("qualities")).
Union(db.Where("facility_id = ?", code).
Select("id, created_at, updated_at, field1, NULL as field2, field3, field4, field5").
Table("inspections")).
Order("created_at desc").
Limit(pageSize).
Offset((page - 1) * pageSize).
Find(&req).Error
```
在这个示例中,我们使用了 `Select` 函数选择每个表中的字段,并将不需要的字段设置为 `NULL`。然后我们使用 `Union` 函数将所有查询合并到一起,并使用 `Order`、`Limit` 和 `Offset` 函数进行分页和排序。
这种方法比使用 `Raw` 函数更安全,因为它可以防止 SQL 注入攻击,并且更易于维护。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)