Advance query

ORM uses QuerySeter to organize query, every method that returns QuerySeter will give you a new QuerySeter object.

Basic usage:

o := orm.NewOrm()

// Get QuerySeter object, table name is user.
qs := o.QueryTable("user")

// Or use object as table name.
user := NewUser()
qs = o.QueryTable(user) // 返回 QuerySeter

expr

expr describes fields and SQL operators in QuerySeter.

Fields organized according to relation of tables. For example, User has a outside key Profile, so if you want to use Profile.Age as the condition, you have to the expression Profile__Age. Note that the saperator is double under lines __. You can append operations of SQL to expr except you use expr to describe fields. For example, Profile__Age__gt represents condition query Profile.Age > 18.

Comments describes SQL statements that similar to the expr, not the exactly generated results.

qs.Filter("id", 1) // WHERE id = 1
qs.Filter("profile__age", 18) // WHERE profile.age = 18
qs.Filter("Profile__Age", 18) // Both table and struct field name are acceptable.
qs.Filter("profile__age", 18) // WHERE profile.age = 18
qs.Filter("profile__age__gt", 18) // WHERE profile.age > 18
qs.Filter("profile__age__gte", 18) // WHERE profile.age >= 18
qs.Filter("profile__age__in", 18, 20) // WHERE profile.age IN (18, 20)

qs.Filter("profile__age__in", 18, 20).Exclude("profile__lt", 1000)
// WHERE profile.age IN (18, 20) AND NOT profile_id < 1000

Operators

Currently supported operators:

• exact / iexact equals
• contains / icontains contains
• gt / gte greater / greater or equal
• lt / lte less / less or equal
• startswith / istartswith starts with
• endswith / iendswith ends with
• in
• isnull

Starts with i means that’s not case sensitive.

exact

Default values of Filter / Exclude / Condition expr:

qs.Filter("name", "slene") // WHERE name = 'slene'
qs.Filter("name__exact", "slene") // WHERE name = 'slene'
// Use `=` to match, data table collation decides whether it's case sensitive or not.
qs.Filter("profile", nil) // WHERE profile_id IS NULL

iexact

qs.Filter("name__iexact", "slene")
// WHERE name LIKE 'slene'
// Not case sensitive, 'Slene' 'sLENE' are both acceptable.

contains

qs.Filter("name__contains", "slene")
// WHERE name LIKE BINARY '%slene%'
// Case sensitive, matches all characters that contain "slene".

icontains

qs.Filter("name__icontains", "slene")
// WHERE name LIKE '%slene%'
// Not case sensitive, 'im Slene', 'im sLENE' are both acceptable.

in

qs.Filter("profile__age__in", 17, 18, 19, 20)
// WHERE profile.age IN (17, 18, 19, 20)

gt / gte

qs.Filter("profile__age__gt", 17)
// WHERE profile.age > 17

qs.Filter("profile__age__gte", 18)
// WHERE profile.age >= 18

lt / lte

qs.Filter("profile__age__lt", 17)
// WHERE profile.age < 17

qs.Filter("profile__age__lte", 18)
// WHERE profile.age <= 18

startswith

qs.Filter("name__startswith", "slene")
// WHERE name LIKE BINARY 'slene%'
// Case sensitive, matches all strings that start with 'slene'.

istartswith

qs.Filter("name__istartswith", "slene")
// WHERE name LIKE 'slene%'
// Not case sensitive, matches all strings start with something like 'slene', 'Slene'.

endswith

qs.Filter("name__endswith", "slene")
// WHERE name LIKE BINARY '%slene'
// Case sensitive, matches all strings that end with 'slene'.

iendswith

qs.Filter("name__startswith", "slene")
// WHERE name LIKE '%slene'
// Not case sensitive, matches all strings end with something like 'slene', 'Slene'.

isnull

qs.Filter("profile__isnull", true)
qs.Filter("profile_id__isnull", true)
// WHERE profile_id IS NULL

qs.Filter("profile__isnull", false)
// WHERE profile_id IS NOT NULL

Advanced query interface

QuerySeter is the advanced query interface, let’s talk a look:

• type QuerySeter interface {
  • Filter(string, …interface{}) QuerySeter
  • Exclude(string, …interface{}) QuerySeter
  • SetCond(*Condition) QuerySeter
  • Limit(int, …int64) QuerySeter
  • Offset(int64) QuerySeter
  • OrderBy(…string) QuerySeter
  • RelatedSel(…interface{}) QuerySeter
  • Count() (int64, error)
  • Update(Params) (int64, error)
  • Delete() (int64, error)
  • PrepareInsert() (Inserter, error)
  • All(interface{}) (int64, error)
  • One(Modeler) error
  • Values(*[]Params, …string) (int64, error)
  • ValuesList(*[]ParamsList, …string) (int64, error)
  • ValuesFlat(*ParamsList, string) (int64, error)

• }

• Every API that returns QuerySeter will returns a new QuerySeter that no relationship with the old one.

• Use Filter and Exclude to do commonly conditional query which contains two kinds of filter rules: contain and exclude.

Filter

Contain filter example, use AND to connect multiple filters:

qs.Filter("profile__isnull", true).Filter("name", "slene")
// WHERE profile_id IS NULL AND name = 'slene'

Exclude

Exclude filter example, use NOT to exclude conditions, and AND to connect multiple Excludes:

qs.Exclude("profile__isnull", true).Filter("name", "slene")
// WHERE NOT profile_id IS NULL AND name = 'slene'

SetCond

Customized conditional expression:

cond := NewCondition()
cond1 := cond.And("profile__isnull", false).AndNot("status__in", 1).Or("profile__age__gt", 2000)

qs := orm.QueryTable("user")
qs = qs.SetCond(cond1)
// WHERE ... AND ... AND NOT ... OR ...

cond2 := cond.AndCond(cond1).OrCond(cond.And("name", "slene"))
qs = qs.SetCond(cond2).Count()
// WHERE (... AND ... AND NOT ... OR ...) OR ( ... )

Limit

Limit maximum returns rows, the 2nd argument is for setting Offset.

var DefaultRowsLimit = 1000 // Default limitation of ORM is 1000.

qs.Limit(10)
// LIMIT 10

qs.Limit(10, 20)
// LIMIT 10 OFFSET 20

qs.Limit(-1)
// no limit

qs.Limit(-1, 100)
// LIMIT 18446744073709551615 OFFSET 100
// 18446744073709551615 is 1<<64 - 1 in case that no limitation of return rows but has offset.

Offset

Set offset rows:

qs.Offset(20)
// LIMIT 1000 OFFSET 20

OrderBy

Arguments are expr:

Use minus - to represent DESC:

qs.OrderBy("id", "-profile__age")
// ORDER BY id ASC, profile.age DESC

qs.OrderBy("-profile__age", "profile")
// ORDER BY profile.age DESC, profile_id ASC

RelatedSel

Arguments are expr:

var DefaultRelsDepth = 5 // `RelatedSel` will try 5 levels relational query as default.

qs := o.QueryTable("post")

qs.RelateSel()
// INNER JOIN user ... LEFT OUTER JOIN profile ...

qs.RelateSel("user")
// INNER JOIN user ... 
// Use expr to set fields that need to do relational query.

// Use `LEFT OUTER JOIN` for fields that have null properties.

Count

Total rows of results:

cnt, err := o.QueryTable("user").Count() // SELECT COUNT(*) FROM USER
fmt.Printf("Count Num: %s, %s", cnt, err)

Update

Batch update operation:

num, err := o.QueryTable("user").Filter("name", "slene").Update(orm.Params{
    "name": "astaxie",
})
fmt.Printf("Affected Num: %s, %s", num, err)
// SET name = "astaixe" WHERE name = "slene"

Delete

Batch delete operation:

num, err := o.QueryTable("user").Filter("name", "slene").Delete()
fmt.Printf("Affected Num: %s, %s", num, err)
// DELETE FROM user WHERE name = "slene"

PrepareInsert

Execute insert many times in one prepare to speed up:

var users []*User
...
qs := o.QueryTable("user")
i, _ := qs.PrepareInsert()
for _, user := range users {
    id, err := i.Insert(user)
    if err != nil {
        ...
    }
}
// PREPARE INSERT INTO user (`name`, ...) VALUES (?, ...)
// EXECUTE INSERT INTO user (`name`, ...) VALUES ("slene", ...)
// EXECUTE ...
// ...
i.Close() // 别忘记关闭 statement

All

Returns corresponding object result set:

var users []*User
num, err := o.QueryTable("user").Filter("name", "slene").All(&users)
fmt.Printf("Returned Rows Num: %s, %s", num, err)

One

Try to return single record:

var user *User
err := o.QueryTable("user").Filter("name", "slene").One(&user)
if err == orm.ErrMultiRows {
    fmt.Printf("Returned Multi Rows Not One")
}
if err == orm.ErrNoRows {
    fmt.Printf("Not row found")
}

Values

Returns key => value of result set.

Key is the field name of the Model, values saved as string:

var maps []orm.Params
num, err := o.QueryTable("user").Values(&maps)
if err != nil {
    fmt.Printf("Result Nums: %d\n", num)
    for _, m := range maps {
        fmt.Println(m["Id"], m["Name"])
    }
}

Returns specific field’s data:

var maps []orm.Params
num, err := o.QueryTable("user").Values(&maps, "id", "name", "profile", "profile__age")
if err != nil {
    fmt.Printf("Result Nums: %d\n", num)
    for _, m := range maps {
        fmt.Println(m["Id"], m["Name"], m["Profile"], m["Profile__Age"])
        // Data in map are expanded, no nested.
    }
}

ValuesList

Results are saved in slice, the order of results is the same order of fields in Model, elements are saved as string:

var lists []orm.ParamsList
num, err := o.QueryTable("user").ValuesList(&lists)
if err != nil {
    fmt.Printf("Result Nums: %d\n", num)
    for _, row := range lists {
        fmt.Println(row)
    }
}

You can use expr to indicate which field to be returned:

var lists []orm.ParamsList
num, err := o.QueryTable("user").ValuesList(&lists, "name", "profile__age")
if err != nil {
    fmt.Printf("Result Nums: %d\n", num)
    for _, row := range lists {
        fmt.Printf("Name: %s, Age: %s\m", row[0], row[1])
    }
}

ValuesFlat

Only returned specific field value into single slice:

var list orm.ParamsList
num, err := o.QueryTable("user").ValuesFlat(&list, "name")
if err != nil {
    fmt.Printf("Result Nums: %d\n", num)
    fmt.Printf("All User Names: %s", strings.Join(list, ", ")
}