GORM/Go: How to Join Two Tables Properly?

Introduction

GORM is an ORM (Object-Relational Mapping) library for Golang which allows you to perform operations on a RDBMS (relational database management system). Completely replacing raw SQL queries that developers are used to writing with their ORM is unrealistic as it abstracts out much of the essence behind setting up models and relationships while leaving them with only a high level understanding. A classic example in DB management is joining several tables and searching for related data. In this tutorial, you will learn how to join two tables in GORM with real-world examples and popular ways such as to use of `Preload()` and `Joins()`.

Why Joining Tables Matters

Tables are related through foreign keys in a relational database. For example, the e-commerce application may have a User table and an Order table where each user can have many orders. By performing this join across the tables, you will be able to retrieve user data and their orders all at once thereby optimising your access to data.

Setting Up Models

Before we dive deeper into the connector table, We need to define our model and build relationships. Let us consider two models. Users and orders.

type User struct {
    ID        uint
    Name      string
    Email     string
    Orders    []Order `gorm:"foreignKey:UserID"`
}

type Order struct {
    ID       uint
    OrderID  string
    Amount   float64
    UserID   uint
}

In the example above:

• User is a table in your database that may have fields such as ID, Name and Email
• Model Order with its fields — Id, OrderId, Amount and UserId which represents a table of Orders.
• The Orders field in the User model is a hasMany relationship, which means that a user can have multiple orders. The foreignKey:UserID tag binds the User to the Order model.

Defining Relationships in GORM

• HasMany: In our example, User has many Orders.
• BelongsTo: Conversely, Order belongs to User. This relationship is implicitly defined through the UserID foreign key in the Order table.

Now we have created a model. We will join between those models.

Performing the Join

Many GORM methods allow combining tables. Two of some common methods are Preload() and Joins().

Using Preload()

The Preload() method allows you to automatically load related entities. For example you can do this if you want to load all users and their related commands.

var users []User
db.Preload("Orders").Find(&users)

This query brings all users and their orders together into a single query. SQL generated behind scenes inserts the implicit left join that the user loads. Even though they didn't have any orders.

Using Joins()

If you need control beyond join operation (for example, to specify join conditions), you can use Joins() function. Here is an example of an inner join between the Users table and the Order table.

var results []struct {
    Name    string
    OrderID string
    Amount  float64
}
db.Table("users").
    Select("users.name, orders.order_id, orders.amount").
    Joins("left join orders on orders.user_id = users.id").
    Scan(&results)

This will return a list of users with information relevant to their request. You can adjust heating type (internal, external, etc.) according to your indivisual needs.

Inner Join vs Left Join

• Inner Join: Returns records where there is a match in tables. It will not take the records if it is null in values.
• Left Join: Returns all records from the left table (User), and the matched records from the right table (Order).

You can edit query and select desired joins in GORM, lets take for example:

• Inner join: db.Joins("join orders on")
• Left join: db.Joins("left join orders on ")

Customizing the Join Query

You may want to optimize aggregation conditions or optimize extracted data these are some helpful strategies for you to try on your own:

Filtering and Selecting Specific Fields

A Select() may be used for choosing columns, and a Where() might also be used to filter some consequences out.

var results []struct {
    Name    string
    OrderID string
}
db.Table("users").
    Select("users.name, orders.order_id").
    Joins("left join orders on orders.user_id = users.id").
    Where("users.name = ?", "John Doe").
    Scan(&results)

Here we are getting only the users by name "John Doe" and their relevant order ids only. To do so simply add whatever filter you need to accurately adjust the results.

Optimizing the Query

Fetching unnecessary fields can halt down your query. By using Select(), you prevent some data load by fetching only necessary fields. Additionally, if you are fetching a large data set, You can add pages to avoid memory overload

var results []User
db.Preload("Orders").Limit(10).Offset(0).Find(&results)

This way you get the user has 10 queries (per time).

Conclusion

Joins are a key part of Relational Databases and GORM makes it easy in Go. By Preload() for auto-joins, or Joins() for manually defining the query based joins you can easily fetch associated data without writing complex SQL. Even when dataset was very large you have to optimize queries—for example reduce fields selection, filter results etc), so your applications is fast.

Now you are in a good position to utilise GORM joins on various occasions.