QuerySeter

ORM uses QuerySeter to organize queries. Every method that returns QuerySeter will give you a new QuerySeter object.

Basic Usage:

  1. o := orm.NewOrm()
  3. // or
  4. qs := o.QueryTable("user")
  6. // or
  7. qs = o.QueryTable(&User)
  9. // or
  10. user := new(User)
  11. 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:

  1. qs.Filter("id", 1) // WHERE id = 1

Or in relationships:

  1. qs.Filter("profile__age", 18) // WHERE profile.age = 18
  2. qs.Filter("Profile__Age", 18) // key name and field name are both valid
  3. qs.Filter("profile__age", 18) // WHERE profile.age = 18
  4. qs.Filter("profile__age__gt", 18) // WHERE profile.age > 18
  5. qs.Filter("profile__age__gte", 18) // WHERE profile.age >= 18
  6. qs.Filter("profile__age__in", 18, 20) // WHERE profile.age IN (18, 20)
  8. qs.Filter("profile__age__in", 18, 20).Exclude("profile__lt", 1000)
  9. // 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:

The operators that start with i ignore case.

exact

Default values of Filter, Exclude and Condition expr

  1. qs.Filter("name", "slene") // WHERE name = 'slene'
  2. qs.Filter("name__exact", "slene") // WHERE name = 'slene'
  3. // using = , case sensitive or not is depending on which collation database table is used
  4. qs.Filter("profile", nil) // WHERE profile_id IS NULL

iexact

  1. qs.Filter("name__iexact", "slene")
  2. // WHERE name LIKE 'slene'
  3. // Case insensitive, will match any name that equals to 'slene'

contains

  1. qs.Filter("name__contains", "slene")
  2. // WHERE name LIKE BINARY '%slene%'
  3. // Case sensitive, only match name that contains 'slene'

icontains

  1. qs.Filter("name__icontains", "slene")
  2. // WHERE name LIKE '%slene%'
  3. // Case insensitive, will match any name that contains 'slene'

in

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

gt / gte

  1. qs.Filter("profile__age__gt", 17)
  2. // WHERE profile.age > 17
  4. qs.Filter("profile__age__gte", 18)
  5. // WHERE profile.age >= 18

lt / lte

  1. qs.Filter("profile__age__lt", 17)
  2. // WHERE profile.age < 17
  4. qs.Filter("profile__age__lte", 18)
  5. // WHERE profile.age <= 18

startswith

  1. qs.Filter("name__startswith", "slene")
  2. // WHERE name LIKE BINARY 'slene%'
  3. // Case sensitive, only match name that starts with 'slene'

istartswith

  1. qs.Filter("name__istartswith", "slene")
  2. // WHERE name LIKE 'slene%'
  3. // Case insensitive, will match any name that starts with 'slene'

endswith

  1. qs.Filter("name__endswith", "slene")
  2. // WHERE name LIKE BINARY '%slene'
  3. // Case sensitive, only match name that ends with 'slene'

iendswith

  1. qs.Filter("name__iendswith", "slene")
  2. // WHERE name LIKE '%slene'
  3. // Case insensitive, will match any name that ends with 'slene'

isnull

  1. qs.Filter("profile__isnull", true)
  2. qs.Filter("profile_id__isnull", true)
  3. // WHERE profile_id IS NULL
  5. qs.Filter("profile__isnull", false)
  6. // WHERE profile_id IS NOT NULL

Intermediate Methods

Filter

Used to filter the result for the include conditions.

Use AND to connect multiple filters:

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

FilterRaw

  1. 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.

例如：

  1. qs.FilterRaw("user_id IN (SELECT id FROM profile WHERE age>=18)")
  2. //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:

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

SetCond

Custom conditions:

  1. cond := NewCondition()
  2. cond1 := cond.And("profile__isnull", false).AndNot("status__in", 1).Or("profile__age__gt", 2000)
  4. qs := orm.QueryTable("user")
  5. qs = qs.SetCond(cond1)
  6. // WHERE ... AND ... AND NOT ... OR ...
  8. cond2 := cond.AndCond(cond1).OrCond(cond.And("name", "slene"))
  9. qs = qs.SetCond(cond2).Count()
  10. // WHERE (... AND ... AND NOT ... OR ...) OR ( ... )

GetCond

  1. GetCond() *Condition

It returns all conditions:

  1.  cond := orm.NewCondition()
  2.  cond = cond.And("profile__isnull", false).AndNot("status__in", 1)
  3.  qs = qs.SetCond(cond)
  4.  cond = qs.GetCond()
  5.  cond := cond.Or("profile__age__gt", 2000)
  6.  //sql-> WHERE T0.`profile_id` IS NOT NULL AND NOT T0.`Status` IN (?) OR T1.`age` >  2000
  7.  num, err := qs.SetCond(cond).Count()

Limit

Limit maximum returned lines. The second param can set Offset

  1. var DefaultRowsLimit = 1000 // The default limit of ORM is 1000
  3. // LIMIT 1000
  5. qs.Limit(10)
  6. // LIMIT 10
  8. qs.Limit(10, 20)
  9. // LIMIT 10 OFFSET 20
  11. qs.Limit(-1)
  12. // no limit
  14. qs.Limit(-1, 100)
  15. // LIMIT 18446744073709551615 OFFSET 100
  16. // 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:

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

GroupBy

  1. qs.GroupBy("id", "age")
  2. // GROUP BY id,age

OrderBy

  1. OrderBy(exprs ...string) QuerySeter

Cases:

  • If the column names are passed in, then it means sort by column name ASC；
  • If the column names with symbol - are passed in, then it means sort by column name DESC；

Example:

  1. qs.OrderBy("id", "-profile__age")
  2. // ORDER BY id ASC, profile.age DESC
  4. qs.OrderBy("-profile__age", "profile")
  5. // ORDER BY profile.age DESC, profile_id ASC

Similarly:

  1. qs.OrderBy("id", "-profile__age")
  2. // ORDER BY id ASC, profile.age DESC
  4. qs.OrderBy("-profile__age", "profile")
  5. // 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.

  1. qs.ForceIndex(`idx_name1`,`idx_name2`)

UseIndex

Suggest DB to user the index.

You need to check your DB whether it support this feature.

  1. qs.UseIndex(`idx_name1`,`idx_name2`)

IgnoreIndex

Make DB ignore the index

You need to check your DB whether it support this feature.

  1. qs.IgnoreIndex(`idx_name1`,`idx_name2`)

RelatedSel

  1. 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:

  1. // Use LEFT JOIN to load all the related table data of table user
  2. qs.RelatedSel().One(&user)
  3. // Use LEFT JOIN to load only the data of the profile of table user
  4. qs.RelatedSel("profile").One(&user)
  5. 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

  1. qs.Distinct()
  2. // SELECT DISTINCT

ForUpdate

  1. ForUpdate() QuerySeter

Add FOR UPDATE clause.

PrepareInsert

  1. PrepareInsert() (Inserter, error)

Used to prepare multiple insert inserts at once to increase the speed of bulk insertion.

  1. var users []*User
  2. ...
  3. qs := o.QueryTable("user")
  4. i, _ := qs.PrepareInsert()
  5. for _, user := range users {
  6.     id, err := i.Insert(user)
  7.     if err == nil {
  8.         ...
  9.     }
  10. }
  11. // PREPARE INSERT INTO user (`name`, ...) VALUES (?, ...)
  12. // EXECUTE INSERT INTO user (`name`, ...) VALUES ("slene", ...)
  13. // EXECUTE ...
  14. // ...
  15. i.Close() // don't forget to close statement

Aggregate

  1. Aggregate(s string) QuerySeter

Using aggregate functions:

  1. type result struct {
  2.   DeptName string
  3.   Total    int
  4. }
  5. var res []result
  6. o.QueryTable("dept_info").Aggregate("dept_name,sum(salary) as total").GroupBy("dept_name").All(&res)

Terminate Methods

Count

  1. Count() (int64, error)

Return line count based on the current query

Exist

  1. 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

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

Atom operation add field:

  1. // Assume there is a nums int field in user struct
  2. num, err := o.QueryTable("user").Update(orm.Params{
  3.     "nums": orm.ColValue(orm.Col_Add, 100),
  4. })
  5. // SET nums = nums + 100

orm.ColValue supports:

  1. Col_Add      // plus
  2. Col_Minus    // minus 
  3. Col_Multiply // multiply 
  4. Col_Except   // divide

Delete

  1. Delete() (int64, error)

Execute batch deletion based on the current query.

All

Return the related ResultSet

Param of All supports []Type and []*Type

  1. var users []*User
  2. num, err := o.QueryTable("user").Filter("name", "slene").All(&users)
  3. 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:

  1. type Post struct {
  2.     Id      int
  3.     Title   string
  4.     Content string
  5.     Status  int
  6. }
  8. // Only return Id and Title
  9. var posts []Post
  10. 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

  1. var user User
  2. err := o.QueryTable("user").Filter("name", "slene").One(&user)
  3. if err == orm.ErrMultiRows {
  4.     // Have multiple records
  5.     fmt.Printf("Returned Multi Rows Not One")
  6. }
  7. if err == orm.ErrNoRows {
  8.     // No result 
  9.     fmt.Printf("Not row found")
  10. }

The returned fields can be specified:

  1. // Only return Id and Title
  2. var post Post
  3. 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.

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

Return specific fields:

TODO: doesn’t support recursive query. RelatedSel return Values directly

But it can specify the value needed by expr.

  1. var maps []orm.Params
  2. num, err := o.QueryTable("user").Values(&maps, "id", "name", "profile", "profile__age")
  3. if err == nil {
  4. fmt.Printf("Result Nums: %d\n", num)
  5. for _, m := range maps {
  6. fmt.Println(m["Id"], m["Name"], m["Profile"], m["Profile__Age"])
  7. // There is no complicated nesting data in the map
  8. }
  9. }

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.

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

It can return specific fields by setting expr.

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

ValuesFlat

Only returns a single values slice of a specific field.

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

RowsToMap 和 RowsToStruct

Not implement.