in Query

Use string or slice parameter types. When using the slice parameter type, only one ? placeholder is needed.

  1. // SELECT * FROM user WHERE uid IN(100,10000,90000)
  2. g.Model("user").Where("uid IN(?,?,?)", 100, 10000, 90000).All()
  3. g.Model("user").Where("uid", g.Slice{100, 10000, 90000}).All()
  5. // SELECT * FROM user WHERE gender=1 AND uid IN(100,10000,90000)
  6. g.Model("user").Where("gender=? AND uid IN(?)", 1, g.Slice{100, 10000, 90000}).All()
  8. // SELECT COUNT(*) FROM user WHERE age in(18,50)
  9. g.Model("user").Where("age IN(?,?)", 18, 50).Count()
  10. g.Model("user").Where("age", g.Slice{18, 50}).Count()

Use any map parameter type.

  1. // SELECT * FROM user WHERE gender=1 AND uid IN(100,10000,90000)
  2. g.Model("user").Where(g.Map{
  3.     "gender" : 1,
  4.     "uid"    : g.Slice{100,10000,90000},
  5. }).All()

Use struct parameter type, note that the order of query conditions depends on the order of struct attribute definitions.

  1. type User struct {
  2.     Id     []int  `orm:"uid"`
  3.     Gender int    `orm:"gender"`
  4. }
  5. // SELECT * FROM `user` WHERE uid IN(100,10000,90000) AND gender=1
  6. g.Model("user").Where(User{
  7.     Gender: 1,
  8.     Id:     []int{100, 10000, 90000},
  9. }).All()

For usability, if the passed slice parameter is empty or nil, the query will not throw an error but convert to a false condition statement.

  1. // SELECT * FROM `user` WHERE 0=1
  2. g.Model("user").Where("uid", g.Slice{}).All()
  3. // SELECT * FROM `user` WHERE `uid` IS NULL
  4. g.Model("user").Where("uid", nil).All()

The ORM also provides common condition methods WhereIn/WhereNotIn/WhereOrIn/WhereOrNotIn for common In query condition filtering. Method definitions are as follows:

  1. func (m *Model) WhereIn(column string, in interface{}) *Model
  2. func (m *Model) WhereNotIn(column string, in interface{}) *Model
  3. func (m *Model) WhereOrIn(column string, in interface{}) *Model
  4. func (m *Model) WhereOrNotIn(column string, in interface{}) *Model

Examples:

  1. // SELECT * FROM `user` WHERE (`gender`=1) AND (`type` IN(1,2,3))
  2. g.Model("user").Where("gender", 1).WhereIn("type", g.Slice{1,2,3}).All()
  4. // SELECT * FROM `user` WHERE (`gender`=1) AND (`type` NOT IN(1,2,3))
  5. g.Model("user").Where("gender", 1).WhereNotIn("type", g.Slice{1,2,3}).All()
  7. // SELECT * FROM `user` WHERE (`gender`=1) OR (`type` IN(1,2,3))
  8. g.Model("user").Where("gender", 1).WhereOrIn("type", g.Slice{1,2,3}).All()
  10. // SELECT * FROM `user` WHERE (`gender`=1) OR (`type` NOT IN(1,2,3))
  11. g.Model("user").Where("gender", 1).WhereOrNotIn("type", g.Slice{1,2,3}).All()

like Query

  1. // SELECT * FROM `user` WHERE name like '%john%'
  2. g.Model("user").Where("name like ?", "%john%").All()
  3. // SELECT * FROM `user` WHERE birthday like '1990-%'
  4. g.Model("user").Where("birthday like ?", "1990-%").All()

Starting from goframe v1.16, goframe‘s ORM also provides common condition methods WhereLike/WhereNotLike/WhereOrLike/WhereOrNotLike for common Like query condition filtering. Method definitions are as follows:

  1. func (m *Model) WhereLike(column string, like interface{}) *Model
  2. func (m *Model) WhereNotLike(column string, like interface{}) *Model
  3. func (m *Model) WhereOrLike(column string, like interface{}) *Model
  4. func (m *Model) WhereOrNotLike(column string, like interface{}) *Model

Examples:

  1. // SELECT * FROM `user` WHERE (`gender`=1) AND (`name` LIKE 'john%')
  2. g.Model("user").Where("gender", 1).WhereLike("name", "john%").All()
  4. // SELECT * FROM `user` WHERE (`gender`=1) AND (`name` NOT LIKE 'john%')
  5. g.Model("user").Where("gender", 1).WhereNotLike("name", "john%").All()
  7. // SELECT * FROM `user` WHERE (`gender`=1) OR (`name` LIKE 'john%')
  8. g.Model("user").Where("gender", 1).WhereOrLike("name", "john%").All()
  10. // SELECT * FROM `user` WHERE (`gender`=1) OR (`name` NOT LIKE 'john%')
  11. g.Model("user").Where("gender", 1).WhereOrNotLike("name", "john%").All()

min/max/avg/sum

We directly apply the statistical method on the Fields method, for example:

  1. // SELECT MIN(score) FROM `user` WHERE `uid`=1 LIMIT 1
  2. g.Model("user").Fields("MIN(score)").Where("uid", 1).Value()
  4. // SELECT MAX(score) FROM `user` WHERE `uid`=1 LIMIT 1
  5. g.Model("user").Fields("MAX(score)").Where("uid", 1).Value()
  7. // SELECT AVG(score) FROM `user` WHERE `uid`=1 LIMIT 1
  8. g.Model("user").Fields("AVG(score)").Where("uid", 1).Value()
  10. // SELECT SUM(score) FROM `user` WHERE `uid`=1  LIMIT 1
  11. g.Model("user").Fields("SUM(score)").Where("uid", 1).Value()

Starting from goframe v1.16, goframe‘s ORM also provides common statistical methods Min/Max/Avg/Sum for common field statistical queries. Method definitions are as follows:

  1. func (m *Model) Min(column string) (float64, error)
  2. func (m *Model) Max(column string) (float64, error)
  3. func (m *Model) Avg(column string) (float64, error)
  4. func (m *Model) Sum(column string) (float64, error)

The above examples using shortcut statistical methods:

  1. // SELECT MIN(`score`) FROM `user` WHERE `uid`=1 LIMIT 1
  2. g.Model("user").Where("uid", 1).Min("score")
  4. // SELECT MAX(`score`) FROM `user` WHERE `uid`=1 LIMIT 1
  5. g.Model("user").Where("uid", 1).Max("score")
  7. // SELECT AVG(`score`) FROM `user` WHERE `uid`=1 LIMIT 1
  8. g.Model("user").Where("uid", 1).Avg("score")
  10. // SELECT SUM(`score`) FROM `user` WHERE `uid`=1 LIMIT 1
  11. g.Model("user").Where("uid", 1).Sum("score")

count Query

  1. // SELECT COUNT(1) FROM `user` WHERE `birthday`='1990-10-01'
  2. g.Model("user").Where("birthday", "1990-10-01").Count()
  3. // SELECT COUNT(uid) FROM `user` WHERE `birthday`='1990-10-01'
  4. g.Model("user").Fields("uid").Where("birthday", "1990-10-01").Count()

Starting from goframe v1.16, goframe‘s ORM also provides a common method CountColumn for Count by field. Method definition is as follows:

  1. func (m *Model) CountColumn(column string) (int, error)

Example:

  1. g.Model("user").Where("birthday", "1990-10-01").CountColumn("uid")

distinct Query

  1. // SELECT DISTINCT uid,name FROM `user`
  2. g.Model("user").Fields("DISTINCT uid,name").All()
  3. // SELECT COUNT(DISTINCT uid,name) FROM `user`
  4. g.Model("user").Fields("DISTINCT uid,name").Count()

Starting from goframe v1.16, goframe‘s ORM also provides a method Distinct for field uniqueness filtering. Method definition is as follows:

  1. func (m *Model) Distinct() *Model

Example:

  1. // SELECT COUNT(DISTINCT `name`) FROM `user`
  2. g.Model("user").Distinct().CountColumn("name")
  4. // SELECT COUNT(DISTINCT uid,name) FROM `user`
  5. g.Model("user").Distinct().CountColumn("uid,name")
  7. // SELECT DISTINCT group,age FROM `user`
  8. g.Model("user").Fields("group, age").Distinct().All()

between Query

  1. // SELECT * FROM `user` WHERE age between 18 and 20
  2. g.Model("user").Where("age between ? and ?", 18, 20).All()

Starting from goframe v1.16, goframe‘s ORM also provides common condition methods WhereBetween/WhereNotBetween/WhereOrBetween/WhereOrNotBetween for common Between query condition filtering. Method definitions are as follows:

  1. func (m *Model) WhereBetween(column string, min, max interface{}) *Model
  2. func (m *Model) WhereNotBetween(column string, min, max interface{}) *Model
  3. func (m *Model) WhereOrBetween(column string, min, max interface{}) *Model
  4. func (m *Model) WhereOrNotBetween(column string, min, max interface{}) *Model

Examples:

  1. // SELECT * FROM `user` WHERE (`gender`=0) AND (`age` BETWEEN 16 AND 20)
  2. g.Model("user").Where("gender", 0).WhereBetween("age", 16, 20).All()
  4. // SELECT * FROM `user` WHERE (`gender`=0) AND (`age` NOT BETWEEN 16 AND 20)
  5. g.Model("user").Where("gender", 0).WhereNotBetween("age", 16, 20).All()
  7. // SELECT * FROM `user` WHERE (`gender`=0) OR (`age` BETWEEN 16 AND 20)
  8. g.Model("user").Where("gender", 0).WhereOrBetween("age", 16, 20).All()
  10. // SELECT * FROM `user` WHERE (`gender`=0) OR (`age` NOT BETWEEN 16 AND 20)
  11. g.Model("user").Where("gender", 0).WhereOrNotBetween("age", 16, 20).All()

null Query

The ORM provides common condition methods WhereNull/WhereNotNull/WhereOrNull/WhereOrNotNull for common Null query condition filtering. Method definitions are as follows:

  1. func (m *Model) WhereNull(columns ...string) *Model
  2. func (m *Model) WhereNotNull(columns ...string) *Model
  3. func (m *Model) WhereOrNull(columns ...string) *Model
  4. func (m *Model) WhereOrNotNull(columns ...string) *Model

Examples:

  1. // SELECT * FROM `user` WHERE (`created_at`>'2021-05-01 00:00:00') AND (`inviter` IS NULL)
  2. g.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereNull("inviter").All()
  4. // SELECT * FROM `user` WHERE (`created_at`>'2021-05-01 00:00:00') AND (`inviter` IS NOT NULL)
  5. g.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereNotNull("inviter").All()
  7. // SELECT * FROM `user` WHERE (`created_at`>'2021-05-01 00:00:00') OR (`inviter` IS NULL)
  8. g.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereOrNull("inviter").All()
  10. // SELECT * FROM `user` WHERE (`created_at`>'2021-05-01 00:00:00') OR (`inviter` IS NOT NULL)
  11. g.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereOrNotNull("inviter").All()

Additionally, these methods support multiple field inputs, for example:

  1. // SELECT * FROM `user` WHERE (`created_at`>'2021-05-01 00:00:00') AND (`inviter` IS NULL) AND (`creator` IS NULL)
  2. g.Model("user").Where("created_at>?", gtime.New("2021-05-01")).WhereNull("inviter", "creator").All()