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 Free
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.
Find a profile whose user id is 1:
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
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:
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).
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:
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.
We can extract all zip codes for user having id = 1 as
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.
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.
We've created three structs to handle the projection of Profile, Address, and a composite model that will have all three models.
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.
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.