Gorm Support a with clause (CTE)

Describe the feature

Implement with.go to support a With clause (CTE).

I have a fork repository that implements this API and has been successfully tested.

func (db *DB) With(alias interface{}, query interface{}, args ...interface{}) (tx *DB)

Usage

db.With("cte_name", "SELECT * FROM users").Table("cte_name").Scan(&users)
// WITH `cte_name` AS (SELECT * FROM users) SELECT * FROM `cte_name`

db.With("cte_name", "SELECT * FROM users WHERE name = ?", "name").Table("cte_name").Scan(&users)
// WITH `cte_name` AS (SELECT * FROM users WHERE name = 'name') SELECT * FROM `cte_name`

db.With("cte_name", db.Table("users").Where("name = ?", "name")).Table("cte_name").Scan(&users)
// WITH `cte_name` AS (SELECT * FROM users WHERE name = 'name') SELECT * FROM `cte_name`

db.With(clause.CTE{Alias: "cte_name", Columns: []string{"id", "name"}}, db.Table("users")).Table("cte_name").Scan(&users)
// WITH `cte_name`(`id`,`name`) AS (SELECT * FROM users) SELECT * FROM `cte_name`

db.With(clause.CTE{Recursive: true, Alias: "cte_name"}, db.Table("users")).Table("cte_name").Scan(&users)
// WITH RECURSIVE `cte_name` AS (SELECT * FROM users) SELECT * FROM `cte_name`

Can I make a pull request?

Motivation

I wanted to use gorm to write the with clause, but I couldn't find a way to do it in the documentation or in past issues.

First of all, I successfully implemented the StatementModifier to write the With clause, but that often tends to make the code longer.

The with clause is defined in SQL99 and is implemented in any DB that supports gorm. I figured that there must be an official gorm supported way to write this, so I found the unimplemented with.go while doing some research and decided to implement it.

Related Issues

2483 (No comments there)

Comment From: WinterYukky

Remarks: With clause is not support in MySQL < 8.

Comment From: jinzhu

Hello @WinterYukky

I would prefer to write code like following but don't use the with clause:

db.Table("? AS cte_name", db.Model(&User{}).Select("id", "name").Where("name = ?", "name")).Scan(&users)

Comment From: WinterYukky

Hello, and thank you @jinzhu

I wanted to use the With clause because I couldn't use it in the From clause, but I understood.

Comment From: WinterYukky

~~For those who want to use the With clause, please refer to the With implementation of StatementModifier in my repository.~~ If you want to use "with clause", please check https://github.com/go-gorm/gorm/issues/3955#issuecomment-881866443.

Comment From: shudipta

I also prefer with clause support. If you want, I can write a PR.

Comment From: WinterYukky

Thank you @shudipta for your help 😄! However, the maintainer doesn't seem to want to support "with clause".

If more people want to support "with clause", I will write a PR.

Comment From: WinterYukky

I'm sorry to reply for closed issue. Currenty, gorm support to customize clauses, unlike when I opened this PR. This means if we write struct to implemented clause.Interface, we can use any clauses without gorm's support. This change is very nice.

I made the plugin project for unsupported clauses by gorm. If you want to use "with clause", please check this repository. It is useful than StatementModifier.

Comment From: ceferrari

I'm sorry to reply for closed issue. Currenty, gorm support to customize clauses, unlike when I opened this PR. This means if we write struct to implemented clause.Interface, we can use any clauses without gorm's support. This change is very nice.

I made the plugin project for unsupported clauses by gorm. If you want to use "with clause", please check this repository. It is useful than StatementModifier.

Awesome plugin! I used it to build a complex multilevel CTE that is not even exemplified in the documentation and it worked perfectly.

I could have used Raw for my query, but I also had to use Method Chaining with some joins and scopes, and they would't work with Raw but they did work with your plugin!

gorm should definetly incorporate it.

Comment From: lifegit

@WinterYukky thank you very much!

Comment From: rashetu-notch

I'm sorry to reply for closed issue. Currenty, gorm support to customize clauses, unlike when I opened this PR. This means if we write struct to implemented clause.Interface, we can use any clauses without gorm's support. This change is very nice. I made the plugin project for unsupported clauses by gorm. If you want to use "with clause", please check this repository. It is useful than StatementModifier.

Awesome plugin! I used it to build a complex multilevel CTE that is not even exemplified in the documentation and it worked perfectly.

I could have used Raw for my query, but I also had to use Method Chaining with some joins and scopes, and they would't work with Raw but they did work with your plugin!

gorm should definetly incorporate it.

Hey do you have any example on that?

Comment From: ceferrari

I'm sorry to reply for closed issue. Currenty, gorm support to customize clauses, unlike when I opened this PR. This means if we write struct to implemented clause.Interface, we can use any clauses without gorm's support. This change is very nice. I made the plugin project for unsupported clauses by gorm. If you want to use "with clause", please check this repository. It is useful than StatementModifier.

Awesome plugin! I used it to build a complex multilevel CTE that is not even exemplified in the documentation and it worked perfectly. I could have used Raw for my query, but I also had to use Method Chaining with some joins and scopes, and they would't work with Raw but they did work with your plugin! gorm should definetly incorporate it.

Hey do you have any example on that?

Sure:

SELECT *
FROM (
    WITH
    cards_multi_version AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY version_id ORDER BY created_at DESC) AS rn
        FROM cards
        WHERE is_published = TRUE AND version_id IS NOT NULL
    ),
    cards_single_version AS (
        SELECT *, 1 AS rn
        FROM cards
        WHERE is_published = TRUE AND version_id IS NULL AND id NOT IN (SELECT version_id FROM cards_multi_version)
    )
    SELECT *
    FROM cards_multi_version
    WHERE rn = 1
    UNION ALL
    SELECT *
    FROM cards_single_version
) cards
WHERE EXISTS (
    SELECT 1
    FROM card_classifications
    WHERE card_classifications.card_id = cards.id
    AND card_classifications.id IN (SELECT UNNEST(STRING_TO_ARRAY('{UUID_1},{UUID_2},...,{UUID_N}', ','))
)

Gets translated into:

func (r Repository) Get(ctx context.Context, classificationsStr string) ([]entities.card, error) {
    var db *gorm.DB

    cte := db.Clauses(exclause.With{CTEs: []exclause.CTE{
        {Name: "cards_multi_version", Subquery: clause.Expr{SQL: `
            SELECT *, ROW_NUMBER() OVER(PARTITION BY parent_id ORDER BY created_at DESC) AS rn
            FROM cards
            WHERE is_published = TRUE AND parent_id IS NOT NULL
        `}},
        {Name: "cards_single_version", Subquery: clause.Expr{SQL: `
            SELECT *, 1 AS rn
            FROM cards
            WHERE is_published = TRUE AND parent_id IS NULL AND id NOT IN (SELECT parent_id FROM cards_multi_version)
        `}},
    }}).
        Table("cards_multi_version").Where("rn = 1").
        Clauses(exclause.NewUnion("ALL ?", db.Table("cards_single_version")))

    db = db.Table("(?) as cards", cte).Where(`EXISTS (
        SELECT 1
        FROM card_classifications
        WHERE card_classifications.card_id = cards.id
        AND card_classifications.catalog_id IN (SELECT UNNEST(STRING_TO_ARRAY(?, ',')))
    )`, classificationsStr)

    db = db.Scopes(
        // hidden
    )

    var cards []entities.card

    err := db.Find(&cards).Error
    if err != nil {
        return nil, err
    }

    return cards, nil
}

The WHERE EXISTS used to be a JOIN when I posted my first answer, that's why I mentioned it. But it works both ways, you are free to use Method Chaining as much as you need

Describe the feature

Motivation

Related Issues

2483 (No comments there)