Implement a Tag API service based on Go + MySQL + ES

Tag is a very common feature. This article will use Go + MySQL + ES to implement a 500-plus line Tag API service that supports creating/searching tags, associating tags with entities, and querying a list of tags associated with entities.

Initializing the Environment

MySQL

brew install mysql
Copy the code

ES

Here’s how to launch ES directly from docker:

docker run -d --name elasticsearch -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" elasticsearch
Copy the code

You can use curl to check whether the startup is complete and obtain the version information.

curl localhost:9200
{
  "name" : "5059f2c85a1d"."cluster_name" : "docker-cluster"."cluster_uuid" : "T5EjufvlSdCcZXVDJFi2cA"."version" : {
    "number" : "7.7.1"."build_flavor" : "default"."build_type" : "docker"."build_hash" : "ad56dce891c901a492bb1ee393f12dfff473a423"."build_date" : "The 2020-05-28 T16:30:01. 040088 z"."build_snapshot" : false."lucene_version" : "8.5.1"."minimum_wire_compatibility_version" : "6.8.0"."minimum_index_compatibility_version" : "6.0.0 - beta1"
  },
  "tagline" : "You Know, for Search"
}
Copy the code

Install Elasticsearch with Docker if you want to deploy Elasticsearch with Docker in production deployment.

Design storage structure

MySQL > create test database;

create database test;
use test;
Copy the code

Create table tag_tbl;

CREATE TABLE `tag_tbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Tag_tbl is used to store tags, and notice that we add a unique key to the name field and use hash as the index method. For hash Indexes, see the official documentation: Comparison of B-tree and Hash Indexes.

Create entity_tag_tbl to store the tag associated with the entity:

CREATE TABLE `entity_tag_tbl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `entity_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `entity_id` (`entity_id`,`tag_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Design of the API

Create a label

Request:

POST /api/tag
{
    "name": "your tag name"
}
Copy the code

Response:

{
    "tag_id": 1}Copy the code

Search tag

Request:

GET /api/tag/search
{
    "keyword": "cat"
}
Copy the code

Response:

{
    "matchs": [{"tag_id": 5,
            "name": "cat"
        },
        {
            "tag_id": 6,
            "name": "cat pictures"}}]Copy the code

Associate labels with entities

Request:

POST /api/tag/link_entity
{
    "entity_id": 1,
    "tag_id": 3}Copy the code

Response:

{
    "link_id": 1
}
Copy the code

Query the label list associated with an entity

Request:

GET /api/tag/entity_tags
{
    "entity_id": 1}Copy the code

Response:

{
    "tags": [{"tag_id": 3."name": "Food"}}]Copy the code

coded

Initialization:

mkdir tag-server
cd tag-server
go mod init github.com/3vilive/tag-server
Copy the code

Dependencies will be used for installation:

go get github.com/go-sql-driver/mysql github.com/jmoiron/sqlx github.com/gin-gonic/gin github.com/elastic/go-elasticsearch/v7
Copy the code

Create CMD /api-server/main.go and write scaffolding code:

package main

import (
    "net/http"

    "github.com/gin-gonic/gin"
)

func OnNewTag(c *gin.Context) {
    c.JSON(http.StatusOK, gin.H{
        "tag_id": 0})},func OnSearchTag(c *gin.Context) {
    c.JSON(http.StatusOK, gin.H{
        "matches": []struct{} {},})}func OnLinkEntity(c *gin.Context) {
	c.JSON(http.StatusOK, gin.H{
		"link_id": 0})},func OnEntityTags(c *gin.Context) {
	c.JSON(http.StatusOK, gin.H{
		"tags": []struct{}{},
	})
	return
}

func main(a) {
    r := gin.Default()

    r.POST("/api/tag", OnNewTag)
    r.GET("/api/tag/search", OnSearchTag)
    r.POST("/api/tag/link_entity", OnLinkEntity)
    r.GET("/api/tag/entity_tags", OnEntityTags)

    r.Run(": 9800")}Copy the code

Implement the API for creating labels

Connect to database:

import "github.com/jmoiron/sqlx"
import _ "github.com/go-sql-driver/mysql" // mysql driver

var (
    mysqlDB *sqlx.DB
)

func init(a) {
    mysqlDB = sqlx.MustOpen("mysql"."test:test@tcp(localhost:3306)/test? parseTime=True&loc=Local&multiStatements=true&charset=utf8mb4")}Copy the code

Define the Tag structure:

type Tag struct {
    TagID int    `db:"id"`
    Name  string `db:"name"`
}
Copy the code

Write the logic to create the tag:

// NewTagReqBody Creates the request body for the tag
type NewTagReqBody struct {
    Name string `json:"name"`
}

// OnNewTag Creates a tag
func OnNewTag(c *gin.Context) {
    var reqBody NewTagReqBody
    ifbindErr := c.BindJSON(&reqBody); bindErr ! =nil {
        c.JSON(http.StatusBadRequest, gin.H{
            "status":  http.StatusBadRequest,
            "message": bindErr.Error(),
        })
        return
    }

    // Check whether the tag name passed in is empty
    tagName := strings.TrimSpace(reqBody.Name)
    if tagName == "" {
        c.JSON(http.StatusBadRequest, gin.H{
            "status":  http.StatusBadRequest,
            "message": "invalid name",})return
    }

    var queryTag Tag
    queryErr := mysqlDB.Get(&queryTag, "select id, name from tag_tbl where name = ?", tagName)
    if queryErr == nil {
        // The tag already exists
        c.JSON(http.StatusOK, gin.H{
            "tag_id": queryTag.TagID,
        })
        return
    }

    // An error occurred when querying mysql
    ifqueryErr ! =nil&& queryErr ! = sql.ErrNoRows { c.JSON(http.StatusInternalServerError, gin.H{"status":  http.StatusInternalServerError,
            "message": queryErr.Error(),
        })
        return
    }

    // The tag does not exist
    result, execErr := mysqlDB.Exec("insert into tag_tbl (name) values (?) on duplicate key update created_at = now()", tagName)
    ifexecErr ! =nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "status":  http.StatusInternalServerError,
            "message": execErr.Error(),
        })
        return
    }

    tagID, err := result.LastInsertId()
    iferr ! =nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "status":  http.StatusInternalServerError,
            "message": err.Error(),
        })
        return
    }

    c.JSON(http.StatusOK, gin.H{
        "tag_id": tagID,
    })
}
Copy the code

Start the test:

go run cmd/api-server/main.go
[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached.

[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
 - using env:   export GIN_MODE=release
 - using code:  gin.SetMode(gin.ReleaseMode)

[GIN-debug] POST   /api/tag                  --> main.OnNewTag (3 handlers)
[GIN-debug] POST   /api/tag/search           --> main.OnSearchTag (3 handlers)
[GIN-debug] Listening and serving HTTP on :9800
Copy the code

Create a tag named test:

curl --request POST \
  --url http://localhost:9800/api/tag \
  --header 'content-type: application/json' \
  --data '{ "name": "test" }'
Copy the code

Response:

{
  "tag_id": 1}Copy the code

Create another TAB called tests:

curl --request POST \
  --url http://localhost:9800/api/tag \
  --header 'content-type: application/json' \
  --data '{"name": "test"}'
Copy the code

Response:

{
  "tag_id"2} :Copy the code

Rerun the request to create the test tag:

curl --request POST \
  --url http://localhost:9800/api/tag \
  --header 'content-type: application/json' \
  --data '{ "name": "test" }'
Copy the code

Response:

{
  "tag_id": 1}Copy the code

The test results meet expectations, and the current complete file contents are as follows:

package main

import (
    "database/sql"
    "net/http"
    "strings"

    "github.com/gin-gonic/gin"
    "github.com/jmoiron/sqlx"

    _ "github.com/go-sql-driver/mysql" // mysql driver
)

var (
    mysqlDB *sqlx.DB
)

func init(a) {
    mysqlDB = sqlx.MustOpen("mysql"."test:test@tcp(localhost:3306)/test? parseTime=True&loc=Local&multiStatements=true&charset=utf8mb4")}// Tag Defines the Tag structure
type Tag struct {
    TagID int    `db:"id"`
    Name  string `db:"name"`
}

// NewTagReqBody Creates the request body for the tag
type NewTagReqBody struct {
    Name string `json:"name"`
}

// OnNewTag Creates a tag
func OnNewTag(c *gin.Context) {
    var reqBody NewTagReqBody
    ifbindErr := c.BindJSON(&reqBody); bindErr ! =nil {
        c.JSON(http.StatusBadRequest, gin.H{
            "status":  http.StatusBadRequest,
            "message": bindErr.Error(),
        })
    }

    // Check whether the tag name passed in is empty
    tagName := strings.TrimSpace(reqBody.Name)
    if tagName == "" {
        c.JSON(http.StatusBadRequest, gin.H{
            "status":  http.StatusBadRequest,
            "message": "invalid name",})return
    }

    var queryTag Tag
    queryErr := mysqlDB.Get(&queryTag, "select id, name from tag_tbl where name = ?", tagName)
    if queryErr == nil {
        // The tag already exists
        c.JSON(http.StatusOK, gin.H{
            "tag_id": queryTag.TagID,
        })
        return
    }

    // An error occurred when querying mysql
    ifqueryErr ! =nil&& queryErr ! = sql.ErrNoRows { c.JSON(http.StatusInternalServerError, gin.H{"status":  http.StatusInternalServerError,
            "message": queryErr.Error(),
        })
        return
    }

    // The tag does not exist
    result, execErr := mysqlDB.Exec("insert into tag_tbl (name) values (?) on duplicate key update created_at = now()", tagName)
    ifexecErr ! =nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "status":  http.StatusInternalServerError,
            "message": execErr.Error(),
        })
        return
    }

    tagID, err := result.LastInsertId()
    iferr ! =nil {
        c.JSON(http.StatusInternalServerError, gin.H{
            "status":  http.StatusInternalServerError,
            "message": err.Error(),
        })
        return
    }

    c.JSON(http.StatusOK, gin.H{
        "tag_id": tagID,
    })
}

// OnSearchTag Search tag
func OnSearchTag(c *gin.Context) {
    c.JSON(http.StatusOK, gin.H{
        "matches": []struct{} {},})}func main(a) {
    r := gin.Default()

    r.POST("/api/tag", OnNewTag)
    r.POST("/api/tag/search", OnSearchTag)

    r.Run(": 9800")}Copy the code

Implement an API for searching tags

Import elasticSearch package:

import(... elasticsearch7"github.com/elastic/go-elasticsearch/v7"
)
Copy the code

Declare the esClient variable:

var (
    mysqlDB  *sqlx.DB
    esClient *elasticsearch7.Client
)
Copy the code

Initialize esClient in init function:

func init(a) {
	// Initialize mysql
	mysqlDB = sqlx.MustOpen("mysql"."test:test@tcp(localhost:3306)/test? parseTime=True&loc=Local&multiStatements=true&charset=utf8mb4")

	// Initialize ES
	esConf := elasticsearch7.Config{
		Addresses: []string{"http://localhost:9200"},
	}
	es, err := elasticsearch7.NewClient(esConf)
	iferr ! =nil {
		panic(err)
	}

	res, err := es.Info()
	iferr ! =nil {
		panic(err)
	}

	if res.IsError() {
		panic(res.String())
	}

	esClient = es
}
Copy the code

Add the label to the ES index

In order to search for a label on ES, we need to add the label to the ES index when we add the label.

Modify the Tag structure by adding a JSON Tag and adding a method to convert it to a JSON string:

// Tag Defines the Tag structure
type Tag struct {
	TagID int    `db:"id" json:"tag_id"`
	Name  string `db:"name" json:"name"`
}

// MustToJSON converts the structure to JSON
func (t *Tag) MustToJSON(a) string {
	bs, err := json.Marshal(t)
	iferr ! =nil {
		panic(err)
	}
	return string(bs)
}
Copy the code

Then add a function that reports the Tag to the ES index:

// ReportTagToES
func ReportTagToES(tag *Tag) {
	req := esapi.IndexRequest{
		Index:        "test",
		DocumentType: "tag",
		DocumentID:   strconv.Itoa(tag.TagID),
		Body:         strings.NewReader(tag.MustToJSON()),
		Refresh:      "true",
	}

	resp, err := req.Do(context.Background(), esClient)
	iferr ! =nil {
		log.Printf("ESIndexRequestErr: %s", err.Error())
		return
	}

	defer resp.Body.Close()
	if resp.IsError() {
		log.Printf("ESIndexRequestErr: %s", resp.String())
	} else {
		log.Printf("ESIndexRequestOk: %s", resp.String())
	}
}
Copy the code

Add reporting logic to the bottom of OnNewTag:

func OnNewTag(c *gin.Context){... tagID, err := result.LastInsertId()iferr ! =nil {
		c.JSON(http.StatusInternalServerError, gin.H{
			"status":  http.StatusInternalServerError,
			"message": err.Error(),
		})
		return
	}
    
	// Add to ES index
	newTag := &Tag{TagID: int(tagID), Name: tagName}
	go ReportTagToES(newTag)

	c.JSON(http.StatusOK, gin.H{
		"tag_id": tagID,
	})
}
Copy the code

Restart the service, then test creating a Tag and observe the log:

2020/06/05 11:29:11 ESIndexRequestOk: [201 Created] {"_index":"test"."_type":"tag"."_id":"4"."_version": 1,"result":"created"."forced_refresh":true."_shards": {"total": 2."successful": 1,"failed": 0}."_seq_no": 3."_primary_term": 1}Copy the code

Call the ES API again to verify:

curl -XGET "localhost:9200/test/tag/4"

{"_index":"test"."_type":"tag"."_id":"4"."_version": 1,"_seq_no": 3."_primary_term": 1,"found":true."_source": {"tag_id": 4."name":"Test method"}}
Copy the code

Perfect search logic

Add a SearchTagReqBody structure as the request body for the search tag

type SearchTagReqBody struct {
	Keyword string `json:"keyword"`
}
Copy the code

Add some basic parameters to the OnSearchTag function:

func OnSearchTag(c *gin.Context) {
	var reqBody SearchTagReqBody
	ifbindErr := c.BindJSON(&reqBody); bindErr ! =nil {
		c.JSON(http.StatusBadRequest, gin.H{
			"status":  http.StatusBadRequest,
			"message": bindErr.Error(),
		})
		return
	}

	searchKeyword := strings.TrimSpace(reqBody.Keyword)
	if searchKeyword == "" {
		c.JSON(http.StatusBadRequest, gin.H{
			"status":  http.StatusBadRequest,
			"message": "invalid keyword",})return
	}

	c.JSON(http.StatusOK, gin.H{
		"matches": []struct{} {},})}Copy the code

Add an O structure to alias map[string]interface{}, and add a MustToJSONBytesBuffer() * bytes.buffer method to this structure:

type O map[string]interface{}

func (o *O) MustToJSONBytesBuffer(a) *bytes.Buffer {
	var buf bytes.Buffer
	iferr := json.NewEncoder(&buf).Encode(o); err ! =nil {
		panic(err)
	}

	return &buf
}
Copy the code

This O is defined to make it a little easier to build ES queries later.

Add SearchTagsFromES function to SearchTagsFromES:

func SearchTagsFromES(keyword string) ([]*Tag, error) {
	// Build the query
	query := O{
		"query": O{
			"match_phrase_prefix": O{
				"name":           keyword,
				"max_expansions": 50,
			},
		},
	}
	jsonBuf := query.MustToJSONBytesBuffer()

	// Issue a query request
	resp, err := esClient.Search(
		esClient.Search.WithContext(context.Background()),
		esClient.Search.WithIndex("test"),
		esClient.Search.WithBody(jsonBuf),
	)
	iferr ! =nil {
		return nil, err
	}
	defer resp.Body.Close()

	if resp.IsError() {
		return nil, errors.New(resp.Status())
	}

	js, err := simplejson.NewFromReader(resp.Body)
	iferr ! =nil {
		return nil, err
	}

	hitsJS := js.GetPath("hits"."hits")
	hits, err := hitsJS.Array()
	iferr ! =nil {
		return nil, err
	}

	hitsLen := len(hits)
	if hitsLen == 0 {
		return []*Tag{}, nil
	}

	tags := make([]*Tag, 0.len(hits))
	for idx := 0; idx < hitsLen; idx++ {
		sourceJS := hitsJS.GetIndex(idx).Get("_source")

		tagID, err := sourceJS.Get("tag_id").Int()
		iferr ! =nil {
			return nil, err
		}

		tagName, err := sourceJS.Get("name").String()
		iferr ! =nil {
			return nil, err
		}

		tagEntity := &Tag{TagID: tagID, Name: tagName}
		tags = append(tags, tagEntity)
	}

	return tags, nil
}
Copy the code

Modify the OnSearchTag function to add search logic:

func OnSearchTag(c *gin.Context) {
	var reqBody SearchTagReqBody
	ifbindErr := c.BindJSON(&reqBody); bindErr ! =nil {
		c.JSON(http.StatusBadRequest, gin.H{
			"status":  http.StatusBadRequest,
			"message": bindErr.Error(),
		})
		return
	}

	searchKeyword := strings.TrimSpace(reqBody.Keyword)
	if searchKeyword == "" {
		c.JSON(http.StatusBadRequest, gin.H{
			"status":  http.StatusBadRequest,
			"message": "invalid keyword",})return
	}

	tags, err := SearchTagsFromES(reqBody.Keyword)
	iferr ! =nil {
		c.JSON(http.StatusInternalServerError, gin.H{
			"status":  http.StatusInternalServerError,
			"message": err.Error(),
		})
		return
	}

	c.JSON(http.StatusOK, gin.H{
		"matches": tags,
	})
}
Copy the code

Restart the service, then add a gourmet TAB, then search again:

curl --request GET \
  --url http://localhost:9800/api/tag/search \
  --header 'content-type: application/json' \
  --data '{"keyword": "keyword"}'

// response:

{
  "matches": [{"tag_id": 5,
      "name": "Food"}}]Copy the code

Search for API final results

MySQL > alter table MySQL > alter table MySQL > alter table MySQL > alter table MySQL > alter table MySQL > alter table MySQL

truncate tag_tbl;
Copy the code

Also clean up the ES index:

curl -XDELETE "localhost:9200/test"
Copy the code

Next add a batch of tags:

Food Street Food Festival Food Festival Interesting stories Food festival Three Musketeers Food Heaven food temptation food in China food street what are thereCopy the code

Search for “food” :

{
  "matches": [{"tag_id": 1."name": "Food"
    },
    {
      "tag_id": 2."name": "Food Court"
    },
    {
      "tag_id": 3."name": "Food Festival"
    },
    {
      "tag_id": 6."name": "Food Heaven"
    },
    {
      "tag_id": 4."name": "Food Festival Facts."
    },
    {
      "tag_id": 7."name": "The lure of food."
    },
    {
      "tag_id": 8."name": "Food in China"
    },
    {
      "tag_id": 5."name": "The Three Musketeers of the Food Festival."
    },
    {
      "tag_id": 9."name": "What's in the food court?"}}]Copy the code

Search for “Food Court” :

{
  "matches": [{"tag_id": 2."name": "Food Court"
    },
    {
      "tag_id": 9."name": "What's in the food court?"}}]Copy the code

Search for “Food Festival” :

{
  "matches": [{"tag_id": 3."name": "Food Festival"
    },
    {
      "tag_id": 4."name": "Food Festival Facts."
    },
    {
      "tag_id": 5."name": "The Three Musketeers of the Food Festival."}}]Copy the code

Implement the associate label to entity API

Define the structure of the entity associated Tag:

type EntityTag struct {
	LinkID   int `db:"id" json:"-"`
	EntityID int `db:"entity_id" json:"entity_id"`
	TagID    int `db:"tag_id" json:"tag_id"`
}
Copy the code

Define the request body:

type LinkEntityReqBody struct {
	EntityID int `json:"entity_id"`
	TagID    int `json:"tag_id"`
}
Copy the code

Start writing the logic inside OnLinkEntity, first do the basic parameter verification:

var reqBody LinkEntityReqBody
ifbindErr := c.BindJSON(&reqBody); bindErr ! =nil {
	c.JSON(http.StatusBadRequest, gin.H{
		"status":  http.StatusBadRequest,
		"message": bindErr.Error(),
	})
	return
}

if reqBody.EntityID == 0 || reqBody.TagID == 0 {
	c.JSON(http.StatusBadRequest, gin.H{
		"status":  http.StatusBadRequest,
		"message": "request params error",})return
}
Copy the code

Query whether the label has been associated with the entity, if so, return:

var entityTag EntityTag
queryErr := mysqlDB.Get(
	&entityTag,
	"select id, entity_id, tag_id from entity_tag_tbl where entity_id = ? and tag_id = ?",
	reqBody.EntityID, reqBody.TagID,
)

if queryErr == nil {
	// An association already exists
	c.JSON(http.StatusOK, gin.H{
		"link_id": entityTag.LinkID,
	})
	return
}

ifqueryErr ! = sql.ErrNoRows {// Query error
	c.JSON(http.StatusInternalServerError, gin.H{
		"status":  http.StatusInternalServerError,
		"message": queryErr.Error(),
	})
	return
}
Copy the code

Check whether the Tag exists:

var tag Tag
queryErr = mysqlDB.Get(
	&tag,
	"select id, name from tag_tbl where id = ?",
	reqBody.TagID,
)
ifqueryErr ! =nil {
	ifqueryErr ! = sql.ErrNoRows {// Query error
		c.JSON(http.StatusInternalServerError, gin.H{
			"status":  http.StatusInternalServerError,
			"message": queryErr.Error(),
		})
		return
	}

	// Tag does not exist
	c.JSON(http.StatusNotFound, gin.H{
		"status":  http.StatusNotFound,
		"message": "tag not found",})return
}
Copy the code

Log the association information and return the association ID:

execResult, execErr := mysqlDB.Exec(
	"insert into entity_tag_tbl (entity_id, tag_id) values (?, ?) on duplicate key update created_at = now()",
	reqBody.EntityID, reqBody.TagID,
)
ifexecErr ! =nil {
	// Failed to insert
	c.JSON(http.StatusInternalServerError, gin.H{
		"status":  http.StatusInternalServerError,
		"message": execErr.Error(),
	})
	return
}

linkID, err := execResult.LastInsertId()
iferr ! =nil {
	c.JSON(http.StatusInternalServerError, gin.H{
		"status":  http.StatusInternalServerError,
		"message": err.Error(),
	})
	return
}

c.JSON(http.StatusOK, gin.H{
	"link_id": int(linkID),
})
Copy the code

Restart the service and create some associations:

curl --request POST \
  --url http://localhost:9800/api/tag/link_entity \
  --header 'content-type: application/json' \
  --data '{ "entity_id": 1, "tag_id": 5 }'
Copy the code

Verify this with a database:

mysql> select * from entity_tag_tbl; +----+-----------+--------+---------------------+ | id | entity_id | tag_id | created_at | + - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 | 3 | 2020-06-05 15:03:00 | | 2 | | 1 | 15:39:42 2020-06-05 | | 3 | 1 | | 4 2020-06-05 15:39:47 | | 4 | 1 | 2 | 2020-06-05 15:39:52 | | 5 7 | | 1 | 2020-06-05 15:55:59 | | | | 1 June 5 15:56:01 | 2020-06-05 | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +Copy the code

Implement tag list API for querying entity association

Define the request body for the tag list associated with the query entity:

type EntityTagReqBody struct {
	EntityID int `json:"entity_id"`
}
Copy the code

Write OnEntityTags logic and check parameters as before:

var reqBody EntityTagReqBody
ifbindErr := c.BindJSON(&reqBody); bindErr ! =nil {
	c.JSON(http.StatusBadRequest, gin.H{
		"status":  http.StatusBadRequest,
		"message": bindErr.Error(),
	})
	return
}

if reqBody.EntityID == 0 {
	c.JSON(http.StatusBadRequest, gin.H{
		"status":  http.StatusBadRequest,
		"message": "request params error",})return
}
Copy the code

Query entity associated label:

entityTags := []*EntityTag{}
selectErr := mysqlDB.Select(&entityTags, "select id, entity_id, tag_id from entity_tag_tbl where entity_id = ? order by id", reqBody.EntityID)
ifselectErr ! =nil {
	c.JSON(http.StatusInternalServerError, gin.H{
		"status":  http.StatusInternalServerError,
		"message": selectErr.Error(),
	})
	return
}

if len(entityTags) == 0 {
	c.JSON(http.StatusOK, gin.H{
		"tags": []*Tag{},
	})
	return
}
Copy the code

Query the tag list and return:

tagIDs := make([]int.0.len(entityTags))
tagIndex := make(map[int]int.len(entityTags))
for index, entityTag := range entityTags {
	tagIndex[entityTag.TagID] = index
	tagIDs = append(tagIDs, entityTag.TagID)
}

queryTags, args, err := sqlx.In("select id, name from tag_tbl where id in (?) ", tagIDs)
iferr ! =nil {
	c.JSON(http.StatusInternalServerError, gin.H{
		"status":  http.StatusInternalServerError,
		"message": err.Error(),
	})
	return
}

tags := []*Tag{}
selectErr = mysqlDB.Select(&tags, queryTags, args...)
ifselectErr ! =nil {
	c.JSON(http.StatusInternalServerError, gin.H{
		"status":  http.StatusInternalServerError,
		"message": selectErr.Error(),
	})
	return
}

sort.Slice(tags, func(i, j int) bool {
	return tagIndex[tags[i].TagID] < tagIndex[tags[j].TagID]
})

c.JSON(http.StatusOK, gin.H{
	"tags": tags,
})
Copy the code

Restart the service to test:

curl --request GET \
  --url http://localhost:9800/api/tag/entity_tags \
  --header 'content-type: application/json' \
  --data '{ "entity_id": 1 }'

// response

{
  "tags": [{"tag_id": 3."name": "Food Festival"
    },
    {
      "tag_id": 1,
      "name": "Food"
    },
    {
      "tag_id": 4."name": "Food Festival Facts."
    },
    {
      "tag_id": 2."name": "Food Court"
    },
    {
      "tag_id": 7,
      "name": "The lure of food."
    },
    {
      "tag_id": 5,
      "name": "The Three Musketeers of the Food Festival."}}]Copy the code

The last

The full code can be found on Github:

Github.com/3vilive/bui…

References:

Tags-Database-schemas
Tagsystems-performance-tests
Elasticsearch: The definitive guide

Implement a Tag API service based on Go + MySQL + ES

Initializing the Environment

MySQL

ES

Design storage structure

Design of the API

Create a label

Search tag

Associate labels with entities

Query the label list associated with an entity

coded

Implement the API for creating labels

Implement an API for searching tags

Add the label to the ES index

Perfect search logic

Search for API final results

Implement the associate label to entity API

Implement tag list API for querying entity association

The last

Related Posts

Fresh from the oven! With this interview guide, I finally got the offer from Bytedance!

Command line tool kubectl for kubernetes learning

Content-length, Transfer-Encoding, and Flush methods for web services