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.

engine.Alias("o").Where("o.name = ?", name).Get(&order)

• And(string, …interface{})

Conditional AND

engine.Where(...).And(...).Get(&order)

• Asc(…string)

Ascending ordering on 1 or more fields

engine.Asc("id").Find(&orders)

• Desc(…string)

Descending ordering on 1 or more fields

engine.Asc("id").Desc("time").Find(&orders)

• ID(interface{})

Primary Key as query condition, for example:

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

if your primary key is composited, you can

engine.ID(schemas.PK{1, "name"}).Get(&user)
// 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:

engine.Select("a.*, (select name from b limit 1) as name").Find(&beans)

engine.Select("a.*, (select name from b limit 1) as name").Get(&bean)

• SQL(string, …interface{})

Custom SQL query

engine.SQL("select * from table").Find(&beans)

• Where(string, …interface{})

As SQL conditional WHERE clause

engine.Where("a = ? AND b = ?", 1, 2).Find(&beans)

engine.Where(builder.Eq{"a":1, "b": 2}).Find(&beans)

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

// select from table where column in (1,2,3)
engine.In("cloumn", 1, 2, 3).Find()

// select from table where column in (1,2,3)
engine.In("column", []int{1, 2, 3}).Find()

// select from table where column in (select column from table2 where a = 1)
engine.In("column", builder.Select("column").From("table2").Where(builder.Eq{"a":1})).Find()

• Cols(…string)

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

engine.Cols("age", "name").Get(&usr)
// SELECT age, name FROM user limit 1
engine.Cols("age", "name").Find(&users)
// SELECT age, name FROM user
engine.Cols("age", "name").Update(&user)
// UPDATE user SET age=? AND name=?

• AllCols()

Query or update all columns.

engine.AllCols().ID(1).Update(&user)
// 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()

engine.Omit("age", "gender").Update(&user)
// UPDATE user SET name = ? AND department = ?
engine.Omit("age, gender").Insert(&user)
// INSERT INTO user (name) values (?) // so age and gender will be as default value.
engine.Omit("age", "gender").Find(&users)
// SELECT name FROM user //only select columns except age and gender

• Distinct(…string)

As SQL DISTINCT

engine.Distinct("age", "department").Find(&users)
// 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