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
// #271 (comment)
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.