Queries and Aggregations is basically formed by using Get
, Find
, Count
methods, with conjunction of following chainable APIs to form conditions, grouping and ordering:
Set an alias name for table, so we can use alias on conditions.
engine.Alias("o").Where("o.name = ?", name).Get(&order)
Conditional AND
engine.Where(...).And(...).Get(&order)
Ascending ordering on 1 or more fields
engine.Asc("id").Find(&orders)
Descending ordering on 1 or more fields
engine.Asc("id").Desc("time").Find(&orders)
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(core.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.
Conditional OR
As SQL ORDER BY
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)
Custom SQL query
engine.SQL("select * from table").Find(&beans)
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)
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()
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=?
Query or update all columns.
engine.AllCols().ID(1).Update(&user)
// UPDATE user SET name = ?, age =?, gender =? WHERE id = 1
Update the specified columns and other non-empty, non-zero columns.
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
As SQL DISTINCT
engine.Distinct("age", "department").Find(&users)
// SELECT DISTINCT age, department FROM user
Caution: this method will not lookup from caching store
Specify table name, or if struct pointer is passed into the name is extract from struct type name by IMapper conversion policy
As SQL LIMIT with optional second param for OFFSET
As SQL LIMIT
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
As SQL GROUP BY
As SQL HAVING