How to write my own mysql CRUD code?

[pkunight]

英文/English:
Let's say I have a table structure written in the user.sql, then I execute this command:
goctl model mysql ddl --src user.sql --dir .

And I'll get those files:
usermodel.go
usermodel_gen.go
var.go

There are only four methods in usermodel_gen.go: Insert(), FindOne(), Update(), Delete(). and the file says // Code generated by goctl. DO NOT EDIT.

But I didn't find any documentation on how to write my own CRUD method like FindOneByXXX() or FindAllByXXX()

Then where should I write my CRUD method so it can be generated into usermodel_gen.go file?

中文/Chinese:
假设我有一个数据表的结构写在user.sql中, 当我执行以下命令:
goctl model mysql ddl --src user.sql --dir .

我会获得如下几个文件:
usermodel.go
usermodel_gen.go
var.go

只有4个标准方法在usermodel_gen.go文件中: Insert(), FindOne(), Update(), Delete(). 同时这个文件开头写着 // Code generated by goctl. DO NOT EDIT.
但是我没找到任何文档去说明我该在哪里写自己的CRUD代码, 比如 FindOneByXXX() or FindAllByXXX()

那么我应该在哪里写我自己的CRUD代码, 让他能被自动生成到 usermodel_gen.go file文件中去?

[kesonan]

The generated file which not end with suffix _gen.go has already marked.

[jsonMark]

英文/English: Let's say I have a table structure written in the user.sql, then I execute this command: goctl model mysql ddl --src user.sql --dir .

And I'll get those files: usermodel.go usermodel_gen.go var.go

There are only four methods in usermodel_gen.go: Insert(), FindOne(), Update(), Delete(). and the file says // Code generated by goctl. DO NOT EDIT.

But I didn't find any documentation on how to write my own CRUD method like FindOneByXXX() or FindAllByXXX()

Then where should I write my CRUD method so it can be generated into usermodel_gen.go file?

中文/Chinese: 假设我有一个数据表的结构写在user.sql中, 当我执行以下命令: goctl model mysql ddl --src user.sql --dir .

我会获得如下几个文件: usermodel.go usermodel_gen.go var.go

只有4个标准方法在usermodel_gen.go文件中: Insert(), FindOne(), Update(), Delete(). 同时这个文件开头写着 // Code generated by goctl. DO NOT EDIT. 但是我没找到任何文档去说明我该在哪里写自己的CRUD代码, 比如 FindOneByXXX() or FindAllByXXX()

那么我应该在哪里写我自己的CRUD代码, 让他能被自动生成到 usermodel_gen.go file文件中去?

我们自己修改了很多版本，最终还是在tools/goctl/model/sql/gen/gen.go文件的genModel方法新增一套other模版最爽。 otherCode, otherCodeMethod, err := genOtherFunc(table, withCache, g.isPostgreSql) if err != nil { return "", err }

然后复制这个根据自己需要修改：

func genOtherFunc(table Table, withCache, postgreSql bool) (string, string, error) {
	keySet := collection.NewSet()
	keyVariableSet := collection.NewSet()
	keySet.AddStr(table.PrimaryCacheKey.DataKeyExpression)
	keyVariableSet.AddStr(table.PrimaryCacheKey.KeyLeft)
	for _, key := range table.UniqueCacheKey {
		keySet.AddStr(key.DataKeyExpression)
		keyVariableSet.AddStr(key.KeyLeft)
	}
	keys := keySet.KeysStr()
	sort.Strings(keys)
	keyVars := keyVariableSet.KeysStr()
	sort.Strings(keyVars)

	expressions := make([]string, 0)
	expressionValues := make([]string, 0)
	var count int
	for _, field := range table.Fields {
		camel := util.SafeString(field.Name.ToCamel())
		if table.isIgnoreColumns(field.Name.Source()) {
			continue
		}

		if field.Name.Source() == table.PrimaryKey.Name.Source() {
			if table.PrimaryKey.AutoIncrement {
				continue
			}
		}

		count += 1
		if postgreSql {
			expressions = append(expressions, fmt.Sprintf("$%d", count))
		} else {
			expressions = append(expressions, "?")
		}
		expressionValues = append(expressionValues, "data."+camel)
	}

	camel := table.Name.ToCamel()
	text, err := pathx.LoadTemplate(category, insertTemplateFile, template.Insert)
	if err != nil {
		return "", "", err
	}

	output, err := util.With("insert").
		Parse(text).
		Execute(map[string]any{
			"withCache":             withCache,
			"upperStartCamelObject": camel,
			"lowerStartCamelObject": stringx.From(camel).Untitle(),
			"expression":            strings.Join(expressions, ", "),
			"expressionValues":      strings.Join(expressionValues, ", "),
			"keys":                  strings.Join(keys, "\n"),
			"keyValues":             strings.Join(keyVars, ", "),
			"data":                  table,
		})
	if err != nil {
		return "", "", err
	}

	// interface method
	text, err = pathx.LoadTemplate(category, insertTemplateMethodFile, template.InsertMethod)
	if err != nil {
		return "", "", err
	}

	insertMethodOutput, err := util.With("insertMethod").Parse(text).Execute(map[string]any{
		"upperStartCamelObject": camel,
		"data":                  table,
	})
	if err != nil {
		return "", "", err
	}

	return output.String(), insertMethodOutput.String(), nil
}```

最后扩展参数	otherCode:   otherCode,
```go

code := &code{
		importsCode: importsCode,
		varsCode:    varsCode,
		typesCode:   typesCode,
		newCode:     newCode,
		insertCode:  insertCode,
		otherCode:   otherCode,
		findCode:    findCode,
		updateCode:  updateCode,
		deleteCode:  deleteCode,
		cacheExtra:  ret.cacheExtra,
		tableName:   tableName,
	}

[Mikaelemmmm]

Perhaps you can refer here: Example: https://github.com/Mikaelemmmm/go-zero-looklook/blob/main/app/order/model/homestayOrderModel_gen.go Template: https://github.com/Mikaelemmmm/go-zero-looklook/tree/main/deploy/goctl

[pkunight]

The generated file which not end with suffix `_gen.go` has already marked.

That's right, but I really need an example instead of a line of comments...

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

It’s amazing, but this feels like a magical change😂😂, I want to know what is the correct way to use the author’s design, there is indeed a saying in usermodel.go that you should implement the interface yourself, I’m going to try it in usermodel.go Write your own sql

[pkunight]

英文/English: Let's say I have a table structure written in the user.sql, then I execute this command: goctl model mysql ddl --src user.sql --dir . And I'll get those files: usermodel.go usermodel_gen.go var.go There are only four methods in usermodel_gen.go: Insert(), FindOne(), Update(), Delete(). and the file says // Code generated by goctl. DO NOT EDIT. But I didn't find any documentation on how to write my own CRUD method like FindOneByXXX() or FindAllByXXX() Then where should I write my CRUD method so it can be generated into usermodel_gen.go file? 中文/Chinese: 假设我有一个数据表的结构写在user.sql中, 当我执行以下命令: goctl model mysql ddl --src user.sql --dir . 我会获得如下几个文件: usermodel.go usermodel_gen.go var.go 只有4个标准方法在usermodel_gen.go文件中: Insert(), FindOne(), Update(), Delete(). 同时这个文件开头写着 // Code generated by goctl. DO NOT EDIT. 但是我没找到任何文档去说明我该在哪里写自己的CRUD代码, 比如 FindOneByXXX() or FindAllByXXX() 那么我应该在哪里写我自己的CRUD代码, 让他能被自动生成到 usermodel_gen.go file文件中去?

我们自己修改了很多版本，最终还是在tools/goctl/model/sql/gen/gen.go文件的genModel方法新增一套other模版最爽。 otherCode, otherCodeMethod, err := genOtherFunc(table, withCache, g.isPostgreSql) if err != nil { return "", err }

然后复制这个根据自己需要修改：

func genOtherFunc(table Table, withCache, postgreSql bool) (string, string, error) {
	keySet := collection.NewSet()
	keyVariableSet := collection.NewSet()
	keySet.AddStr(table.PrimaryCacheKey.DataKeyExpression)
	keyVariableSet.AddStr(table.PrimaryCacheKey.KeyLeft)
	for _, key := range table.UniqueCacheKey {
		keySet.AddStr(key.DataKeyExpression)
		keyVariableSet.AddStr(key.KeyLeft)
	}
	keys := keySet.KeysStr()
	sort.Strings(keys)
	keyVars := keyVariableSet.KeysStr()
	sort.Strings(keyVars)

	expressions := make([]string, 0)
	expressionValues := make([]string, 0)
	var count int
	for _, field := range table.Fields {
		camel := util.SafeString(field.Name.ToCamel())
		if table.isIgnoreColumns(field.Name.Source()) {
			continue
		}

		if field.Name.Source() == table.PrimaryKey.Name.Source() {
			if table.PrimaryKey.AutoIncrement {
				continue
			}
		}

		count += 1
		if postgreSql {
			expressions = append(expressions, fmt.Sprintf("$%d", count))
		} else {
			expressions = append(expressions, "?")
		}
		expressionValues = append(expressionValues, "data."+camel)
	}

	camel := table.Name.ToCamel()
	text, err := pathx.LoadTemplate(category, insertTemplateFile, template.Insert)
	if err != nil {
		return "", "", err
	}

	output, err := util.With("insert").
		Parse(text).
		Execute(map[string]any{
			"withCache":             withCache,
			"upperStartCamelObject": camel,
			"lowerStartCamelObject": stringx.From(camel).Untitle(),
			"expression":            strings.Join(expressions, ", "),
			"expressionValues":      strings.Join(expressionValues, ", "),
			"keys":                  strings.Join(keys, "\n"),
			"keyValues":             strings.Join(keyVars, ", "),
			"data":                  table,
		})
	if err != nil {
		return "", "", err
	}

	// interface method
	text, err = pathx.LoadTemplate(category, insertTemplateMethodFile, template.InsertMethod)
	if err != nil {
		return "", "", err
	}

	insertMethodOutput, err := util.With("insertMethod").Parse(text).Execute(map[string]any{
		"upperStartCamelObject": camel,
		"data":                  table,
	})
	if err != nil {
		return "", "", err
	}

	return output.String(), insertMethodOutput.String(), nil
}```

最后扩展参数	otherCode:   otherCode,
```go

code := &code{
		importsCode: importsCode,
		varsCode:    varsCode,
		typesCode:   typesCode,
		newCode:     newCode,
		insertCode:  insertCode,
		otherCode:   otherCode,
		findCode:    findCode,
		updateCode:  updateCode,
		deleteCode:  deleteCode,
		cacheExtra:  ret.cacheExtra,
		tableName:   tableName,
	}

厉害了, 不过这个有点魔改的感觉😂😂, 我想知道作者设计的正确使用方式是啥, 在usermodel.go里确实有说法是自己去实现那个interface, 我准备试试就在usermodel.go里写自己的sql了

[fynxiu]

英文/English: Let's say I have a table structure written in the user.sql, then I execute this command: goctl model mysql ddl --src user.sql --dir .

And I'll get those files: usermodel.go usermodel_gen.go var.go

There are only four methods in usermodel_gen.go: Insert(), FindOne(), Update(), Delete(). and the file says // Code generated by goctl. DO NOT EDIT.

But I didn't find any documentation on how to write my own CRUD method like FindOneByXXX() or FindAllByXXX()

Then where should I write my CRUD method so it can be generated into usermodel_gen.go file?

中文/Chinese: 假设我有一个数据表的结构写在user.sql中, 当我执行以下命令: goctl model mysql ddl --src user.sql --dir .

我会获得如下几个文件: usermodel.go usermodel_gen.go var.go

只有4个标准方法在usermodel_gen.go文件中: Insert(), FindOne(), Update(), Delete(). 同时这个文件开头写着 // Code generated by goctl. DO NOT EDIT. 但是我没找到任何文档去说明我该在哪里写自己的CRUD代码, 比如 FindOneByXXX() or FindAllByXXX()

那么我应该在哪里写我自己的CRUD代码, 让他能被自动生成到 usermodel_gen.go file文件中去?

If you want to implement FindOneByXXX, you can do so by setting a unique index, and goctl will automatically generate the corresponding method for you. If you want to add new general methods, you can modify and replace the template file at tools/goctl/model/sql/template/tpl/model.tpl.

[kesonan]

@pkunight Documentation has been updated https://go-zero.dev/docs/tutorials/cli/model#%E6%96%B0%E5%A2%9E%E8%87%AA%E5%AE%9A%E4%B9%89-model-%E6%96%B9%E6%B3%95%E7%A4%BA%E4%BE%8B

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 14 days since being marked as stale.

[NozVine]

https://github.com/zt102545/go-microservices 参考这个项目，基于表结构自动生成比较全面的crud