「GoCN酷Go推荐」postgresql ORM 框架 go-pg系列(三）

之前的两篇都是入门篇，今天我们深入了解下go-pg的select,insert,delete,update的多种玩法。

【GoCN酷Go推荐】postgresql ORM 框架 go-pg系列(一）

【GoCN酷Go推荐】postgresql ORM 框架 go-pg系列(二）

零、设计理念

我们的目标是帮助您编写SQL，而不是用自定义方言隐藏或替换它。Go-pg查询生成器有以下帮助:

•将长查询分割为逻辑上分离的块;•用正确的转义值来替换?占位符•通过go models生成列的列表和一些join连接

举例有如下go代码：

err := db.Model(book).
    ColumnExpr("lower(name)").
    Where("? = ?", pg.Ident("id"), "some-id").
    Select()

生成如下的sql语句

SELECT lower(name)
FROM "books"
WHERE "id" = 'some-id'

一、Select查询

1、1 sql和go-pg对照表

SQL	go-pg
SELECT col1, col2	Column("col1", "col2")
SELECT col1, col2	ColumnExpr("col1, col2")
SELECT count()	ColumnExpr("count()")
SELECT count("id")	ColumnExpr("count(?)", pg.Ident("id"))
FROM "table1", "table2"	Table("table1", "table2")
FROM table1, table2	TableExpr("table1, table2")
JOIN table1 ON col1 = 'value1'	Join("JOIN table1 ON col1 = ?", "value1")
JOIN table1 ON col1 = 'value1'	Join("JOIN table1").JoinOn("col1 = ?", "value1")
LEFT JOIN table1 ON col1 = 'value1'	Join("LEFT JOIN table1 ON col1 = ?", "value1")
WHERE id = 1	Where("id = ?", 1)
WHERE "foo" = 'bar'	Where("? = ?", pg.Ident("foo"), "bar")
WHERE id = 1 OR foo = 'bar'	Where("id = ?", 1).WhereOr("foo = ?", "bar")
GROUP BY "col1", "col2"	Group("col1", "col2")
GROUP BY col1, col2	GroupExpr("col1, col2")
GROUP BY "col1", "col2"	GroupExpr("?, ?", pg.Ident("col1"), pg.Ident("col2"))
ORDER BY "col1" ASC	Order("col1 ASC")
ORDER BY col1 ASC	OrderExpr("col1 ASC")
ORDER BY "col1" ASC	OrderExpr("? ASC", pg.Ident("col1"))
LIMIT 10	Limit(10)
OFFSET 1000	Offset(1000)

1、2 示例