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.