ecodeclub

# Raw Query

Most of the time, you should not use raw queries. Raw queries should only be considered when there is no other choice.

  • Using Raw SQL to query doesn't require an ORM definition
  • Multiple databases support ? as placeholders and auto convert.
  • The params of query support Model Struct, Slice and Array

Example:

o := orm.NewOrm()
ids := []int{1, 2, 3}
var r RawSter
r = o.Raw("SELECT name FROM user WHERE id IN (?, ?, ?)", ids)
1
2
3
4

# Exec

Run sql query and return sql.Result (opens new window) object

res, err := o.Raw("UPDATE user SET name = ?", "your").Exec()
if err == nil {
num, _ := res.RowsAffected()
fmt.Println("mysql row affected nums: ", num)
}
1
2
3
4
5

# QueryRow And QueryRows

API:

QueryRow(containers ...interface{}) error
QueryRows(containers ...interface{}) (int64, error)
1
2

They will use the returned values to initiate container。

Example:

var name string
var id int
// id==2 name=="slene"
dORM.Raw("SELECT 'id','name' FROM `user`").QueryRow(&id,&name)
1
2
3
4

In this example, QueryRow will query to get two columns and only one row. In this case, the values of the two columns are assigned to id and name respectively.

QueryRows Example:

var ids []int
var names []int
query = "SELECT 'id','name' FROM `user`"
// ids=>{1,2},names=>{"nobody","slene"}
num, err = dORM.Raw(query).QueryRows(&ids,&names)
1
2
3
4
5

Similarly, QueryRows is also returned by column, so you can notice that in the example we have declared two slices corresponding to the columns id and name respectively。

# SetArgs

Changing args param in Raw(sql, args...) can return a new RawSeter:

SetArgs(...interface{}) RawSeter
1

Example:

var name string
var id int
query := "SELECT 'id','name' FROM `user` WHERE `id`=?"
// id==2 name=="slene"
// 等效于"SELECT 'id','name' FROM `user` WHERE `id`=1"
dORM.Raw(query).SetArgs(1).QueryRow(&id,&name)
1
2
3
4
5
6

It can also be used in a single sql statement, reused, replacing parameters and then executed.

res, err := r.SetArgs("arg1", "arg2").Exec()
res, err := r.SetArgs("arg1", "arg2").Exec()
1
2
3

# Values / ValuesList / ValuesFlat

	Values(container *[]Params, cols ...string) (int64, error)
	ValuesList(container *[]ParamsList, cols ...string) (int64, error)
	ValuesFlat(container *ParamsList, cols ...string) (int64, error)
1
2
3

More details refer：

# RowsToMap

RowsToMap(result *Params, keyCol, valueCol string) (int64, error)
1

SQL query results

name value
total 100
found 200

map rows results to map

res := make(orm.Params)
nums, err := o.Raw("SELECT name, value FROM options_table").RowsToMap(&res, "name", "value")
// res is a map[string]interface{}{
//	"total": 100,
//	"found": 200,
// }
1
2
3
4
5
6

# RowsToStruct

RowsToStruct(ptrStruct interface{}, keyCol, valueCol string) (int64, error)
1

SQL query results

name value
total 100
found 200

map rows results to struct

type Options struct {
	Total int
	Found int
}

res := new(Options)
nums, err := o.Raw("SELECT name, value FROM options_table").RowsToStruct(res, "name", "value")
fmt.Println(res.Total) // 100
fmt.Println(res.Found) // 200
1
2
3
4
5
6
7
8
9

support name conversion: snake -> camel, eg: SELECT user_name ... to your struct field UserName.

# Prepare

Prepare once and exec multiple times to improve the speed of batch execution.

p, err := o.Raw("UPDATE user SET name = ? WHERE name = ?").Prepare()
res, err := p.Exec("testing", "slene")
res, err  = p.Exec("testing", "astaxie")
...
...
p.Close() // Don't forget to close the prepare statement.
1
2
3
4
5
6

← Relationships →