Golang GORM join查詢

Go使用GORM ORM套件做select join查詢的方式如下。

範例環境：

• Go 1.19
• GORM 2.0 (gorm.io/gorm v1.24.2)
• PostgreSQL 14

事前要求

參考「Golang GORM PostgreSQL基本設定」設定資料庫及安裝GORM套件。

建立EMPLOYEE、DEPARTMENT資料表及資料如下。

CREATE TABLE IF NOT EXISTS department (
    id bigserial PRIMARY KEY,
    name varchar(60) UNIQUE NOT NULL,
    created_at timestamp NOT NULL
);

INSERT INTO public.department ("name", created_at)
VALUES('HR', '2022-12-29 22:37:02.199');
INSERT INTO public.department (id, "name", created_at)
VALUES('Marketing', '2022-12-29 22:37:23.538');
INSERT INTO public.department (id, "name", created_at)
VALUES('Logistic', '2022-12-29 22:39:14.389');

CREATE TABLE IF NOT EXISTS employee (
    id bigserial PRIMARY KEY,
    department_id bigint,
    name varchar(60) UNIQUE NOT NULL,
    age integer,
    created_at timestamp NOT NULL
);

INSERT INTO public.employee (department_id, "name", age, created_at)
VALUES(1, 'John', 33, '2022-12-29 22:38:36.891');
INSERT INTO public.employee (id, department_id, "name", age, created_at)
VALUES(2, 'Mary', 28, '2022-12-29 22:38:36.893');
INSERT INTO public.employee (id, department_id, "name", age, created_at)
VALUES(NULL, 'Tony', 45, '2022-12-29 22:38:36.895');

DEPARTMENT

 id |   name    |         created_at
----+-----------+----------------------------
  1 | HR        | 2022-12-29 22:37:02.199582
  2 | Marketing | 2022-12-29 22:37:23.538383
  3 | Logistic  | 2022-12-29 22:39:14.389925

EMPLOYEE

 id | department_id | name | age |         created_at
----+---------------+------+-----+----------------------------
  1 |             1 | John |  33 | 2022-12-29 22:38:36.891199
  2 |             2 | Mary |  28 | 2022-12-29 22:38:36.893386
  3 |        <null> | Tony |  45 | 2022-12-29 22:38:36.895071

範例

GORM使用gorm.DB.Select搭配gorm.DB.Joins做join查詢。

下面範例先用gorm.DB.Model指定要查詢的model Employee；
gorm.DB.Select設定要查詢的欄位employee.id, employee.department_id, employee.name, employee.age, employee.created_at；
gorm.DB.Joins設定join條件，employee.department_id參照department.id；
gorm.DB.Scan將查詢結果塞入model slice；。

main.go

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/schema"
)

type Employee struct {
    ID           int64     // primary key, column name is `id`
    DepartmentID int64     // column name is `department_id`
    Name         string    // column name is `name`
    Age          int       // column name is `age`
    CreatedAt    time.Time // column name is `created_at`
}

const (...)

func getGormDB() *gorm.DB {...}

func main() {
    db := getGormDB()

    emps := []Employee{}
    db.Model(&Employee{}).
        Select("employee.id, employee.department_id, employee.name, employee.age, employee.created_at").
        Joins("inner join department on department.id = employee.department_id").
        Scan(&emps)
        /*
            SELECT id, department_id, name, age, created_at
            FROM employee
            INNER JOIN department ON department_id = employee.department_id
        */
    for _, emp := range emps {
        fmt.Println(emp)
    }
}

github。

測試

執行印出以下。

{1 1 John 33 2022-12-29 22:38:36.891199 +0000 UTC}
{2 2 Mary 28 2022-12-29 22:38:36.893386 +0000 UTC}

GORM實際執行的SQL如下：

SELECT 
    employee.id, 
    employee.department_id, 
    employee.name, 
    employee.age, 
    employee.created_at 
FROM "employee" INNER JOIN department ON department.id = employee.department_id

• GORM Docs - Query - Joins
• PostgreSQL 設定外鍵 foreign key