func Example() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
for _, query := range []string{
`CREATE TABLE test_model (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
addr TEXT NOT NULL
)`,
`INSERT INTO test_model VALUES (1, 'test1', 'addr1')`,
`INSERT INTO test_model VALUES (2, 'test2', 'addr2')`,
`INSERT INTO test_model VALUES (3, 'test3', 'addr3')`,
} {
if _, err := db.DB().Exec(query); err != nil {
log.Fatal(err)
}
}
var results []TestModel
// SELECT * FROM "test_model";
if err := db.Select(&results); err != nil {
log.Fatal(err)
}
fmt.Println(results)
// Output: [{1 test1 addr1} {2 test2 addr2} {3 test3 addr3}]
}
func ExampleDB_Select_like() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" WHERE "name" LIKE "alice%";
if err := db.Select(&results, db.Where("name").Like("alice%")); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_whereNested() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" WHERE "id" = 1 OR ("name" = "alice" AND "addr" != "Tokyo");
if err := db.Select(&results, db.Where("id", "=", 1).Or(db.Where("name", "=", "alice").And("addr", "!=", "Tokyo"))); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_in() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" WHERE "id" IN (1, 3, 5);
if err := db.Select(&results, db.Where("id").In(1, 3, 5)); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_columns() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model"."id", "test_model"."name" FROM "test_model";
if err := db.Select(&results, []string{"id", "name"}); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_leftJoin() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" LEFT JOIN "m2" ON "test_model"."name" = "m2"."body" WHERE "m2"."body" IS NULL;
if err := db.Select(&results, "name", db.LeftJoin(&M2{}).On("name", "=", "body").Where(&M2{}, "body").IsNull()); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_countDistinct() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var result int64
// SELECT COUNT(DISTINCT "test_model"."name") FROM "test_model";
if err := db.Select(&result, db.Count(db.Distinct("name")), db.From(TestModel{})); err != nil {
log.Fatal(err)
}
fmt.Println(result)
}
func ExampleDB_Select_distinct() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT DISTINCT "test_model"."name" FROM "test_model";
if err := db.Select(&results, db.Distinct("name")); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_offset() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" OFFSET 10;
if err := db.Select(&results, db.Offset(10)); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_limit() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" LIMIT 3;
if err := db.Select(&results, db.Limit(3)); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_orderByMultiple() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" ORDER BY "name" DESC, "addr" DESC;
if err := db.Select(&results, db.OrderBy("name", genmai.DESC, "addr", genmai.DESC)); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_join() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
type M2 struct {
Id int64
Body string
}
var results []TestModel
// SELECT "test_model".* FROM "test_model" JOIN "m2" ON "test_model"."id" = "m2"."id";
if err := db.Select(&results, "name", db.Join(&M2{}).On("id")); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}
func ExampleDB_Select_complex() {
db, err := genmai.New(&genmai.SQLite3Dialect{}, ":memory:")
if err != nil {
log.Fatal(err)
}
var results []TestModel
// SELECT "test_model"."name" FROM "test_model"
// WHERE "name" LIKE "%alice%" OR ("id" > 100 AND "id" < 200) OR ("id" BETWEEN 700 AND 1000)
// ORDER BY "id" ASC LIMIT 2 OFFSET 5
if err := db.Select(&results, "name", db.Where("name").
Like("%alice%").
Or(db.Where("id", ">", 100).And("id", "<", 200)).
Or(db.Where("id").Between(700, 1000)).
Limit(2).Offset(5).OrderBy("id", genmai.ASC),
); err != nil {
log.Fatal(err)
}
fmt.Println(results)
}