gorm series - query

Catalog

Gorm query

General query

// Query the first record according to the primary key
db.First(&user)
//// SELECT * FROM users ORDER BY id LIMIT 1;

// Get a record at random
db.Take(&user)
//// SELECT * FROM users LIMIT 1;

// Query the last record according to the primary key
db.Last(&user)
//// SELECT * FROM users ORDER BY id DESC LIMIT 1;

// Query all records
db.Find(&users)
//// SELECT * FROM users;

// Query a specified record (only available if the primary key is integer)
db.First(&user, 10)
//// SELECT * FROM users WHERE id = 10;
package main

import (
	"database/sql"
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

//1. Define the model
type User struct {
	ID int64
	//Name *string  `gorm:"default:'zisefeizhu'"`
	Name sql.NullString `gorm:"default:'zisefeizhu'"`
	Age byte
}

func main() {
	//2. Connect to Mysql database
	db, err := gorm.Open("mysql","root:123456@tcp(127.0.0.1:3306)/db?charset=utf8mb4&parseTime=True&loc=Local")
	if err != nil {
		panic(err)
	}
	defer db.Close()
	//3. Match the model with the table in the database
	db.AutoMigrate(&User{})

	//4. Create a structure instance
	//u := User{Name: sql.NullString{"", false}, Age: 60}
	//FMT. Println (DB. Newrecord) / / return 'true' if the primary key is empty`
	//DB. Debug(). Create / / create a user record in the database
	//fmt.Println(db.NewRecord(u)) / / return 'false' after creating 'user'`

	//query
	//var user User / / declares the model structure variable type user (folder A)
	//Db.first (& user) / / (folder B)
	//user:main.User{ID:1, Name:sql.NullString{String:"zisefeizhu", Valid:true}, Age:0x1e}
	user := new(User)
	db.First(user)
	fmt.Printf("user:%#v\n",user)
	//user:&main.User{ID:1, Name:sql.NullString{String:"zisefeizhu", Valid:true}, Age:0x1e}

	//Query all records
	var users []User
	//users := make([]User, 10)
	db.Find(&users)
	fmt.Printf("user:%#v\n",users)
}

where condition

Normal SQL query
/ Get first matched record
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' limit 1;

// Get all matched records
db.Where("name = ?", "jinzhu").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu';

// <>
db.Where("name <> ?", "jinzhu").Find(&users)
//SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name in ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
//Query where record
	var users []User

	db.Where("name = ?", "zisefeizhu ").Find(&users)
	fmt.Printf("user:%#v\n",users)
Touch & map query
// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// Slice of primary key
db.Where([]int64{20, 21, 22}).Find(&users)
//SELECT * FROM users WHERE id IN (20, 21, 22);

Note: when querying through a structure, GORM will only query through non-zero value fields, which means that if your field value is 0, false or other zero values, it will not be used to build query criteria.

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";

You can use pointers or implement the scanner / value interface to avoid this problem

// Use pointer
type User struct {
  gorm.Model
  Name string
  Age  *int
}

// Using Scanner/Valuer
type User struct {
  gorm.Model
  Name string
  Age  sql.NullInt64  // sql.NullInt64 implements the Scanner/Valuer interface
}

Not condition

Similar to Where

db.Not("name", "jinzhu").First(&user)
//// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;

// Not In
db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)
//// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

// Not In slice of primary keys
db.Not([]int64{1,2,3}).First(&user)
//// SELECT * FROM users WHERE id NOT IN (1,2,3);

db.Not([]int64{}).First(&user)
//// SELECT * FROM users;

// Plain SQL
db.Not("name = ?", "jinzhu").First(&user)
//// SELECT * FROM users WHERE NOT(name = "jinzhu");

// Struct
db.Not(User{Name: "jinzhu"}).First(&user)
//// SELECT * FROM users WHERE name <> "jinzhu";

Or condition

db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
//// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

// Struct
db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
//// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)
//// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

Inline conditions

Similar to Where queries, when inline conditions are used with multiple immediate execution methods, inline conditions are not passed to subsequent immediate execution methods.

// Get records based on primary key (only applicable to shaping primary key)
db.First(&user, 23)
//// SELECT * FROM users WHERE id = 23 LIMIT 1;
// Get the record according to the primary key, if it is a non plastic primary key
db.First(&user, "id = ?", "string_primary_key")
//// SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1;

// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
//// SELECT * FROM users WHERE name = "jinzhu";

db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
//// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

// Struct
db.Find(&users, User{Age: 20})
//// SELECT * FROM users WHERE age = 20;

// Map
db.Find(&users, map[string]interface{}{"age": 20})
//// SELECT * FROM users WHERE age = 20;

Additional Query options

// Add additional SQL operations for query SQL
db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)
//// SELECT * FROM users WHERE id = 10 FOR UPDATE;

FirstOrInit

Get the first matching record, otherwise initialize a new object according to the given condition (only struct and map conditions are supported)

//Query FirstOrInit record
var user User
db.FirstOrInit(&user,User{Name: "princeling"})
fmt.Printf("user:%#v\n",user)
//user:main.User{ID:0, Name: "Little Prince", Age:0x15}
Attrs

If the record is not found, struct is initialized with the parameter

// not found
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
//// SELECT * FROM USERS WHERE name = 'non_existing';
//// user -> User{Name: "non_existing", Age: 20}

db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
//// SELECT * FROM USERS WHERE name = 'non_existing';
//// user -> User{Name: "non_existing", Age: 20}

// find
db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user)
//// SELECT * FROM USERS WHERE name = jinzhu';
//// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
Assign

Whether the record is found or not, the parameter is assigned to struct

// not found
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
//// user -> User{Name: "non_existing", Age: 20}

// find
db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = jinzhu';
// user -> User{Id: 111, Name: "Jinzhu", Age: 30}

FirstOrCreate

Get the first matching record, otherwise create a new record according to the given condition (only struct and map conditions are supported)

// not found
db.FirstOrCreate(&user, User{Name: "non_existing"})
//// INSERT INTO "users" (name) VALUES ("non_existing");
//// user -> User{Id: 112, Name: "non_existing"}

// find
db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user)
//// user -> User{Id: 111, Name: "Jinzhu"}
Attrs

If the record is not found, struct s and records are created with parameters

 // not found
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'non_existing';
//// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
//// user -> User{Id: 112, Name: "non_existing", Age: 20}

// find
db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'jinzhu';
//// user -> User{Id: 111, Name: "jinzhu", Age: 20}
Assign

Whether the record is found or not, the parameter is assigned to struct and saved to the database

// not found
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'non_existing';
//// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
//// user -> User{Id: 112, Name: "non_existing", Age: 20}

// find
db.Where(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'jinzhu';
//// UPDATE users SET age=30 WHERE id = 111;
//// user -> User{Id: 111, Name: "jinzhu", Age: 30}

Advanced query

Subquery

Subquery based on * gorm.expr

db.Where("amount > ?", db.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").SubQuery()).Find(&orders)
// SELECT * FROM "orders"  WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders"  WHERE (state = 'paid')));
Select field

Select, specify the fields you want to retrieve from the database. All fields will be selected by default.

db.Select("name, age").Find(&users)
//// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
//// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
//// SELECT COALESCE(age,'42') FROM users;
sort

Order, which specifies the order in which records are retrieved from the database. Set the second parameter reorder to true to override the previously defined sorting conditions.

db.Order("age desc, name").Find(&users)
//// SELECT * FROM users ORDER BY age desc, name;

// Multi field sorting
db.Order("age desc").Order("name").Find(&users)
//// SELECT * FROM users ORDER BY age desc, name;

// Override sort
db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
//// SELECT * FROM users ORDER BY age desc; (users1)
//// SELECT * FROM users ORDER BY age; (users2)
Number

Limit, which specifies the maximum number of records retrieved from the database.

db.Limit(3).Find(&users)
//// SELECT * FROM users LIMIT 3;

// -1 cancel Limit condition
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
//// SELECT * FROM users LIMIT 10; (users1)
//// SELECT * FROM users; (users2)
deviation

Offset, which specifies the number of records to skip before returning records.

db.Offset(3).Find(&users)
//// SELECT * FROM users OFFSET 3;

// -1 cancel Offset condition
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
//// SELECT * FROM users OFFSET 10; (users1)
//// SELECT * FROM users; (users2)
Total

Count, the total number of records that the model can obtain

db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
//// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
//// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)

db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
//// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

db.Table("deleted_users").Count(&count)
//// SELECT count(*) FROM deleted_users;

db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
//// SELECT count( distinct(name) ) FROM deleted_users; (count)

Note: count must be the last operation of the chain query, because it will overwrite the previous SELECT, but it will not be overwritten if count is used in it

Group & Having
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
for rows.Next() {
  ...
}

// Scan multiple results into a prepared slice of structure
type Result struct {
	Date time.Time
	Total int
}
var rets []Result
db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Scan(&rets)

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
for rows.Next() {
  ...
}

type Result struct {
  Date  time.Time
  Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
Connect

Join, specify connection conditions

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
  ...
}

db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

// Multiple connections and parameters
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)

Pluck

If you want to query multiple columns, you should use Scan

var ages []int64
db.Find(&users).Pluck("age", &ages)

var names []string
db.Model(&User{}).Pluck("name", &names)

db.Table("deleted_users").Pluck("name", &names)

// Want to query multiple fields? Do it in this way.
db.Select("name, age").Find(&users)

scanning

Scan, scan results to a struct

type Result struct {
  Name string
  Age  int
}

var result Result
db.Table("users").Select("name, age").Where("name = ?", "Antonio").Scan(&result)

var results []Result
db.Table("users").Select("name, age").Where("id > ?", 0).Scan(&results)

// Native SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)

Chain operation related

Chain operation

Method Chaining, Gorm implements the chained operation interface, so you can write the code as follows:

// Create a query
tx := db.Where("name = ?", "jinzhu")

// Add more conditions
if someCondition {
  tx = tx.Where("age = ?", 20)
} else {
  tx = tx.Where("age = ?", 30)
}

if yetAnotherCondition {
  tx = tx.Where("active = ?", 1)
}

Query statements are not generated before the immediate execution method is called. With this feature, you can create a function to handle some general logic.

Immediate execution method

Immediate methods, immediate execution methods refer to those methods that generate SQL statements and send them to the database immediately. They are generally CRUD methods, such as:

Create, First, Find, Take, Save, UpdateXXX, Delete, Scan, Row, Rows...
Here is an example of an immediate execution method based on the linked method code above

tx.Find(&user)

The generated SQL statement is as follows:

SELECT * FROM users where name = 'jinzhu' AND age = 30 AND active = 1;

Range

Scope is based on chain operation.
Based on it, you can extract some general logic and write more reusable function libraries.

func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
  return db.Where("amount > ?", 1000)
}

func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
  return db.Where("pay_mode_sign = ?", "C")
}

func PaidWithCod(db *gorm.DB) *gorm.DB {
  return db.Where("pay_mode_sign = ?", "C")
}

func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
  return func (db *gorm.DB) *gorm.DB {
    return db.Scopes(AmountGreaterThan1000).Where("status IN (?)", status)
  }
}

db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
// Find all credit card orders with an amount greater than 1000

db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
// Find all COD orders with an amount greater than 1000

db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
// Find all orders with an amount greater than 1000 and paid or shipped

Multiple immediate execution methods

Multiple Immediate Methods: when Multiple Immediate Methods are used in GORM, the latter will reuse the conditions of the former immediate method (excluding inline conditions).

db.Where("name LIKE ?", "jinzhu%").Find(&users, "id IN (?)", []int{1, 2, 3}).Count(&count)

Generated Sql

SELECT * FROM users WHERE name LIKE 'jinzhu%' AND id IN (1, 2, 3)

SELECT count(*) FROM users WHERE name LIKE 'jinzhu%'

Tags: Go SQL Database MySQL github

Posted on Mon, 27 Apr 2020 02:44:21 -0700 by jklanka