gorm-extra-clause-plugin

The clause support plugin for gorm, that not supported by gorm.

Support clauses

• [x] WITH (CTE)
• [x] UNION
• [x] INTERSECT
• [x] EXCEPT

Install

go get github.com/WinterYukky/gorm-extra-clause-plugin

Get Started

[!IMPORTANT] Do not forget the plugin initialisation code db.Use(extraClausePlugin.New().

package main

import (
  "gorm.io/gorm"
  "gorm.io/driver/sqlite"
  // Add plugin package
  extraClausePlugin "github.com/WinterYukky/gorm-extra-clause-plugin"
  "github.com/WinterYukky/gorm-extra-clause-plugin/exclause"
)

func main() {
    db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }
    // Insert this line
    db.Use(extraClausePlugin.New())
    // Use exclauses
    db.Clauses(exclause.NewWith("cte", db.Table("users"))).Table("cte").Scan(&users)
}

Examples

WITH (CTE)

// WITH `cte` AS (SELECT * FROM `users`) SELECT * FROM `cte`
db.Clauses(exclause.NewWith("cte", "SELECT * FROM `users`")).Table("cte").Scan(&users)

// WITH `cte` AS (SELECT * FROM `users` WHERE `name` = 'WinterYukky') SELECT * FROM `cte`
db.Clauses(exclause.NewWith("cte", "SELECT * FROM `users` WHERE `name` = ?", "WinterYukky")).Table("cte").Scan(&users)

// WITH `cte` AS (SELECT * FROM `users` WHERE `name` = 'WinterYukky') SELECT * FROM `cte`
db.Clauses(exclause.NewWith("cte", db.Table("users").Where("`name` = ?", "WinterYukky"))).Table("cte").Scan(&users)

// WITH `cte` (`id`,`name`) AS (SELECT * FROM `users`) SELECT * FROM `cte`
db.Clauses(exclause.With{CTEs: []exclause.CTE{{Name: "cte", Columns: []string{"id", "name"}, Subquery: exclause.Subquery{DB: db.Table("users")}}}}).Table("cte").Scan(&users)

// WITH RECURSIVE `cte` AS (SELECT * FROM `users`) SELECT * FROM `cte`
db.Clauses(exclause.With{Recursive: true, CTEs: []exclause.CTE{{Name: "cte", Subquery: exclause.Subquery{DB: db.Table("users")}}}}).Table("cte").Scan(&users)

// WITH `cte` AS MATERIALIZED (SELECT * FROM `users`) SELECT * FROM `cte`
db.Clauses(exclause.With{CTEs: []exclause.CTE{{Name: "cte", Subquery: exclause.Subquery{DB: db.Table("users")}, Materialized: exclause.CTEMaterialize}}}).Table("cte").Scan(&users)

// WITH `cte` AS NOT MATERIALIZED (SELECT * FROM `users`) SELECT * FROM `cte`
db.Clauses(exclause.With{CTEs: []exclause.CTE{{Name: "cte", Subquery: exclause.Subquery{DB: db.Table("users")}, Materialized: exclause.CTENotMaterialize}}}).Table("cte").Scan(&users)

// Using helper functions for materialized CTEs
// WITH `cte1` AS MATERIALIZED (...), `cte2` AS NOT MATERIALIZED (...) SELECT * FROM `cte1`
db.Clauses(exclause.With{CTEs: []exclause.CTE{
    exclause.NewMaterializedCTE("cte1", exclause.Subquery{DB: db.Table("users")}),
    exclause.NewNotMaterializedCTE("cte2", exclause.Subquery{DB: db.Table("products")}),
}}).Table("cte1").Scan(&users)

UNION

// SELECT * FROM `general_users` UNION SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewUnion("SELECT * FROM `admin_users`")).Scan(&users)

// SELECT * FROM `general_users` UNION SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewUnion(db.Table("admin_users"))).Scan(&users)

// SELECT * FROM `general_users` UNION ALL SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewUnion("ALL ?", db.Table("admin_users"))).Scan(&users)

INTERSECT

// SELECT * FROM `general_users` INTERSECT SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewIntersect("SELECT * FROM `admin_users`")).Scan(&users)

// SELECT * FROM `general_users` INTERSECT SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewIntersect(db.Table("admin_users"))).Scan(&users)

// SELECT * FROM `general_users` INTERSECT ALL SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewIntersect("ALL ?", db.Table("admin_users"))).Scan(&users)

EXCEPT

// SELECT * FROM `general_users` EXCEPT SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewExcept("SELECT * FROM `admin_users`")).Scan(&users)

// SELECT * FROM `general_users` EXCEPT SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewExcept(db.Table("admin_users"))).Scan(&users)

// SELECT * FROM `general_users` EXCEPT ALL SELECT * FROM `admin_users`
db.Table("general_users").Clauses(exclause.NewExcept("ALL ?", db.Table("admin_users"))).Scan(&users)