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:

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

Exec

Run sql query and return sql.Result object

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

QueryRow And QueryRows

API:

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

They will use the returned values to initiate container。

Example:

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

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:

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

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:

  1. SetArgs(...interface{}) RawSeter

Example:

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

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

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

Values / ValuesList / ValuesFlat

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

More details refer：

RowsToMap

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

SQL query results

name value
total 100
found 200

map rows results to map

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

RowsToStruct

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

SQL query results

name value
total 100
found 200

map rows results to struct

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

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.

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