# QuerySeter
ORM uses QuerySeter to organize queries. Every method that returns QuerySeter will give you a new QuerySeter object.
Basic Usage:
o := orm.NewOrm()
// or
qs := o.QueryTable("user")
// or
qs = o.QueryTable(&User)
// or
user := new(User)
qs = o.QueryTable(user) // return QuerySeter
The methods of QuerySeter
can be roughly divided into two categories:
- Intermediate methods: used to construct the query.
- Terminate methods: used to execute the query and encapsulate the result.
- Each api call that returns a QuerySeter creates a new QuerySeter, without affecting the previously created.
- Advanced queries use Filter and Exclude to do common conditional queries.
# Query Expression
Beego has designed its own query expressions, which can be used in many methods.
In general, you can use expressions for fields in a single table, or you can use expressions on related tables. For example:
qs.Filter("id", 1) // WHERE id = 1
Or in relationships:
qs.Filter("profile__age", 18) // WHERE profile.age = 18
qs.Filter("Profile__Age", 18) // key name and field name are both valid
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
For example, if the User table has a foreign key for Profile, then if the User table is queried for the corresponding Profile.Age, then Profile__Age
is used. Note that the field separators use the double underscore __
for the field separator.
Operators can be added to the end of an expression to perform the corresponding sql operation. For example, Profile__Age__gt
represents a conditional query for Profile.Age > 18
. If no operator is specified, =
will be used as the operator.
The supported operators:
- exact / iexact - equal to
- contains / icontains - contains
- gt / gte - greater than / greater than or equal to
- lt / lte - less than / less than or equal to
- startswith / istartswith - starts with
- endswith / iendswith - ends with
- in
- isnull
The operators that start with i
ignore case.
# exact
Default values of Filter, Exclude and Condition expr
qs.Filter("name", "slene") // WHERE name = 'slene'
qs.Filter("name__exact", "slene") // WHERE name = 'slene'
// using = , case sensitive or not is depending on which collation database table is used
qs.Filter("profile", nil) // WHERE profile_id IS NULL
# iexact
qs.Filter("name__iexact", "slene")
// WHERE name LIKE 'slene'
// Case insensitive, will match any name that equals to 'slene'
# contains
qs.Filter("name__contains", "slene")
// WHERE name LIKE BINARY '%slene%'
// Case sensitive, only match name that contains 'slene'
# icontains
qs.Filter("name__icontains", "slene")
// WHERE name LIKE '%slene%'
// Case insensitive, will match any name that contains 'slene'
# 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, only match name that starts with 'slene'
# istartswith
qs.Filter("name__istartswith", "slene")
// WHERE name LIKE 'slene%'
// Case insensitive, will match any name that starts with 'slene'
# endswith
qs.Filter("name__endswith", "slene")
// WHERE name LIKE BINARY '%slene'
// Case sensitive, only match name that ends with 'slene'
# iendswith
qs.Filter("name__iendswith", "slene")
// WHERE name LIKE '%slene'
// Case insensitive, will match any name that ends with '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
# Intermediate Methods
# Filter
Used to filter the result for the include conditions.
Use AND
to connect multiple filters:
qs.Filter("profile__isnull", true).Filter("name", "slene")
// WHERE profile_id IS NULL AND name = 'slene'
# FilterRaw
FilterRaw(string, string) QuerySeter
This method treats the input directly as a query condition, so if there is an error in the input, then the resulting spliced SQL will not work. Beego itself does not perform any checks.
For example:
qs.FilterRaw("user_id IN (SELECT id FROM profile WHERE age>=18)")
//sql-> WHERE user_id IN (SELECT id FROM profile WHERE age>=18)
# Exclude
Used to filter the result for the exclude conditions.
Use NOT
to exclude condition
Use AND
to connect multiple filters:
qs.Exclude("profile__isnull", true).Filter("name", "slene")
// WHERE NOT profile_id IS NULL AND name = 'slene'
# SetCond
Custom conditions:
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 ( ... )
# GetCond
GetCond() *Condition
It returns all conditions:
cond := orm.NewCondition()
cond = cond.And("profile__isnull", false).AndNot("status__in", 1)
qs = qs.SetCond(cond)
cond = qs.GetCond()
cond := cond.Or("profile__age__gt", 2000)
//sql-> WHERE T0.`profile_id` IS NOT NULL AND NOT T0.`Status` IN (?) OR T1.`age` > 2000
num, err := qs.SetCond(cond).Count()
# Limit
Limit maximum returned lines. The second param can set Offset
.
var DefaultRowsLimit = 1000 // The default limit of ORM is 1000
// LIMIT 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. Used to set the condition which is no limit but with offset
If you do not call the method, or if you call the method but pass in a negative number, Beego will use the default value, e.g. 1000.
# Offset
Set offset lines:
qs.Offset(20)
// LIMIT 1000 OFFSET 20
# GroupBy
qs.GroupBy("id", "age")
// GROUP BY id,age
# OrderBy
OrderBy(exprs ...string) QuerySeter
Cases:
- If the column names are passed in, then it means sort ascending by column name.
- If the column names with symbol
-
are passed in, then it means sort descending by column name.
Example:
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
Similarly:
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
# ForceIndex
Forcing DB to use the index.
You need to check your DB whether it support this feature.
qs.ForceIndex(`idx_name1`,`idx_name2`)
# UseIndex
Suggest DB to user the index.
You need to check your DB whether it support this feature.
qs.UseIndex(`idx_name1`,`idx_name2`)
# IgnoreIndex
Make DB ignore the index.
You need to check your DB whether it support this feature.
qs.IgnoreIndex(`idx_name1`,`idx_name2`)
# RelatedSel
RelatedSel(params ...interface{}) QuerySeter
Loads the data of the associated table. If no parameters are passed, then Beego loads the data of all related tables. If parameters are passed, then only the specific table data is loaded.
When loading, if the corresponding field is available as NULL, then LEFT JOIN is used. Otherwise JOIN is used.
Example:
// Use LEFT JOIN to load all the related table data of table user
qs.RelatedSel().One(&user)
// Use LEFT JOIN to load only the data of the profile of table user
qs.RelatedSel("profile").One(&user)
user.Profile.Age = 32
Calling RelatedSel directly by default will perform a relational query at the maximum DefaultRelsDepth
.
# Distinct
Same as distinct
statement in sql, return only distinct (different) values
qs.Distinct()
// SELECT DISTINCT
# ForUpdate
ForUpdate() QuerySeter
Add FOR UPDATE clause.
# PrepareInsert
PrepareInsert() (Inserter, error)
Used to prepare multiple insert inserts at once to increase the speed of bulk insertion.
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() // don't forget to close statement
# Aggregate
Aggregate(s string) QuerySeter
Using aggregate functions:
type result struct {
DeptName string
Total int
}
var res []result
o.QueryTable("dept_info").Aggregate("dept_name,sum(salary) as total").GroupBy("dept_name").All(&res)
# Terminate Methods
# Count
Count() (int64, error)
Return line count based on the current query.
# Exist
Exist() bool
Determines if the query returns data. Equivalent to Count()
to return a value greater than 0.
# Update
Execute batch updating based on the current query:
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"
Atom operation add field:
// Assume there is a nums int field in user struct
num, err := o.QueryTable("user").Update(orm.Params{
"nums": orm.ColValue(orm.Col_Add, 100),
})
// SET nums = nums + 100
orm.ColValue supports:
Col_Add // plus
Col_Minus // minus
Col_Multiply // multiply
Col_Except // divide
# Delete
Delete() (int64, error)
Execute batch deletion based on the current query.
# All
Return the related ResultSet.
Param of All
supports *[]Type
and *[]*Type
.
var users []*User
num, err := o.QueryTable("user").Filter("name", "slene").All(&users)
fmt.Printf("Returned Rows Num: %s, %s", num, err)
All / Values / ValuesList / ValuesFlat will be limited by Limit. 1000 lines by default.
The returned fields can be specified:
type Post struct {
Id int
Title string
Content string
Status int
}
// Only return Id and Title
var posts []Post
o.QueryTable("post").Filter("Status", 1).All(&posts, "Id", "Title")
The other fields of the object are set to the default value of the field's type.
# One
Try to return one record:
var user User
err := o.QueryTable("user").Filter("name", "slene").One(&user)
if err == orm.ErrMultiRows {
// Have multiple records
fmt.Printf("Returned Multi Rows Not One")
}
if err == orm.ErrNoRows {
// No result
fmt.Printf("Not row found")
}
The returned fields can be specified:
// Only return Id and Title
var post Post
o.QueryTable("post").Filter("Content__istartswith", "prefix string").One(&post, "Id", "Title")
The other fields of the object are set to the default value of the fields' type.
# Values
Return key => value of result set.
key is Field name in Model. value type if 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"])
}
}
Return specific fields:
TODO: doesn't support recursive query. RelatedSel return Values directly
But it can specify the value needed by expr.
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"])
// There is no complicated nesting data in the map
}
}
# ValuesList
The result set will be stored as a slice.
The order of the result is same as the Fields order in the Model definition.
The values are saved as strings.
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)
}
}
It can return specific fields by setting expr.
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 returns a single values slice of a specific field.
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, ", "))
}
# RowsToMap and RowsToStruct
Not implemented.