{{announcement.body}}
{{announcement.title}}

GORM Association (t+1) to 1 Database Query

DZone 's Guide to

GORM Association (t+1) to 1 Database Query

In this article, lok at GORM association and a one to one database query.

· Database Zone ·
Free Resource

GORM is one of the many ORM (Objet-Relationational Mapper) for Go programming language. It comes with some nice intuitive methods to deal with the association. For details, refer to the documentation.

GORM comes with a drawback when we try to fetch associations, and then it queries the database for associated tables, which leads to (t+1) queries where t is the number of related tables in a query. 

For example, let's say we have a User table and a corresponding Profile table, and it has one to one mapping.

Go
 




xxxxxxxxxx
1
12


 
1
type User struct {
2
  gorm.Model
3
  Refer string
4
  Name string
5
}
6
7
type Profile struct {
8
  gorm.Model
9
  Name      string
10
  User      User `gorm:"association_foreignkey:Refer"` // use Refer as association foreign key
11
  UserRefer string
12
}



Find a profile whose user id is 1:

Go
 




xxxxxxxxxx
1


 
1
var user User
2
var profile Profile
3
db.Find(&user, 1).Related(&profile)
4
 
          



This will lead to two database queries: one for the  users  table and another one for the profiles  table. Even sometimes we can do, now the first query would run on  profiles  table and 2nd query on  users  table. 

Go
 




xxxxxxxxxx
1


 
1
db.Where("user_id", 1 ).Preload("User").Find(&profile)
2
 
          



Now when we do  profile.User, we can see  that User field is populated with correct details.

But what if we can trick the GORM to make only one query and populate the result? Golang has a nice feature of embedded struct, we can see another struct that will hold both the results like:

Go
 




xxxxxxxxxx
1


 
1
type UserAndProfile struct{
2
  Profile
3
  User
4
}



Go
 




xxxxxxxxxx
1


 
1
var userAndProfile UserAndProfile
2
db = db.Joins("JOIN users u on u.id = profiles.user_id")
3
db = db.Select("profiles.*, users.*")
4
db = db.Where("user_id", 1)
5
db.Table("profiles").Find(&userAndProfile)



 What do we expect now? As per GO SQL Rows scanner, this should populate User and Profile fields of  UserAndProfile  struct, but this won't always work. You may be wondering why. 

GORM handles the mapping differently; it tries to match the database column names from SQL query result and struct filed names, and as we can see, there are multiple duplicate columns here (id, created_at, deleted_at). 

File: https://github.com/jinzhu/gorm/blob/master/scope.go#L485

Go
 




xxxxxxxxxx
1
31


 
1
for index, column := range columns {
2
        values[index] = &ignored
3
 
          
4
        selectFields = fields
5
        offset := 0
6
        if idx, ok := selectedColumnsMap[column]; ok {
7
            offset = idx + 1
8
            selectFields = selectFields[offset:]
9
        }
10
 
          
11
        for fieldIndex, field := range selectFields {
12
            if field.DBName == column {
13
                if field.Field.Kind() == reflect.Ptr {
14
                    values[index] = field.Field.Addr().Interface()
15
                } else {
16
                    reflectValue := reflect.New(reflect.PtrTo(field.Struct.Type))
17
                    reflectValue.Elem().Set(field.Field.Addr())
18
                    values[index] = reflectValue.Interface()
19
                    resetFields[index] = field
20
                }
21
 
          
22
                selectedColumnsMap[column] = offset + fieldIndex
23
 
          
24
                if field.IsNormal {
25
                    break
26
                }
27
            }
28
        }
29
    }
30
 
          
31
    scope.Err(rows.Scan(values...))



If you go through line by line carefully, then you can see it can lead to an error if column names are duplicated. 

But we can achieve the same by aliasing the column name. We need to create a new embedded struct that will have aliased fields, for example:

Go
 




xxxxxxxxxx
1


 
1
type UserAndProfile struct{
2
  Profile
3
  UserId int
4
  Refer string
5
  Name string
6
}



Go
 




xxxxxxxxxx
1


 
1
var userAndProfile UserAndProfile
2
db = db.Joins("JOIN users u on u.id = profiles.user_id")
3
db = db.Select("profiles.*, users.id as user_id, refer, name")
4
db = db.Where("user_id", 1)
5
db.Table("profiles").Find(&userAndProfile)



Now all three fields will be populated without any issue. If we have a DAO layer, then we can put this logic in DAO, and all services would be working as expected. 

All one to one table relationships can be handled without any issue, but this won't work for one to many relationships. If we need some set of fields from one to many relationships, then we can concatenate those columns in the select to avoid handling the one to many relationships.

For example, let's say we're storing the address in the Address table and it has a zip code. Now we want to find all zip codes of users.

Go
 




xxxxxxxxxx
1


 
1
type Address struct{
2
   gorm.Model
3
   UserId uint
4
   User User
5
   ZipCode string
6
}



We can extract all zip codes for user having id  = 1 as 

Go


Now the only thing left is to split the ZipCodes field. Once we split the field, we will get a list of zip codes. 

This hack is very useful when we have a condition on associated tables,

  • Find all users whose zip code is xyz  
  • Find all users who have been referred by Ram
  • Find the user and profile together together 

At the same time, this does not look to be very intuitive when we need all the fields of some models and that model has one to many relationships.

For that, we can add an intermediate layer aka adapter that would convert this database results into the respective model. Let's say our model is like this: this User has one profile and multiple addresses. 

Go
 




xxxxxxxxxx
1
21


 
1
type User struct {
2
  ID        uint `gorm:"primary_key"`
3
  UserName  string
4
  Profile   Profile
5
  Addresses []Address
6
}
7
 
          
8
 
          
9
type Profile struct {
10
  ID        uint `gorm:"primary_key"`
11
  UserId    int
12
  Name      string
13
  Refer     string
14
}
15
 
          
16
type Address struct{
17
  ID        uint `gorm:"primary_key"`
18
  UserId    int
19
  ZipCode   string
20
}
21
 
          



Now we need to create projected models so those column names would be aliased. For example, to select the profile's ID column, we will use profile_id as a column name in the select query. 

Go


We've created three structs to handle the projection of Profile, Address, and a composite model that will have all three models.

Go
 




xxxxxxxxxx
1


 
1
var userPrpfileAndAddress []*UserPrpfileAndAddress
2
db = db.Joins("JOIN profiles p on p.user_id = users.id")
3
db = db.Joins("JOIN addresses a on a.user_id = users.id")
4
db = db.Select("users.*, p.id as profile_id, p.user_id as profile_user_id refer, name, a.id as address_id, a.user_id as adress_user_id, zip_code")
5
db = db.Where("id", 1)
6
db.Table("users").Find(&userPrpfileAndAddress)



By doing this, we have retrieved all results from the database where user_id is one, but this result is not quite useful due to one to many relationships of address. 

To solve that, we can just run a quick  for  loop over the database results to fix the association.

Go
 




xxxxxxxxxx
1
36


 
1
func ToAddress(a AdressWithId) Address {
2
    return Address{
3
        UserId:  a.AddressUserId,
4
        Id:      a.AddressId,
5
        ZipCode: a.ZipCode,
6
    }
7
}
8
 
          
9
func ToProfile(p ProfileWithId) Profile {
10
    return Profile{
11
        UserId: p.ProfileUserId,
12
        Id:     p.ProfileId,
13
        Name:   p.Name,
14
        Refer:  p.Refer,
15
    }
16
}
17
 
          
18
func ToUsers(userProfileAndAddress []UserProfileAndAddress) []*User {
19
    usersMap := make(map[uint]*User, 0)
20
    for _, userProfileAndAddress := range userProfileAndAddresses {
21
        user, found := usersMap[ userProfileAndAddress.Id ]
22
        if found {
23
            user.Addresses = append(user.addresses, ToAddress(userProfileAndAddress.AdressWithId))
24
        } else {
25
            user := &userProfileAndAddress.User
26
            user.Profile = ToProfile(userProfileAndAddress.ProfileWithId)
27
            user.Addresses = []Address{ToAddress(userProfileAndAddress.AdressWithId)}
28
        }
29
        usersMap[ userProfileAndAddress.Id ] = user
30
    }
31
    users := make([]*User, len(usersMap))
32
    for _, user := range usersMap {
33
        users = append(users, user)
34
    }
35
    return users
36
}



Now we need to just call the  ToUsers  method on the database result. We have removed two additional database queries by doing this hack, which can easily save up to 20-100Ms depending upon the database table size and network latency.

If you found this post helpful, please share and give it a thumbs up.

Topics:
database, dbms, go language, gorm, one to one, optimization, orm, perforamnce, rdbms, tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}