Gorm Golang orm协会 [英] Gorm Golang orm associations

问题描述

我使用Go与 GORM ORM 。
我有以下结构。关系很简单。一个城镇有多个地方，一个地方属于一个城镇。

 类型地方结构{
 ID int 
名称字符串
镇镇
} 
 
类型镇结构{
 ID int 
名称字符串
} 
  

现在我想查询所有地点，并与相关城镇的相关信息相处。
这是我的代码：

  db，_：= gorm.Open（sqlite3，./data .db）
 defer db.Close（）
 
 places：= []将{} 
 db.Find（&places）
 fmt.Println（地点）
  

我的示例数据库包含以下数据：

  / *地点表* / 
 id名称town_id 
 1地点1 1 
 2地点2 1 
 
 / *城镇Table * / 
 id name 
 1 Town1 
 2 Town2 
  

我接收：

[{1 Place1 {0}} {2 Mares Place2 {0}} ]

但我很期待接收类似的内容（这两个地方都属于同一个城镇）：

[{1 Place1 {1 Town1}} {2 Mares Place2 {1 Town1}}]

我该如何做这样的查询？我试着用 Preloads 和相关没有成功（可能是错误的方法）。我无法得到预期的结果。

解决方案

TownID 必须被指定为外键。 Place 结构会像这样：

 类型Place struct {
 ID int 
名称字符串
描述字符串
 TownID int 
 Town Town 
} 
  

现在有不同的方法来处理这个问题。例如：

  places：= []放置{} 
 db.Find（&places）
为i，_：= range places {
 db.Model（places [i]）。Related（& places [i] .Town）
} 
  
 
 
 
这肯定会产生预期的结果，但请注意日志输出和查询触发。
 
 
 < SELECT * FROMplaces
 [1.00ms] SELECT * FROMtownsWHERE（id='1'）
 [pre $  [4.76ms] 0.73ms] SELECT * FROMtownsWHERE（id='1'）
 
 [{1 Place1 {1 Town1} 1} {2 Place2 {1 Town1} 1}] 
  

输出是预期的，但这种方法有一个根本的缺陷，注意每个地方都需要做另一个产生 n + 1 问题的db查询。这可以解决问题，但随着地方数量的增长，它会很快失去控制。

事实证明，使用预加载 good 方法相当简单。

 db.Preload（Town）。查找（&放置）
  

这就是产生的查询日志：

$ p $ [22.24ms] SELECT * FROMplaces
[0.92ms] SELECT * FROMtownsWHERE（（1）中的id）

[{1 Place1 {1 Town1} 1} {2 Place2 {1 Town1} 1}]


这种方法只会触发两个查询，一个查询所有地方，一个查询所有城镇。这种方法在地点和城镇数量方面表现良好（在所有情况下只有两个查询）。

I'm using Go with the GORM ORM. I have the following structs. The relation is simple. One Town has multiple Places and one Place belongs to one Town.

type Place struct {
  ID          int
  Name        string
  Town        Town
}

type Town struct {
  ID   int
  Name string
}

Now i want to query all places and get along with all their fields the info of the corresponding town. This is my code:

db, _ := gorm.Open("sqlite3", "./data.db")
defer db.Close()

places := []Place{}
db.Find(&places)
fmt.Println(places)

My sample database has this data:

/* places table */
id  name    town_id
 1  Place1        1
 2  Place2        1

/* towns Table */
id name
 1 Town1
 2 Town2

i'm receiving this:

[{1 Place1 {0 }} {2 Mares Place2 {0 }}]

But i'm expecting to receive something like this (both places belongs to the same town):

[{1 Place1 {1 Town1}} {2 Mares Place2 {1 Town1}}]

How can i do such query ? I tried using Preloads and Related without success (probably the wrong way). I can't get working the expected result.

解决方案

TownID must be specified as the foreign key. The Place struct gets like this:

type Place struct {
  ID          int
  Name        string
  Description string
  TownID      int
  Town        Town
}

Now there are different approach to handle this. For example:

places := []Place{}
db.Find(&places)
for i, _ := range places {
    db.Model(places[i]).Related(&places[i].Town)
}

This will certainly produce the expected result, but notice the log output and the queries triggered.

[4.76ms]  SELECT  * FROM "places"
[1.00ms]  SELECT  * FROM "towns"  WHERE ("id" = '1')
[0.73ms]  SELECT  * FROM "towns"  WHERE ("id" = '1')

[{1 Place1  {1 Town1} 1} {2 Place2  {1 Town1} 1}]

The output is the expected but this approach has a fundamental flaw, notice that for every place there is the need to do another db query which produce a n + 1 problem issue. This could solve the problem but will quickly gets out of control as the amount of places grow.

It turns out that the good approach is fairly simple using preloads.

db.Preload("Town").Find(&places)

That's it, the query log produced is:

[22.24ms]  SELECT  * FROM "places"
[0.92ms]  SELECT  * FROM "towns"  WHERE ("id" in ('1'))

[{1 Place1  {1 Town1} 1} {2 Place2  {1 Town1} 1}]

This approach will only trigger two queries, one for all places, and one for all towns that has places. This approach scales well regarding of the amount of places and towns (only two queries in all cases).

这篇关于Gorm Golang orm协会的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！