GORM Association (t+1) to 1 Database Query
In this article, lok at GORM association and a one to one database query.
Join the DZone community and get the full member experience.
Join For FreeGORM 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.
xxxxxxxxxx
type User struct {
gorm.Model
Refer string
Name string
}
type Profile struct {
gorm.Model
Name string
User User `gorm:"association_foreignkey:Refer"` // use Refer as association foreign key
UserRefer string
}
Find a profile whose user id is 1:
xxxxxxxxxx
var user User
var profile Profile
db.Find(&user, 1).Related(&profile)
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.
xxxxxxxxxx
db.Where("user_id", 1 ).Preload("User").Find(&profile)
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:
xxxxxxxxxx
type UserAndProfile struct{
Profile
User
}
xxxxxxxxxx
var userAndProfile UserAndProfile
db = db.Joins("JOIN users u on u.id = profiles.user_id")
db = db.Select("profiles.*, users.*")
db = db.Where("user_id", 1)
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
xxxxxxxxxx
for index, column := range columns {
values[index] = &ignored
selectFields = fields
offset := 0
if idx, ok := selectedColumnsMap[column]; ok {
offset = idx + 1
selectFields = selectFields[offset:]
}
for fieldIndex, field := range selectFields {
if field.DBName == column {
if field.Field.Kind() == reflect.Ptr {
values[index] = field.Field.Addr().Interface()
} else {
reflectValue := reflect.New(reflect.PtrTo(field.Struct.Type))
reflectValue.Elem().Set(field.Field.Addr())
values[index] = reflectValue.Interface()
resetFields[index] = field
}
selectedColumnsMap[column] = offset + fieldIndex
if field.IsNormal {
break
}
}
}
}
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:
xxxxxxxxxx
type UserAndProfile struct{
Profile
UserId int
Refer string
Name string
}
xxxxxxxxxx
var userAndProfile UserAndProfile
db = db.Joins("JOIN users u on u.id = profiles.user_id")
db = db.Select("profiles.*, users.id as user_id, refer, name")
db = db.Where("user_id", 1)
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.
xxxxxxxxxx
type Address struct{
gorm.Model
UserId uint
User User
ZipCode string
}
We can extract all zip codes for user having id = 1 as
type UserWithZipCodes struct{
UserAndProfile
ZipCodes string
}
var userWithZipCodes UserWithZipCodes
db = db.Joins("JOIN users u on u.id = profiles.user_id")
db = db.Joins("JOIN address a on a.user_id = profiles.user_id")
db = db.Select("profiles.*, users.id as user_id, refer, name, GROUP_CONCAT(addresses.zip_code) as zip_codes")
db = db.Group("users.id")
db = db.Where("user_id", 1)
db.Table("profiles").Find(&userWithZipCodes)
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.
xxxxxxxxxx
type User struct {
ID uint `gorm:"primary_key"`
UserName string
Profile Profile
Addresses []Address
}
type Profile struct {
ID uint `gorm:"primary_key"`
UserId int
Name string
Refer string
}
type Address struct{
ID uint `gorm:"primary_key"`
UserId int
ZipCode string
}
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.
xxxxxxxxxx
type ProfileWithId struct{
ProfileId int
ProfileUserId int
Name string
Refer string
}
type AddresssithId struct{
AddressId int
AddressUserId int
ZipCode string
}
type UserPrpfileAndAddress struct{
User
ProfileWithId
UserAddress
}
We've created three structs to handle the projection of Profile, Address, and a composite model that will have all three models.
xxxxxxxxxx
var userPrpfileAndAddress []*UserPrpfileAndAddress
db = db.Joins("JOIN profiles p on p.user_id = users.id")
db = db.Joins("JOIN addresses a on a.user_id = users.id")
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")
db = db.Where("id", 1)
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.
xxxxxxxxxx
func ToAddress(a AdressWithId) Address {
return Address{
UserId: a.AddressUserId,
Id: a.AddressId,
ZipCode: a.ZipCode,
}
}
func ToProfile(p ProfileWithId) Profile {
return Profile{
UserId: p.ProfileUserId,
Id: p.ProfileId,
Name: p.Name,
Refer: p.Refer,
}
}
func ToUsers(userProfileAndAddress []UserProfileAndAddress) []*User {
usersMap := make(map[uint]*User, 0)
for _, userProfileAndAddress := range userProfileAndAddresses {
user, found := usersMap[ userProfileAndAddress.Id ]
if found {
user.Addresses = append(user.addresses, ToAddress(userProfileAndAddress.AdressWithId))
} else {
user := &userProfileAndAddress.User
user.Profile = ToProfile(userProfileAndAddress.ProfileWithId)
user.Addresses = []Address{ToAddress(userProfileAndAddress.AdressWithId)}
}
usersMap[ userProfileAndAddress.Id ] = user
}
users := make([]*User, len(usersMap))
for _, user := range usersMap {
users = append(users, user)
}
return users
}
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.
Opinions expressed by DZone contributors are their own.
Comments