Database Basic Operations

The Model function in the github.com/gogf/gf/v2/frame/g package returns a gdb.Model object, providing a set of operations for interacting with the database. The Model function accepts a parameter, which is the table name:

md := g.Model("book")

This returns a Model associated with the book table.

Query Data

One/All/Count/Value/Array/Fields

Query a Single Record

md := g.Model("book")
bk, err := md.One()
if err == nil {
    req.Response.WriteJson(bk)
}

This retrieves the first record from the database. The returned data is of type map[string]*gvar.Var, allowing direct access to each field:

req.Response.WriteJson(bk["name"])  // Access the "name" field in the result

You can use methods of gvar.Var to convert values to the desired types:

bk["name"].String()    // Convert to string type
bk["price"].Float32()  // Convert to float32 type

Specify Query Fields

bk, err := md.Fields("name, price").One()
// Alternatively
bk, err := md.Fields("name", "price").One()

Query Multiple Records

md := g.Model("book")
bk, err := md.All()

This method returns a slice containing all records from the table, allowing iteration over each record:

for _, v := range bk {
    req.Response.Writeln(v)
}

Query Record Count

md := g.Model("book")
count, err := md.Count()

Query a Specific Field of a Record

md := g.Model("book")
name, err := md.Value("name")

Query Specific Column Data

md := g.Model("book")
name, err := md.Array("name")

Max/Min/Sum/Avg

GoFrame provides methods for calculating maximum, minimum, sum, average, etc.:

md := g.Model("book")

max, err := md.Max("price")
min, err := md.Min("price")
sum, err := md.Sum("price")
avg, err := md.Avg("price")

Where/Where*/WhereOr/WhereOr*

When querying data, conditions can be specified using the Where method. Multiple Where calls are connected with AND by default:

Equal To

md := g.Model("book")
books, err := md.Where("id", 1).All()

Not Equal To

For inequality, append a comparison operator after the field name:

md := g.Model("book")
books, err := md.Where("id>", 1).All()

Combining Multiple Conditions

Chain multiple Where calls for multiple conditions connected with AND:

md := g.Model("book")
books, err := md.Where("id>=?", 2).Where("id<?", 4).All()

Where Methods

Refer to the table below for various Where methods and the corresponding SQL expressions generated:

Method	Generated SQL Condition Expression
WhereLT(column, value)	column < value
WhereLTE(column, value)	column <= value
WhereGT(column, value)	column > value
WhereGTE(column, value)	column >= value
WhereBetween(column, min, max)	column BETWEEN min AND max
WhereNotBetween(column, min, max)	column NOT BETWEEN min AND max
WhereLike(column, like)	column LIKE like
WhereIn(column, in)	column IN (in)
WhereNotIn(column, in)	column NOT IN (in)
WhereNot(column, value)	column != value
WhereNull(columns1, columns2... )	columns1 IS NULL AND columns2 IS NULL...
WhereNotNull(columns1, columns2... )	columns1 IS NOT NULL AND columns2 IS NOT NULL...

Example Usage:

md := g.Model("book")
books, err := md.WhereIn("id", g.Array{1, 2, 3}).WhereLike("name", "%data%").All()

// Generates SQL:
// SELECT * FROM `book` WHERE (`id` IN (1,2,3)) AND (`name` LIKE '%data%')

For chaining conditions with OR instead of AND, use the following methods:

WhereOr Methods

Refer to the table below for various WhereOr methods and the corresponding SQL expressions generated:

Method	Generated SQL Condition Expression
WhereOrLT(column, value)	OR (column < value)
WhereOrLTE(column, value)	OR (column <= value)
WhereOrGT(column, value)	OR (column > value)
WhereOrGTE(column, value)	OR (column >= value)
WhereOrBetween(column, min, max)	OR (column BETWEEN min AND max)
WhereOrNotBetween(column, min, max)	OR (column NOT BETWEEN min AND max)
WhereOrLike(column, like)	OR (column LIKE like)
WhereOrIn(column, in)	OR (column IN (in))
WhereOrNotIn(column, in)	OR (column NOT IN (in))
WhereOrNot(column, value)	OR (column != value)
WhereOrNull(columns1, columns2... )	OR (columns1 IS NULL AND columns2 IS NULL...)
WhereOrNotNull(columns1, columns2... )	OR (columns1 IS NOT NULL AND columns2 IS NOT NULL...)

Example Usage:

md := g.Model("book")
books, err := md.WhereIn("id", g.Array{1, 2, 3}).WhereOrLike("name", "%data%").All()

// Generates SQL:
// SELECT * FROM `book` WHERE (`id` IN (1,2,3)) OR (`name` LIKE '%data%')

Group/Order/Order*

Group by Field

md := g.Model("book")
books, err := md.Group("name").All()

Order by Field

md := g.Model("book")
books, err := md.Order("price", "DESC").All()
// Multi-field ordering
books, err := md.Order("price", "DESC").Order("id", "ASC").All()
// Encapsulated ordering methods
books, err := md.OrderDesc("price").OrderAsc("id").All()

Scan

One and All methods return data as Map or Map slices. When querying, the retrieved data may need to be converted to specific data structures for practical use.

The Scan method converts queried data into custom structs or struct arrays. It offers flexibility; the example demonstrates recommended usage:

Convert Queried Data into a Custom Struct

type Book struct {
    Id          uint
    Name        string
    Author      string
    Price       float64
    PublishTime *gtime.Time
}

var book *Book

md := g.Model("book")
err := md.Scan(&book)

Scan maps database field names (underscore style) to corresponding camel case struct members. If they don't match, the member remains nil or zero value. Use orm: tags to specify mappings if necessary:

type Book struct {
    BookId     uint        `orm:"id"`
    BookName   string      `orm:"name"`
    BookAuthor string      `orm:"author"`
    BookPrice  float64     `orm:"price"`
    PubTime    *gtime.Time `orm:"publish_time"`
}

var book *Book

md := g.Model("book")
err := md.Scan(&book)

Struct Arrays

Scan can query a single struct as shown above or an array of structs by passing a slice of structs:

type Book struct {
    Id          uint
    Name        string
    Author      string
    Price       float64
    PublishTime *gtime.Time
}

var books []Book

md := g.Model("book")
err := md.Scan(&books)

The result is an array of Book structs containing multiple records.

For more complex queries, refer to the official documentation on ORM Queries.

Check for Empty Query Results

All

md := g.Model("book")
books, _ := md.All()
if len(books) == 0 {
    g.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}
// Or
if books.IsEmpty() {
    g.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}

One

md := g.Model("book")
book, _ := md.Where("id", 100).One()
if len(book) == 0 {
    g

.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}
// Or
if book.IsEmpty() {
    g.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}

Value

md := g.Model("book")
name, _ := md.Where("id", 10).Value("name")
if name.IsEmpty() {
    g.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}

Array

md := g.Model("book")
names, _ := md.WhereLT("id", 10).Array("name")
if len(names) == 0 {
    g.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}

Scan Struct Object

var book *Book
md := g.Model("book")
md.Scan(&book)
if book == nil {
    g.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}

Scan Struct Array

var books []Book
md := g.Model("book")
md.Scan(&books)
if len(books) == 0 {
    g.RequestFromCtx(ctx).Response.Writeln("Result is empty")
}

Pagination

GoFrame provides the Page method for easy pagination. Simply provide the page number and number of items per page:

md := g.Model("book")
books, err := md.Page(1, 5).All()

The Limit method is also available to limit the number of queried items or specify a custom starting position and data limit:

md := g.Model("book")
// Limit the number of items
books, err := md.Limit(5).All()
// Specify starting position and limit
books, err := md.Limit(3, 5).All()

Insert Data

Insert/Replace/Save

These methods insert one or more records into the database. They differ in how they handle existing primary keys:

Method	Behavior When Primary Key Exists in Database
Insert	Error on primary key conflict
Replace	Replace existing data with provided data
Save	Update existing data with provided data

Insert Single Record

md := g.Model("book")
data := g.Map{
    "id":           8,
    "name":         "Linux Device Drivers",
    "author":       "John Doe",
    "price":        69,
    "publish_time": "2023-10-10",
}
// Insert
result, err := md.Insert(data)
// Replace
result, err := md.Replace(data)
// Save
result, err := md.Save(data)

Alternatively, use the Data method with these operations:

// Insert
result, err := md.Data(data).Insert()
// Replace
result, err := md.Data(data).Replace()
// Save
result, err := md.Data(data).Save()

You can also use structs. Use orm tags for mapping if struct member names don't match table field names:

type Book struct {
    Id      uint
    Name    string
    Author  string
    Price   float64
    PubTime *gtime.Time `orm:"publish_time"`
}

md := g.Model("book")
data := Book{
    Id:      8,
    Name:    "Linux Device Drivers",
    Author:  "John Doe",
    Price:   69.3,
    PubTime: gtime.New("2023-10-10"),
}
result, err := md.Data(data).Save()

Batch Insert Data

These methods can also handle batch inserts:

data := g.List{
    g.Map{
        "name":         "Linux Device Drivers",
        "author":       "John Doe",
        "price":        69.3,
        "publish_time": gtime.New("2023-10-10"),
    },
    g.Map{
        "name":         "Linux Device Drivers",
        "author":       "John Doe",
        "price":        69.3,
        "publish_time": gtime.New("2023-10-10"),
    },
    g.Map{
        "name":         "Linux Device Drivers",
        "author":       "John Doe",
        "price":        69.3,
        "publish_time": gtime.New("2023-10-10"),
    },
}

result, err := md.Data(data).Save()

For structs, use g.Array or g.Slice instead of g.List.

InsertAndGetId

Insert data and return the auto-increment ID:

data := g.Map{
    "name":         "Linux Device Drivers",
    "author":       "John Doe",
    "price":        69.3,
    "publish_time": gtime.New("2023-10-10"),
}

result, err := md.Data(data).InsertAndGetId()

gdb.Raw

For fields that require SQL operations like CURRENT_DATE() for publish_time, use Raw:

data := g.Map{
    "name":         "Linux Device Drivers",
    "author":       "John Doe",
    "price":        69.3,
    "publish_time": gdb.Raw("CURRENT_DATE()"),
}

result, err := md.Data(data).InsertAndGetId()

Update Data

Update

data := g.Map{
    "author": "Zheng Qiangqiang",
    "price":  69.333,
}

result, err := md.Where("author", "Zheng Qiang").Update(data)

You can also use Data for updating:

data := g.Map{
    "author": "Zheng Qiangqiang",
    "price":  69.333,
}

result, err := md.Where("author", "Zheng Qiang").Data(data).Update()

Increment/Decrement

Used to increment/decrement specified fields by a given value:

result, err := md.WhereBetween("id", 7, 10).Increment("price", 2.5)
result, err := md.WhereBetween("id", 7, 10).Decrement("price", 1.5)

Delete Data

result, err := md.WhereGT("id", 10).Delete()