AdSense

網頁

2022/12/24

Golang GORM 條件查詢 select where query

Go使用GORM ORM套件做SELECT ... WHERE依條件查詢的方式如下。


範例環境：

  • Go 1.19
  • GORM 2.0 (gorm.io/gorm v1.24.2)
  • PostgreSQL 14


事前要求

參考「Golang GORM PostgreSQL基本設定」設定資料庫及安裝GORM套件。


範例

GORM使用gorm.DB.Find(dest interface{}, conds ...interface{})做select query查詢。
第一個參數dest interface{}為儲存查詢結果的model or model slice pointer物件；
第二個可變參數conds ...interface{}為where條件與值。


Inline Conditions

使用gorm.DB.Find設定查詢條件的方式稱為inline condition。

emp := Employee{}
db.Find(&emp, "name = ? AND age = ?", "john", 33) // SELECT * FROM employee WHERE name = 'john' AND age = 33;


Where Conditions

或可另外用gorm.DB.Where(query interface{}, args ...interface{})設定查詢條件與值。

emp := Employee{}
db.Where("name = ? AND age = ?", "john", 33).Find(&emp) // SELECT * FROM employee WHERE name = 'john' AND age = 33;


Map Conditions

Where條件除了用字串及參數表示，也可用map替代稱為map conditions；map的key為查詢欄位，值為查詢欄位的值。這也適用於inline condition。

emp := Employee{}

// use map as where conditions
conditionMap := map[string]interface{}{
    "name": "john",
}
db.Where(conditionMap).Find(&emp) // SELECT * FROM employee WHERE name = 'john';


Struct Conditions

Where條件也可用struct model替代稱為struct conditions；model的屬性為查詢欄位，值為查詢欄位的值。這也適用於inline condition。

emp := Employee{}

// use struct model as where conditions
conditionStruct := Employee{
    Name: "john",
}
db.Where(conditionStruct).Find(&emp) // SELECT * FROM employee WHERE name = 'john';


Primary Key Condition

注意gorm.DB.Find的回傳model中若primary key屬性有值，則除了原本的查詢條件還會加上primary key條件及排序。

emp := Employee{ID: 2}                  // model has primary key field value
db.Where("name = ?", "john").Find(&emp) // SELECT * FROM employee WHERE name = 'john' AND id = 2 ORDER BY id LIMIT 1;


Multiple Results

使用model slice接收多個查詢結果。

// select multiple results
var emps []Employee                 // use model slice for multiple results
db.Where("age > ?", 30).Find(&emps) // SELECT * FROM employee WHERE age > 30;

以下是where查詢的一些簡單範例。

main.go

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/schema"
)

type Employee struct {
    ID        int64     // primary key, column name is `id`
    Name      string    // column name is `name`
    Age       int       // column name is `age`
    CreatedAt time.Time // column name is `created_at`
}

const (...)

func getGormDB() *gorm.DB {...}

func main() {
    db := getGormDB()

    // select with where conidtions
    emp := Employee{}                       // use struct model for single result
    db.Where("name = ?", "john").Find(&emp) // SELECT * FROM employee WHERE name = 'john';
    fmt.Println(emp)

    db.Where("name = ? AND age = ?", "john", 33).Find(&emp) // SELECT * FROM employee WHERE name = 'john' AND age = 33;
    fmt.Println(emp)                                        // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    // use map as where conditions
    conditionMap := map[string]interface{}{
        "name": "john",
    }
    db.Where(conditionMap).Find(&emp) // SELECT * FROM employee WHERE name = 'john';
    fmt.Println(emp)                  // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    // use struct model as where conditions
    conditionStruct := Employee{
        Name: "john",
    }
    db.Where(conditionStruct).Find(&emp) // SELECT * FROM employee WHERE name = 'john';
    fmt.Println(emp)                     // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    // inline select with where conidtion
    db.Find(&emp, "name = ?", "john") // SELECT * FROM employee WHERE name = 'john';
    fmt.Println(emp)                  // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    db.Find(&emp, "name = ? AND age = ?", "john", 33) // SELECT * FROM employee WHERE name = 'john' AND age = 33;
    fmt.Println(emp)                                  // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    // inline select with map conditions
    db.Find(&emp, conditionMap) // SELECT * FROM employee WHERE name = 'john';
    fmt.Println(emp)            // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    // inline select with struct model conditions
    db.Find(&emp, conditionStruct) // SELECT * FROM employee WHERE name = 'john';
    fmt.Println(emp)               // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    // select with where condition and primary key
    emp = Employee{ID: 2}                   // model has primary key field value
    db.Where("name = ?", "john").Find(&emp) // SELECT * FROM employee WHERE name = 'john' AND id = 2 ORDER BY id LIMIT 1;
    fmt.Println(emp)                        // {1 john 33 2022-11-29 18:44:54.114161 +0000 UTC}

    // select multiple results
    var emps []Employee                 // use model slice for multiple results
    db.Where("age > ?", 30).Find(&emps) // SELECT * FROM employee WHERE age > 30;
    fmt.Println(emps)                   // [{3 tony 45 2022-12-22 22:05:09.83327 +0000 UTC} {1 john 33 2022-12-22 21:56:37.061419 +0000 UTC}]

    // select with where between
    db.Where("age BETWEEN ? AND ? ", 18, 30).Find(&emps) // SELECT * FROM employee WHERE age BETWEEN 18 AND 30;
    fmt.Println(emps)                                    // [{2 mary 28 2022-12-22 21:56:37.061419 +0000 UTC}]

    // select with where order by
    db.Where("age > ?", 30).Order("age asc").Find(&emps) // SELECT * FROM employee WHERE age > 30 ORDERY BY age DESC;
    fmt.Println(emps)                                    // [{1 john 33 2022-12-22 21:56:37.061419 +0000 UTC} {3 tony 45 2022-12-22 22:05:09.83327 +0000 UTC}]

}

github。



標籤： ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

AdSense