Fetch Data with Composite Primary Keys in GORM

Make sure Git is installed on your machine and in your system’s PATH. Install the package to your $GOPATH with the go tool from shell:

$ go get github.com/go-sql-driver/mysql
$ go get -u github.com/jinzhu/gorm




Create a database with the name is learngorm. This database have 1 tables: Product table.

--
-- Table structure for table `account`
--

CREATE TABLE `account` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(250) NOT NULL,
  `password` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `account`
--

INSERT INTO `account` (`username`, `password`) VALUES
('acc1', '123'),
('acc2', '456'),
('acc3', '789');

-- --------------------------------------------------------

--
-- Table structure for table `role`
--

CREATE TABLE `role` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `role`
--

INSERT INTO `role` (`name`) VALUES
('Role 1'),
('Role 2'),
('Role 3');

-- --------------------------------------------------------

--
-- Table structure for table `role_account`
--

CREATE TABLE `role_account` (
  `account_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL,
	PRIMARY KEY (`account_id`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `role_account`
--

INSERT INTO `role_account` (`account_id`, `role_id`, `status`) VALUES
(1, 1, 1),
(1, 2, 1),
(1, 3, 1),
(2, 1, 0),
(2, 2, 0),
(3, 3, 1);




Create new folder named src. In src folder, create new folder named entities. In this folder, create new entities as below:

In entities folder, create new go file named role.entity.go as below:

package entities

import "fmt"

type Role struct {
	Id           int `gorm:"primary_key, AUTO_INCREMENT"`
	Name         string
	RoleAccounts []RoleAccount `gorm:"ForeignKey:RoleID"`
}

func (role *Role) TableName() string {
	return "role"
}

func (role Role) ToString() string {
	return fmt.Sprintf("id: %d\nname: %s", role.Id, role.Name)
}

In entities folder, create new go file named account.entity.go as below:

package entities

import "fmt"

type Account struct {
	Id           int `gorm:"primary_key, AUTO_INCREMENT"`
	Username     string
	Password     string
	RoleAccounts []RoleAccount `gorm:"foreignkey:AccountID"`
}

func (account *Account) TableName() string {
	return "account"
}

func (account Account) ToString() string {
	return fmt.Sprintf("id: %d\nusername: %s", account.Id, account.Username)
}

In entities folder, create new go file named roleaccount.entity.go as below:

package entities

import "fmt"

type RoleAccount struct {
	RoleID    int `gorm:"primary_key; column:role_id"`
	AccountID int `gorm:"primary_key; column:account_id"`
	Status    bool
	Role      Role
	Account   Account
}

func (roleAccount *RoleAccount) TableName() string {
	return "role_account"
}

func (roleAccount RoleAccount) ToString() string {
	return fmt.Sprintf("role id: %d\naccount id: %d\nstatus: %t", roleAccount.RoleID, roleAccount.AccountID, roleAccount.Status)
}

In src folder, create new folder named config. In this folder, create new file named config.go, this file is used to connect mysql database:

package config

import (
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

func GetDB() (*gorm.DB, error) {
	dbDriver := "mysql"
	dbName := "learngorm"
	dbUser := "root"
	dbPassword := "123456"
	db, err := gorm.Open(dbDriver, dbUser+":"+dbPassword+"@/"+dbName+"?charset=utf8&parseTime=True")
	if err != nil {
		return nil, err
	}
	return db, nil
}




In src folder, create new folder named models. In models folder, create new models as below:

In models folder, create new go file named account.model.go as below:

package models

import (
	"config"
	"entities"
)

type AccountModel struct {
}

func (accountModel AccountModel) FindAll() ([]entities.Account, error) {
	db, err := config.GetDB()
	if err != nil {
		return nil, err
	} else {
		var accounts []entities.Account
		db.Preload("RoleAccounts").Preload("RoleAccounts.Role").Find(&accounts)
		return accounts, nil
	}
}

In models folder, create new go file named role.model.go as below:

package models

import (
	"config"
	"entities"
)

type RoleModel struct {
}

func (roleModel RoleModel) FindAll() ([]entities.Role, error) {
	db, err := config.GetDB()
	if err != nil {
		return nil, err
	} else {
		var roles []entities.Role
		db.Preload("RoleAccounts").Preload("RoleAccounts.Account").Find(&roles)
		return roles, nil
	}
}




In src folder, create new file named main.go as below and use go run main.go command to run program:

package main

import (
	"fmt"
	"models"
)

func main() {
	fmt.Println("List the roles for each account")
	ListRolesForEachAccount()

	fmt.Println("List the account for each role")
	ListAccountsForEachRole()
}

func ListRolesForEachAccount() {
	var accountModel models.AccountModel
	accounts, _ := accountModel.FindAll()
	for _, account := range accounts {
		fmt.Println(account.ToString())
		fmt.Println("Roles: ", len(account.RoleAccounts))
		fmt.Println("Languages")
		for _, roleAccount := range account.RoleAccounts {
			fmt.Println(roleAccount.Role.ToString())
			fmt.Println("========================")
		}
		fmt.Println("----------------------")
	}
}

func ListAccountsForEachRole() {
	var roleModel models.RoleModel
	roles, _ := roleModel.FindAll()
	for _, role := range roles {
		fmt.Println(role.ToString())
		fmt.Println("Roles: ", len(role.RoleAccounts))
		fmt.Println("Roles")
		for _, roleAccount := range role.RoleAccounts {
			fmt.Println(roleAccount.Account.ToString())
			fmt.Println("========================")
		}
		fmt.Println("----------------------")
	}
}
List the roles for each account
id: 1
username: acc1
Roles:  3
Languages
id: 1
name: Role 1
========================
id: 2
name: Role 2
========================
id: 3
name: Role 3
========================
----------------------
id: 2
username: acc2
Roles:  2
Languages
id: 1
name: Role 1
========================
id: 2
name: Role 2
========================
----------------------
id: 3
username: acc3
Roles:  1
Languages
id: 3
name: Role 3
========================
----------------------

List the account for each role
id: 1
name: Role 1
Roles:  2
Roles
id: 1
username: acc1
========================
id: 2
username: acc2
========================
----------------------
id: 2
name: Role 2
Roles:  2
Roles
id: 1
username: acc1
========================
id: 2
username: acc2
========================
----------------------
id: 3
name: Role 3
Roles:  2
Roles
id: 1
username: acc1
========================
id: 3
username: acc3
========================
----------------------