Fetch Data using Many To Many Relationship in GORM

Install Libraries

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 Database

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

--
-- Table structure for table `language`
--

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

--
-- Dumping data for table `language`
--

INSERT INTO `language` (`name`) VALUES
('Language 1'),
('Language 2'),
('Language 3');

--
-- Table structure for table `user`
--

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

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`username`, `password`, `full_name`) VALUES
('user1', '123', 'User 1'),
('user2', '123', 'User 2'),
('user3', '123', 'User 3'),
('acc4', '123', 'User 4'),
('acc5', '123', 'User 5'),
('acc6', '123', 'User 6');

--
-- Table structure for table `user_language`
--

CREATE TABLE `user_language` (
  `user_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
   PRIMARY KEY (`user_id`,`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_language`
--

INSERT INTO `user_language` (`user_id`, `language_id`) VALUES
(1, 1),
(2, 1),
(4, 1),
(5, 1),
(6, 1),
(1, 2),
(2, 2),
(3, 2),
(1, 3),
(3, 3),
(4, 3),
(5, 3),
(6, 3);

Structure of Language Table

Data of Language Table

Structure of User Table

Data of User Table

Structure of User_Language Table

Data of User_Language Table

Create Entities

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

User Entity

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

package entities

import "fmt"

type User struct {
	Id        int `gorm:"primary_key, AUTO_INCREMENT"`
	Username  string
	Password  string
	FullName  string     `gorm:"column:full_name"`
	Languages []Language `gorm:"many2many:user_language"`
}

func (user *User) TableName() string {
	return "user"
}

func (user User) ToString() string {
	return fmt.Sprintf("id: %d\nusername: %s\nfull name: %s", user.Id, user.Username, user.FullName)
}

Language Entity

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

package entities

import "fmt"

type Language struct {
	Id    int `gorm:"primary_key, AUTO_INCREMENT"`
	Name  string
	Users []User `gorm:"many2many:user_language"`
}

func (language *Language) TableName() string {
	return "language"
}

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

Create Config

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
}

Create Models

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

Language Model

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

package models

import (
	"config"
	"entities"
)

type LanguageModel struct {
}

func (languageModel LanguageModel) FindAll() ([]entities.Language, error) {
	db, err := config.GetDB()
	if err != nil {
		return nil, err
	} else {
		var languages []entities.Language
		db.Preload("Users").Find(&languages)
		return languages, nil
	}
}

User Model

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

package models

import (
	"config"
	"entities"
)

type UserModel struct {
}

func (userModel UserModel) FindAll() ([]entities.User, error) {
	db, err := config.GetDB()
	if err != nil {
		return nil, err
	} else {
		var users []entities.User
		db.Preload("Languages").Find(&users)
		return users, nil
	}
}

Structure of Project

Run Application

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 languages for each user")
	ListLanguagesForEachUser()

	fmt.Println("List the users for each language")
	ListUsersForEachLanguage()
}

func ListLanguagesForEachUser() {
	var userModel models.UserModel
	users, _ := userModel.FindAll()
	for _, user := range users {
		fmt.Println(user.ToString())
		fmt.Println("Languages: ", len(user.Languages))
		if len(user.Languages) > 0 {
			for _, language := range user.Languages {
				fmt.Println(language.ToString())
				fmt.Println("========================")
			}
		}
		fmt.Println("-----------------------------")
	}
}

func ListUsersForEachLanguage() {
	var languageModel models.LanguageModel
	languages, _ := languageModel.FindAll()
	for _, language := range languages {
		fmt.Println(language.ToString())
		fmt.Println("Users: ", len(language.Users))
		if len(language.Users) > 0 {
			for _, user := range language.Users {
				fmt.Println(user.ToString())
				fmt.Println("========================")
			}
		}
		fmt.Println("-----------------------------")
	}
}

Output

List the languages for each user
id: 1
username: user1
full name: User 1
Languages:  3
id: 1
name: Language 1
========================
id: 2
name: Language 2
========================
id: 3
name: Language 3
========================
-----------------------------
id: 2
username: user2
full name: User 2
Languages:  2
id: 1
name: Language 1
========================
id: 2
name: Language 2
========================
-----------------------------
id: 3
username: user3
full name: User 3
Languages:  2
id: 2
name: Language 2
========================
id: 3
name: Language 3
========================
-----------------------------
id: 4
username: acc4
full name: User 4
Languages:  2
id: 1
name: Language 1
========================
id: 3
name: Language 3
========================
-----------------------------
id: 5
username: acc5
full name: User 5
Languages:  2
id: 1
name: Language 1
========================
id: 3
name: Language 3
========================
-----------------------------
id: 6
username: acc6
full name: User 6
Languages:  2
id: 1
name: Language 1
========================
id: 3
name: Language 3
========================
-----------------------------

List the users for each language
id: 1
name: Language 1
Users:  5
id: 1
username: user1
full name: User 1
========================
id: 2
username: user2
full name: User 2
========================
id: 4
username: acc4
full name: User 4
========================
id: 5
username: acc5
full name: User 5
========================
id: 6
username: acc6
full name: User 6
========================
-----------------------------
id: 2
name: Language 2
Users:  3
id: 1
username: user1
full name: User 1
========================
id: 2
username: user2
full name: User 2
========================
id: 3
username: user3
full name: User 3
========================
-----------------------------
id: 3
name: Language 3
Users:  5
id: 1
username: user1
full name: User 1
========================
id: 3
username: user3
full name: User 3
========================
id: 4
username: acc4
full name: User 4
========================
id: 5
username: acc5
full name: User 5
========================
id: 6
username: acc6
full name: User 6
========================
-----------------------------