GORM Association (t+1) to 1 database query
GORM comes with a drawback, when we try to fetch associations, then it queries the database for associated tables, that 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 relationship.
This will lead to two database query one for the
users table another one for
profiles table. Query order can be changed by using different scope e.g.
Now the first query would run on
profiles table and 2nd query on
Now, whenever we do
profile.User then we can see that
User field is populated with correct details.
But what if we can trick the GORM to make only one database query and populate the result? GO has a nice feature of embedded struct, we can embed one struct in another one 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 but this won't always works. You may be wondering why so? GORM handles the mapping differently, it tries to match the database column names from SQL query result and struct field names.
If you go through line by line carefully then you can see it can lead to 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 issue. 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 relationship 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 relatships we’ll have multiple rows for a single model, in the case of join. 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 user’s address in 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 user having id = 1 using above code snippet.
Currently our zip codes are concatenated in a single field, that 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 to be 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 profile and one for Address, struct field names have to be as per the aliased column names. Let’s call them projected models
Now we’ve all required projected models, we can run a database query with the help of projected models to retrieve the result. In this query we have aliased all table columns except 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 of address table.
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.