query

Overview ¶

Go write SQL

import . "github.com/cxr29/scrud/query"

// Create
query, args, err := Insert("Table1").Columns(
	"Column1", "Column2", "Column3", ...
).Values(
	false, 0, "hi", ...
).Values(
	true, 1, "cxr", ...
).Expand(new(MySQL))

// Retrieve
query, args, err = Select(
	"Column1",
	"Table1.Column2",                             // `Table1`.`Column1`
	Expr("COUNT(DISTINCT `Column3`) AS `Count`"), // -- expression also ok
	...
).From("Table1", ...).LeftJoin("Table2", // LEFT JOIN `Table2` USING (`Column2`, ...)
	"Column2", // -- join using when string
	...
).RightJoin("Table3", // RIGHT JOIN `Table3` ON `Column4`=? AND `Table1`.`Column1`+`Table3`.`Column1`>? ... -- true, 1
	Eq("Column4", true),                            // -- join on when Condition
	Cond("`Table1.Column1`+`Table3.Column1`>?", 1), // -- AND
	...
).FullJoin(
	As(Select().From(`Table4`), "a"), // (SELECT * FROM `Table4`) AS `a` -- join expression as subquery
	...
).Where( // -- AND
	In("Column5", 2, 3, 4),    // `Column5` IN (?,?,?) -- 2,3,4
	Contains("Column6", "_"), // `Column6` LIKE ? -- %\_%
	Cond("`Table3.Column3` > `a.Column4`"),
	...
).GroupBy(
	"Column1",
	Expr("EXTRACT(YEAR FROM Column7`)"), // -- expression also ok
).Having( // -- same as Where
	...
).OrderBy(
	Desc("Column1"),                     // `Column1` DESC
	Expr("EXTRACT(YEAR FROM Column7`)"), // -- expression also ok
).Limit(5).Offset(6).Expand(new(Postgres))

// Update
query, args, err = Update("Table1").Set(
	"Column1", true,
).Set( // -- expression also ok
	"Column2", Expr("Column2+?", 1), // `Column2`=`Column2`+? -- 1
).Where(
	... // -- same as Retrieve Where
).OrderBy(
	... // -- same as Retrieve OrderBy
).Limit(...).Expand(new(Sqlite))

// Delete
query, args, err = Delete("Table1").Where(
	... // -- same as Retrieve Where
).OrderBy(
	... // -- same as Retrieve OrderBy
).Limit(...).Expand(...)

See https://github.com/cxr29/scrud for more details