时间戳

首页 > 解决方案 > GORM SQL 优化

sql - GORM SQL 优化

问题描述

在这里，我使用 GORM 进行 sql 查询。我已经宣布了模型。问题是我需要这样的输出。我能够生成预期的输出，但它需要循环，所以我认为我们可以通过减少嵌套调用来优化查询性能。那么如何合并这种查询类型的子查询或嵌套查询。

type Result struct {
    User1 User1
    User2 User2
}
type User1 struct {
    User     string
    Query_id string  `gorm:"primary_key"json:"query_id"`
    User2    []User2 `gorm:"foreignkey:query_id" json:"input"`
}
type User2 struct {
    Table_name string `json:"table_name"`
    Query_id   string `gorm:"primary_key"json:"query_id"`
}
func (User2) TableName() string {
    return "tabledata"
}
func (User1) TableName() string {
    return "querydata"
}
var user1 User1
var user2 []User2
db.Find(&user2)
db.Select("querydata.query_id,querydata.user,tabledata.table_name").Joins("JOIN tabledata ON tabledata.query_id = querydata.query_id ").Find(&user1)
user1.User2 = user2
c.JSON(http.StatusOK, gin.H{"result": &user1})

电流输出：


  "result": {
    "User": "ram",
    "query_id": "firstquery",
    "input": [
      {
        "table_name": "first_table",
        "query_id": "firstquery"
      },
      {
        "table_name": "second_table",
        "query_id": "firstquery"
      },
      {
        "table_name": "second_table",
        "query_id": "secondquery"
      },
      {
        "table_name": "second_table",
        "query_id": "secondquery"
      }]
}

**Expected output**

"result": 
{
    "User": "ram",
    "query_id": "firstquery",
    "input": [
      {
        "table_name": "first_table",
        "query_id": "firstquery"
      },
      {
        "table_name": "second_table",
        "query_id": "firstquery"
      }]

},
{
    "User": "krishna",
    "query_id": "secondquery",
    "input": [
      {
        "table_name": "first_table",
        "query_id": "secondquery"
      },
      {
        "table_name": "second_table",
        "query_id": "secondquery"
      }]

}
}


**Expected output via this**
I am able to generate expected output through this, but I want to minimize this for loop calls. so any suggestion how can I improve GROM SQL . 

var qresult []User1
db.Table("qtb").Find(&qresult)
for i := range qresult {
db.Table("ttb").Select("table_name").Where("query_id=?", qresult[i].Query_id).Find(&qresult[i].User2)
}

标签： sqlgograils-ormgo-gorm

解决方案

您应该User2在查询中预加载：

var qresult []User1
db.Preload("tabledata").Find(&qresult)

推荐阅读