Conditions

5.1. Chainable APIs for Queries, Execusions and Aggregations

Queries and Aggregations is basically formed by using Get, Find, Count methods, with conjunction of following chainable APIs to form conditions, grouping and ordering:

  • Alias(string)

Set an alias name for table, so we can use alias on conditions.

  1. engine.Alias("o").Where("o.name = ?", name).Get(&order)
  • And(string, …interface{})

Conditional AND

  1. engine.Where(...).And(...).Get(&order)
  • Asc(…string)

Ascending ordering on 1 or more fields

  1. engine.Asc("id").Find(&orders)
  • Desc(…string)

Descending ordering on 1 or more fields

  1. engine.Asc("id").Desc("time").Find(&orders)
  • ID(interface{})

Primary Key as query condition, for example:

  1. var user User
  2. engine.ID(1).Get(&user)
  3. // SELECT * FROM user Where id = 1

if your primary key is composited, you can

  1. engine.ID(schemas.PK{1, "name"}).Get(&user)
  2. // SELECT * FROM user Where id =1 AND name= 'name'

The primary key sequence is the same as the field sequence in the struct.

  • Or(string, …interface{})

Conditional OR

  • OrderBy(string)

As SQL ORDER BY

  • Select(string)

Specify the select part when use Find, Iterate or Get:

  1. engine.Select("a.*, (select name from b limit 1) as name").Find(&beans)
  3. engine.Select("a.*, (select name from b limit 1) as name").Get(&bean)
  • SQL(string, …interface{})

Custom SQL query

  1. engine.SQL("select * from table").Find(&beans)
  • Where(string, …interface{})

As SQL conditional WHERE clause

  1. engine.Where("a = ? AND b = ?", 1, 2).Find(&beans)
  3. engine.Where(builder.Eq{"a":1, "b": 2}).Find(&beans)
  5. engine.Where(builder.Eq{"a":1}.Or(builder.Eq{"b": 2})).Find(&beans)
  • In(string, …interface{})

As SQL Conditional IN, you can also give a slice as parameters. And you could use builder.Builder as a sub query

  1. // select from table where column in (1,2,3)
  2. engine.In("cloumn", 1, 2, 3).Find()
  4. // select from table where column in (1,2,3)
  5. engine.In("column", []int{1, 2, 3}).Find()
  7. // select from table where column in (select column from table2 where a = 1)
  8. engine.In("column", builder.Select("column").From("table2").Where(builder.Eq{"a":1})).Find()
  • Cols(…string)

Explicity specify query or update columns. e.g.,:

  1. engine.Cols("age", "name").Get(&usr)
  2. // SELECT age, name FROM user limit 1
  3. engine.Cols("age", "name").Find(&users)
  4. // SELECT age, name FROM user
  5. engine.Cols("age", "name").Update(&user)
  6. // UPDATE user SET age=? AND name=?
  • AllCols()

Query or update all columns.

  1. engine.AllCols().ID(1).Update(&user)
  2. // UPDATE user SET name = ?, age =?, gender =? WHERE id = 1
  • MustCols(…string)

Update the specified columns and other non-empty, non-zero columns.

  • Omit(…string)

Inverse function to Cols, to exclude specify query or update columns. Warning: Don’t use with Cols()

  1. engine.Omit("age", "gender").Update(&user)
  2. // UPDATE user SET name = ? AND department = ?
  3. engine.Omit("age, gender").Insert(&user)
  4. // INSERT INTO user (name) values (?) // so age and gender will be as default value.
  5. engine.Omit("age", "gender").Find(&users)
  6. // SELECT name FROM user //only select columns except age and gender
  • Distinct(…string)

As SQL DISTINCT

  1. engine.Distinct("age", "department").Find(&users)
  2. // SELECT DISTINCT age, department FROM user

Caution: this method will not lookup from caching store

  • Table(nameOrStructPtr interface{})

Specify table name, or if struct pointer is passed into the name is extract from struct type name by IMapper conversion policy

  • Limit(int, …int)

As SQL LIMIT with optional second param for OFFSET

  • Top(int)

As SQL LIMIT

  • Join(type, tableName, criteria string)

As SQL JOIN, support type: either of these values [INNER, LEFT OUTER, CROSS] are supported now tableName: joining table name criteria: join criteria

See 5.Join usage

  • GroupBy(string)

As SQL GROUP BY

  • Having(string)

As SQL HAVING