Does squirrel support, or plan to support, common table expressions

[mojochao]

Is there a way to generate a CTE? I didn't see anything in docs mentioning this. I'm using PostgreSQL.

[lann]

Only via the generic Prefix method, e.g. .Select("*").Prefix("WITH ...") -> WITH ... SELECT *

[tnissen375]

May be it solves your use case. Support CTE and UNION https://github.com/tnissen375/squirrel

Drop me a line if it does not work as expected. (Only tested with MySql)

[tjsampson]

@lann

There's currently a few forks of this that support CTEs. Any reason they are not getting merged back in? IMO, supporting CTEs/With Clause is a pretty common sql operation. I attempted to set them up in this repo yesterday and was never really pleased with the solution (using prefixes), so now I am using https://github.com/jack-t/squirrel but it looks like https://github.com/tnissen375/squirrel is running the same implementation and keeps the repo a little more up to date with the upstream changes here.

There's also an open PR here from this repo which seems to be a bit more fleshed out than some of the others.

I am fine with running a fork if need be; definitely appreciate all the hard work on this package. I am just curious if this feature will ever get merged back in?

[lann]

Probably not. From the readme:

Squirrel is "complete".

Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily > respond to them. If another fork (or substantially similar project) actively improves on what > Squirrel does, let me know and I may link to it here.

[ilxqx]

I also need this feature. I think CTE is a very common SQL requirement.

[tjsampson]

I also need this feature. I think CTE is a very common SQL requirement.

@ilxqx I am just leveraging a fork that supports CTEs (there are a few out there) and then using a go mod replace to override this pkg:

// we are using the fork of squirrel because the original one does not support CTEs // https://pkg.go.dev/github.com/jack-t/squirrel // https://github.com/Masterminds/squirrel/issues/271#issuecomment-1725792877 replace github.com/Masterminds/squirrel v1.5.4 => github.com/jack-t/squirrel v1.6.0

Then you can use it like so:

package blah

import (
    sq "github.com/Masterminds/squirrel"
)

queryExpression := sq.Select(`col1,col2`).From('some_table')

queryCTE := sq.CTE{
    Alias:      "some_alias",
    Recursive:  false,
    Expression: queryExpression
}

mainQuery := sq.Select(`col_foo,col_bar`.From('other_table').WithCTE(queryCTE).InnerJoin('some_alias.col1 ON other_table.col_foo`) 

Hope this helps.

[ilxqx]

@tjsampson Very nice, thank you.

[frbrno]

I was looking for cte support aswell, came up with this idea.

func With(b ...sq.SelectBuilder) (string, []any, error) {
    var args_all []any
    var sql_all string
    for i := 0; i < len(b); i++ {
        sql, args, err := b[i].ToSql()
        if err != nil {
            return "", nil, err
        }
        args_all = append(args_all, args...)

        if i == 0 {
            sql_all += fmt.Sprintf("with cte%v as (%s)", i+1, sql)
        } else if i > 0 && i < len(b)-1 {
            sql_all += fmt.Sprintf(", cte%v as (%s) ", i+1, sql)
        } else if i == len(b)-1 {
            sql_all += sql
        }
    }
    return sql_all, args_all, nil
}

use it like this, the cte names get auto generated eg. "cte1", "cte2" ...

sql, args, err := With(
  sq.Select("id").
      From("tags").
      Where(sq.Eq{"name": []string{ "tag1", "tag2" }).
      GroupBy("id"),
  sq.Select("files.*").
      From("files").
      Join("file_tags on files.id = file_tags.id_file").
      Where("file_tags.id_tag in cte1"),
  sq.Select("*").
      From("cte2"),
)

sql == with cte1 as (SELECT id FROM tags WHERE name IN (?,?) GROUP BY id), cte2 as (SELECT files.* FROM files JOIN file_tags on files.id = file_tags.id_file WHERE file_tags.id_tag in cte1) SELECT * FROM cte2