Gorm Golang orm associations

Question

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.

What's in the database? Also, did you try the `Related` function? — robbrit, Apr 04 '15 at 01:11
@robbrit I have improved the question to reflect the database sample data. No, but i tried now the `Related` function and still i haven't succeed to get the expected result. — Javier Cadiz, Apr 04 '15 at 03:44

score 53 · Accepted Answer · answered Apr 11 '15 at 16:07

53

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).

answered Apr 11 '15 at 16:07

Javier Cadiz

12,326
11
55
76

4

Am I wrong or Gorm does not create the FK in the DB? – Alessio Jun 08 '15 at 14:50
Alessio, did you sort that out? – Ezequiel Moreno Sep 29 '15 at 04:15
2

Is the eager load a bad practice in terms of performance? I mean, you are retrieving all the records from the Town table every time the find function executes. – Rafael Reyes Dec 11 '19 at 01:44
2

@Alessio yes, it doesn't. If you want Foreign Key in DB, you need explicitly write something like `db.Model(&Place{}).AddForeignKey("town_id", "towns(id)", "RESTRICT", "RESTRICT")` during your migrations. Yep, I know it's almost 5 years since your question, but let it be here for someone else :D – Alveona May 02 '20 at 14:40
@Alveona no such method in gorm v2 – Phani Rithvij Dec 10 '20 at 10:32
@Alveona thanks, I'm that someone. Was trying to figure out why my migration didn't create the actual FKs in the database, I thought it suffice with just running it. Thanks – MrCujo Apr 25 '22 at 17:05
@Alessio you can add a tag to the struct member you want, e.g.: `User User \`gorm:"constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"\`` – Nikolay Dimitrov Apr 26 '22 at 02:38

score 7 · Answer 2 · answered Apr 06 '15 at 20:20

You do not specify the foreign key of towns in your Place struct. Simply add TownId to your Place struct and it should work.

package main

import (
    "fmt"

    "github.com/jinzhu/gorm"
    _ "github.com/mattn/go-sqlite3"
)

type Place struct {
    Id     int
    Name   string
    Town   Town
    TownId int //Foregin key
}

type Town struct {
    Id   int
    Name string
}

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

    db.CreateTable(&Place{})
    db.CreateTable(&Town{})
    t := Town{
        Name: "TestTown",
    }

    p1 := Place{
        Name:   "Test",
        TownId: 1,
    }

    p2 := Place{
        Name:   "Test2",
        TownId: 1,
    }

    err := db.Save(&t).Error
    err = db.Save(&p1).Error
    err = db.Save(&p2).Error
    if err != nil {
        panic(err)
    }

    places := []Place{}
    err = db.Find(&places).Error
    for i, _ := range places {
        db.Model(places[i]).Related(&places[i].Town)
    }
    if err != nil {
        panic(err)
    } else {
        fmt.Println(places)
    }
}

Great. Is working now after reading this answer. Just one additional note/question. This approach seems to have a n+1 problem issue since for every place we are getting an additional query. Is this the proper behavior ? — Javier Cadiz, Apr 06 '15 at 21:26
If you dont't want to specify a join yourself I am afraid that this is the only way using Gorm, and yes, then you will have a n+1 issue. Gorm does support joins as a thin wrapper over sql: https://github.com/jinzhu/gorm#joins but then you have to specify the query. — olif, Apr 07 '15 at 08:18
I don't have any issue about implementing a join by myself but if i do so i will need to create another struct just to handle the query result. I just want to reuse the two existing ones. Is my assumption ok ? — Javier Cadiz, Apr 07 '15 at 21:24

score 5 · Answer 3 · answered Apr 17 '16 at 18:48

To optimize query I use "in condition" in the same situation

places := []Place{}

DB.Find(&places)

keys := []uint{}
for _, value := range places {
    keys = append(keys, value.TownID)
}

rows := []Town{}
DB.Where(keys).Find(&rows)

related := map[uint]Town{}
for _, value := range rows {
    related[value.ID] = value
}

for key, value := range places {
    if _, ok := related[value.TownID]; ok {
        res[key].Town = related[value.TownID]
    }
}

score 1 · Answer 4 · answered Apr 27 '20 at 06:32

1

First change your model:

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

And second, make preloading: https://gorm.io/docs/preload.html

answered Apr 27 '20 at 06:32

Javier Ruiz

11
1

score 1 · Answer 5 · answered Nov 07 '21 at 13:48

Click For Full Docs

Summary: preloading one-to-one relation: has one, belongs to

eager preload:

db.Preload("Orders").Preload("Profile").Find(&users)

join preload using inner join:

db.Joins("Orders").Joins("Profile").Find(&users)

preload all associations:

db.Preload(clause.Associations).Find(&users)

score 0 · Answer 6 · answered Sep 22 '19 at 02:54

0

No need to loop for ids, just pluck the ids

townIDs := []uint{}
DB.Model(&Place{}).Pluck("town_id", &placeIDs)

towns := []Town{}
DB.Where(townIDs).Find(&towns)

answered Sep 22 '19 at 02:54

7urkm3n

6,054
4
29
46

Gorm Golang orm associations

6 Answers6

Linked