GORM Association (t+1) to 1 database query

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

GORM comes with a drawback, when we try to fetch associations, 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 and corresponding Profile model with one to one relationship.

This will lead to two database queries one for the users table and another one for the profiles table. Query order can be changed by using different scopes e.g.

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

Now the first query would run on profiles the table and 2nd query on users the table.

Now, whenever we do profile.User then we can see that the User field is prepopulated with correct details.

But what if we can trick the GORM to make only one database query and prepopulate the result? GO has a nice feature of an embedded struct, we can embed one struct in another one that will hold the composite results like this

What do we expect now? As per the GO SQL Rows scanner, this should populate the User and Profile fields UserAndProfile but this won't always work. You may be wondering why so? GORM handles the mapping differently, it tries to match the database column names from SQL query results and struct field names.

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

If you go through line by line carefully then you can see it can lead to an error, if duplicate column names are present in the struct.

But we can achieve the same by aliasing the columns, we need to create a new embedded struct that will have aliased field names for example.

Now All three fields would be populated without any issues. If we have a DAO layer, then we can put this logic in DAO and all services would be working as expected. This is a huge win from 2 queries to 1 query.

So far we have seen, we can handle all types of one-to-one relationships by embedding one model in another and aliasing the column names in select. But what happens if we do the same for one-to-many relationships? It will have problems as in the case of one-to-many relationships we’ll have multiple rows for a single row. Though if we need only some set of columns from one to many relationships then we can concatenate those columns.

For example, let’s say we’re storing the user’s address in the Address table and it has a zip code we want to find all zip codes for a given user, along with their profile and user details.

We can extract all zip codes for a user having id = 1 using the above code snippet.

Currently, our zip codes are concatenated in a single field, which requires a split to make an array 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

At the same time, this does not look very intuitive when we need all the fields of one or more related models and that model has one-to-many relationships. Nevertheless, we can solve that problem as well, for this we can add an intermediate layer aka adapter that would convert the database results into the respective model.

Let’s say we have three models User has one Profile and multiple Addresses.

Now we need to create one struct for the profile and one for the Address, struct field names have to be as per the aliased column names. Let’s call them projected models

Now we’ve all the required projected models, we can run a database query that will pull results from the database with the help of projected models to retrieve the result. In this query, we have aliased all table columns except the users table.

By doing this we have retrieved all results from the database where user_id is 1, but this result is not quite useful due to one-to-many relationships between the user and address entities.

To solve that we can just run a quick for loop over the database results to fix the association and that’s the job of the adapter.

This is another performance gain from 3 database queries to one database query.

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