DB.Table("users").Where("username","john").First(&john) //{select * from `users` where `username` = ? limit 1 [john] {1} 68.820758ms}
If you don’t need entire row,just a column value ,use Value.
var email string DB.Table("users").Where("username","john").Value(&email,"email") //{select `email` from `users` where `username` = ? [john] {1} 73.392676ms} fmt.Println(email) //john@hotmail.com
Use Find to get a row by id
var id4 ChatUser DB.Table("users").Find(&id4,4) //{select * from `users` where `id` in (?) limit 1 [4] {1} 63.692909ms} fmt.Println(id4) //{4 {john@hotmail.com true} { false}}
// Even more ,like Laravel var findMore []ChatUser DB.Table("users").Find(&findMore,[]interface{}{4,6,8}) //{select * from `users` where `id` in (?,?,?) [4 6 8] {3} 68.072428ms} fmt.Println(findMore) //[{4 {john@hotmail.com true} { false}} {6 {john@apple.com true} { false}} {8 {john@yahoo.com true} { false}}]
As you can see ,Find can take a slice to Find mutiple records.
Retrieving A List Of Column Values
You can use Pluck to get a list of a single column
var titles []string DB.Table("posts").Pluck(&tites,"title") //{select `path` from `images` [] {3} 71.442575ms} fmt.Println(paths) //[/images1.jpg /images2.jpg /img3.png]
Chunking Results
Chunk
err = DB.Table("users").OrderBy("id").Chunk(&[]User{}, 10, func(dest interface{})error { us := dest.(*[]User) for _, user := range *us { fmt.Println(user) } returnnil })
ChunkById
err = DB.Table("users").ChunkById(&[]User{}, 10, func(dest interface{})error { us := dest.(*[]User) for _, user := range *us { fmt.Println(user) } returnnil })
Aggregates
We have count,max,min,avg,sum aggregate methods.
var total,avg,max,sum,min float64 DB.Connection("default").Model(&DefaultUser{}).Where("age",">",20).Count(&total, "balance") //{select count(`balance`) as aggregate from `users` where `age` > ? [20] {1} 69.96036ms}
DB.Connection("default").Model(&DefaultUser{}).Max(&max, "balance") //{select max(`balance`) as aggregate from `users` [] {1} 72.807184ms}
DB.Connection("default").Model(&DefaultUser{}).Min(&min, "balance") //{select min(`balance`) as aggregate from `users` [] {1} 64.839132ms}
DB.Connection("default").Model(&DefaultUser{}).Sum(&sum, "balance") //{select sum(`balance`) as aggregate from `users` [] {1} 78.220135ms}
DB.Connection("default").Model(&DefaultUser{}).Avg(&avg, "balance") //{select avg(`balance`) as aggregate from `users` [] {1} 94.77536ms}
You can use Select to specify which column to select
var m = make(map[string]interface{}) DB.Connection("chat").Table("users").Select("id","phone","location").First(&m) DB.Connection("chat").Table("users").First(&m,"id","phone","location") //{select `id`,`phone`,`location` from `users` limit 1 [] {1} 62.829325ms}
The distinct method allows you to force the query to return distinct results:
DB.Distinct().Select("name").From("users")
If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:
DB.Select("foo").AddSelect("bar").AddSelect("baz", "boom").From("users") //"select `foo`, `bar`, `baz`, `boom` from `users`"
Raw Expressions
In Select,Where,GroupBy,OrderBy,Having,you can pass by a Expression . **This could lead to SQL injection **
DB.Table("codes").Where("user_id", userid).GroupBy("code_type").Mapping(map[string]interface{}{ "type": 0, "num": 0, }).Get(&codes, goeloquent.Expression("code_type as type,count(1) as num"))
Raw Methods
SelectRaw
DB.From("orders").SelectRaw("price * ? as price_with_tax", []interface{}{1.1}) //"select price * ? as price_with_tax from `orders`"
WhereRaw/OrWhereRaw
DB.Select().From("users").WhereRaw("id = ? or email = ?", []interface{}{1, "foo"}) "select * from `users` where id = ? or email = ?"
HavingRaw/OrHavingRaw
DB.Select().From("users").HavingRaw("user_foo < user_bar") //"select * from `users` having user_foo < user_bar"
OrderByRaw
DB.Select().From("users").OrderBy("email").OrderByRaw("`age` ? desc", []interface{}{"foo"}) "select * from `users` order by `email` asc, `age` ? desc", sql)
GroupByRaw
DB.Select().From("users").GroupByRaw("DATE(created_at), ? DESC", []interface{}{"foo"}) "select * from `users` group by DATE(created_at), ? DESC"
Joins
When use join, you can scan the result into map or struct
type UserWithAddress struct { Id int64 Age int Name string Country string Province string`goelo:"column:state"` City string Address string`goelo:"column:detail"` } //dest is struct var u UserWithAddress
DB.From("user").Select("user.id as id", "user.age as age", "user.name as name"). AddSelect("address.country as country", "address.state as state", "address.city as city", "address.detail as detail"). Join("address", "user.id", "address.user_id").First(&u) //{select `user`.`id` as `id`, `user`.`age` as `age`, `user`.`name` as `name`, `address`.`country` as `country`, `address`.`state` as `state`, `address`.`city` as `city`, `address`.`detail` as `detail` from `user` inner join `address` on `user`.`id` = `address`.`user_id` limit 1 [] {1} 83.980292ms} joinMap := make(map[string]interface{}) DB.Query().From("user").Select("user.*"). AddSelect("address.*"). Join("address", "user.id", "address.user_id").First(&joinMap) //{select `user`.*, `address`.* from `user` inner join `address` on `user`.`id` = `address`.`user_id` limit 1 [] {1} 83.479ms}
}) }) //"select `users`.`id`, `contacts`.`id`, `contact_types`.`id` from `users` left join (`contacts` inner join `contact_types` on `contacts`.`contact_type_id` = `contact_types`.`id`) on `users`.`id` = `contacts`.`id` and exists (select * from `countrys` inner join `planets` on `countrys`.`planet_id` = `planet`.`id` and `planet`.`is_settled` = ? where `contacts`.`country` = `countrys`.`country` and `planet`.`population` >= ?)"
Unions
developing
Basic Where Clauses
Where
Usually Where function takes 4 parameters,it’s column,operator,value,and/or Logical Operator. Default Logical Operator is and,default operator is =.
var userStructSlice1 []DefaultUser DB.Table("users").Where("age",">",18,goeloquent.BOOLEAN_AND).Where("balance","=",0,goeloquent.BOOLEAN_AND).Get(&userStructSlice1) //{select * from `users` where `age` > ? and `balance` = ? [18 0] {1} 64.546997ms} fmt.Printf("%#v",userStructSlice1) []main.DefaultUser{main.DefaultUser{Table:"", Id:6, Age:20, Balance:0, Email:"john@apple.com", NickName:"test3"}}
You can skip 4th parameter and/or Logical Operator when it’s and
DB.Table("users").Where("age",">",18).Where("balance","=",0).Get(&userStructSlice1) //select * from `users` where `age` > ? and `balance` = ? [18 0] {2} 62.012133ms
You can skip 2nd parameter when it’s =
DB.Table("users").Where("age",">",18).Where("balance",0).Get(&userStructSlice1) //select * from `users` where `age` > ? and `balance` = ? [18 0] {2} 62.202419ms
map[string]interface
DB.From("users").Where("is_admin", 1).Where(map[string]interface{}{ "name": "Joe", "location": "LA", }, goeloquent.BOOLEAN_OR) //"select * from `users` where `is_admin` = ? or (`name` = ? and `location` = ?)"
[][]interface{}
You can pass by a [][]interface{},each element should be a []interface containing the four parameters that pass to the where function
DB.Table("users").Where([][]interface{}{ {"age", ">", 18, goeloquent.BOOLEAN_AND}, {"balance", "=", 0, goeloquent.BOOLEAN_AND}, }).Get(&userStructSlice1) //select * from `users` where `age` > ? and `balance` = ? [18 0] {2} 62.523877ms
skip parameters works too
DB.Table("users").Where([][]interface{}{ {"age", ">", 18}, {"balance", 0}, }).Get(&userStructSlice1) //select * from `users` where `age` > ? and `balance` = ? [18 0] {2} 61.789099ms
Or Where Clauses
For more readable reason,you may want a OrWhere function
DB.Table("users").Where("age",">",18).OrWhere("balance","=",0).Get(&userStructSlice1) select * from `users` where `age` > ? or `balance` = ? [18 0] {24} 62.61687ms
DB.Table("users").Where("balance", ">", 100).WhereBetween("age", []interface{}{18, 35}).Get(&userStructSlice) //select * from `users` where `balance` > ? and `age` between ? and ? [100 18 35] {0} 68.290583ms
DB.Table("users").WhereNotBetween("age", []interface{}{18, 35}).Get(&userStructSlice) //select * from `users` where `age` not between ? and ? [18 35] {23} 69.032302ms
DB.Table("users").Where("balance", ">", 100).OrWhereNotBetween("age", []interface{}{18, 35}).Get(&userStructSlice) //select * from `users` where `balance` > ? or `age` not between ? and ? [100 18 35] {23} 62.927148ms
DB.Table("users").Where("balance", ">", 100).OrWhereBetween("age", []interface{}{18, 35}).Get(&userStructSlice) //select * from `users` where `balance` > ? or `age` between ? and ? [100 18 35] {7} 63.241122ms
WhereIn/OrWhereIn/WhereNotIn/OrWhereNotIn
DB.Model(&DefaultUser{}).WhereIn("id", []interface{}{1,2,3}).Get(&userStructSlice) //select * from `users` where `id` in (?,?,?) [1 2 3] {1} 62.159353ms
DB.Model(&DefaultUser{}).WhereNotIn("id", []interface{}{2,3,4}).Get(&userStructSlice) //select * from `users` where `id` not in (?,?,?) [2 3 4] {28} 68.078067ms
DB.Table("users").Where("username","john").OrWhereIn("email", []interface{}{"john@gmail.com","john@hotmail.com","john@apple.com","john@outlook.com"}).Get(&userStructSlice) //select * from `users` where `username` = ? or `email` in (?,?,?,?) [john john@gmail.com john@hotmail.com john@apple.com john@outlook.com] {3} 61.692218ms
DB.Table("users").Where("username","joe").OrWhereNotIn("email", []interface{}{"joe@gmail.com","joe@hotmail.com","joe@apple.com","joe@outlook.com"}).Get(&userStructSlice) //select * from `users` where `username` = ? or `email` not in (?,?,?,?) [joe joe@gmail.com joe@hotmail.com joe@apple.com joe@outlook.com] {30} 64.416506ms
WhereNull/OrWhereNull/OrWhereNotNull/WhereNotNull
DB.Table("users").WhereIn("id", []interface{}{1,2,3}).WhereNull("email").Get(&userStructSlice) //select * from `users` where `id` in (?,?,?) and `email` is null [1 2 3] {0} 61.984595ms
DB.Table("users").WhereNotIn("id", []interface{}{2,3,4}).WhereNotNull("email").Get(&userStructSlice) //select * from `users` where `id` not in (?,?,?) and `email` is not null [2 3 4] {27} 62.228735ms
DB.Table("users").Where("username","john").OrWhereNull("email").Get(&userStructSlice) //select * from `users` where `username` = ? or `email` is null [john] {1} 62.454664ms
DB.Table("users").Where("username","joe").OrWhereNotNull("email").Get(&userStructSlice) //select * from `users` where `username` = ? or `email` is not null [joe] {29} 62.256084ms
WhereDate/WhereMonth/WhereDay/WhereYear/WhereTime
var now = time.Now() fmt.Println(now) //2021-11-03 16:00:35.461691 +0800 CST m=+0.166644409 DB.Table("users").WhereDate("created_at", now).Get(&userStructSlice) //{select * from `users` where date(`created_at`) = ? [2021-11-03] {0} 65.800819ms}
DB.Table("users").WhereDate("created_at", "2008-01-03").Get(&userStructSlice) //{select * from `users` where date(`created_at`) = ? [2008-01-03] {0} 66.675012ms}
DB.Table("users").WhereDay("created_at", now).Get(&userStructSlice) //{select * from `users` where day(`created_at`) = ? [03] {0} 65.159437ms}
DB.Table("users").WhereDay("created_at", "06").Get(&userStructSlice) //{select * from `users` where day(`created_at`) = ? [06] {0} 64.92847ms}
DB.Table("users").WhereMonth("created_at", now).Get(&userStructSlice) //{select * from `users` where month(`created_at`) = ? [11] {10} 70.454652ms}
DB.Table("users").WhereMonth("created_at", "06").Get(&userStructSlice) //{select * from `users` where month(`created_at`) = ? [11] {10} 66.694005ms}
DB.Table("users").WhereYear("created_at", now).Get(&userStructSlice) //{select * from `users` where year(`created_at`) = ? [2021] {11} 64.805563ms}
DB.Table("users").WhereYear("created_at", "2020").Get(&userStructSlice) //{select * from `users` where year(`created_at`) = ? [2020] {0} 64.970053ms}
DB.Table("users").WhereTime("created_at", now).Get(&userStructSlice) //{select * from `users` where time(`created_at`) = ? [16:00:35] {0} 65.73327ms}
DB.Table("users").WhereTime("created_at", "3:05:16").Get(&userStructSlice) //{select * from `users` where time(`created_at`) = ? [3:05:16] {0} 66.24917ms}
WhereColumn/OrWhereColumn
DB.Table("users").WhereColumn("age", "=", "balance").Get(&userStructSlice) //{select * from `users` where `age` = `balance` [] {1} 65.095414ms}
DB.Table("users").Where("id",4).OrWhereColumn("age", "=", "balance").Get(&userStructSlice) //{select * from `users` where `id` = ? or `age` = `balance` [4] {2} 66.101059ms}
Logical Grouping
If you need to group an where condition within parentheses,you can pass by a function to Where or use WhereNested function
DB.Table("users").Where("age", ">", 30).OrWhere(func(builder *goeloquent.Builder) { builder.Where("age", ">", 18) builder.Where("balance", ">", 5000) }).Get(&userStructSlice, "username", "email") //select `username`,`email` from `users` where `age` > ? or (`age` > ? and `balance` > ?) [30 18 5000] {8} 62.204423ms
DB.Table("users").Where("age", ">", 30).WhereNested([][]interface{}{ {"age", ">", 18}, {"balance", ">", 5000}, },goeloquent.BOOLEAN_OR).Get(&userStructSlice, "username", "email") //select `username`,`email` from `users` where `age` > ? or (`age` > ? and `balance` > ?) [30 18 5000] {8} 64.868523ms
Subquery Where Clauses
DB.Table("users").Where("age", ">", 0).WhereSub("id","in", func(builder *goeloquent.Builder) { builder.From("users").Where("balance",">",0).Select("id") //don't use any finisher function like first/find/get/value/pluck,otherwise it will turn into execute two seperated sql },goeloquent.BOOLEAN_OR).Get(&userStructSlice)
DB.Table("users").Where("age", ">", 0).OrderBy("balance",goeloquent.ORDER_DESC).OrderBy("id").Get(&userStructSlice) //{select * from `users` where `age` > ? order by `balance` desc , `id` asc [0] {24} 73.264891ms}
Grouping
var m []map[string]interface{} DB.Table("comments").GroupBy("commentable_type").Get(&m,"commentable_type",goeloquent.Expression{Value: "count(*) as c"}) //{select `commentable_type`,count(*) as c from `comments` group by `commentable_type` [] {2} 64.624213ms} fmt.Println(string(m[0]["commentable_type"].([]byte))) //post fmt.Println(m[0]["c"]) //2
When using Select to specify columns, we will quote columns with “" as string, if you want avoid this, use Expression`
Having
var m []map[string]interface{} DB.Table("comments").GroupBy("commentable_type").Having("c",">",2).Get(&m,"commentable_type",goeloquent.Expression{Value: "count(*) as c"}) //{select `commentable_type`,count(*) as c from `comments` group by `commentable_type` having `c` > ? [2] {1} 66.393615ms} fmt.Println(string(m[0]["commentable_type"].([]byte))) //video fmt.Println(m[0]["c"]) //3
HavingBetween
var m []map[string]interface{} DB.Table("comments").GroupBy("commentable_type").HavingBetween("c",[]interface{}{0,3}).Get(&m,"commentable_type",goeloquent.Expression{Value: "count(*) as c"}) //{select `commentable_type`,count(*) as c from `comments` group by `commentable_type` having `c` between? and ? [0 3] {2} 65.1953ms} fmt.Println(string(m[0]["commentable_type"].([]byte))) //video fmt.Println(m[0]["c"]) //3
You can use Expression to update column base on another column
Delete
r,err:=DB.Table("users").Where("id",2).Delete() if err!=nil { panic(err.Error()) } fmt.Println(r.RowsAffected()) //{ delete from `users` where `id` = ? [2] {0xc000102000 0xc00001e150} 73.972219ms} //1 <nil>
Pessimistic Locking
var us []map[string]interface{} DB.Table("users").LockForUpdate().Where("id", "<", 100).Get(&us) //{select * from `users` where `id` < ? for update [100] {16} 66.21529ms}
DB.Table("users").SharedLock().Where("id", "<", 100).Get(&us) //{select * from `users` where `id` < ? lock in share mode [100] {16} 67.434753ms}