Add UNION operator

[umisama]

I want to express query SELECT a FROM b WHERE col>? UNION SELECT a FROM c WHERE col<? but squirrel is not supporting UNION.

I think, The following example code is good design for support UNION.

q, attrs, err := sq.Union(
        sq.Select("*").From("a").Where("col > ?", hoge),
        sq.Select("*").From("b").Where("col < ?", piyo)).ToSql()

q == "SELECT a FROM b WHERE col>? UNION SELECT a FROM c WHERE col<?"
attrs == []interface{hoge, piyo}

What do you think so? If you agree on this idea, I'll create patch and send PR. Thanks!

[umisama]

I fixed this issue on 82e6fd95c120574b8797f12b8f03673067b6f27a. I created SelectBuilder.Union() and SelectBuilder.UnionAll().

thanks!

[nwidger]

I don't see the above commit in either the v1 or master branch. I would really appreciate UNION support, is there any reason this hasn't been merged?

[nd2s]

Is there any workaround for using UNION?

[lann]

Something like this should work (untested):

first_select := sq.Select("col1").From("table1")
second_select := sq.Select("col1").From("table2")

sql, args, _ := second_select.ToSql()
union_select := first_select.Suffix("UNION " + sql, args...)

[rbruggem]

Any updates on this? If https://github.com/Masterminds/squirrel/commit/82e6fd95c120574b8797f12b8f03673067b6f27a works, can it be merged in? Thanks

[lann]

If someone is willing to make sure it works on v1 HEAD and submit a PR I'd be inclined to merge.

[wI2L]

Any news about this PR?

[aladine]

Something like this should work (untested):

union_select := first_select.Suffix("UNION " + sql, args)

I test this and found that it args needs to be spreaded as arguments:

 firstSelect := sq.Select("col1").From("table1")
 secondSelect := sq.Select("col1").From("table2")

 sql, args, _ := secondSelect.ToSql()
 unionSelect := firstSelect.Suffix("UNION " + sql, args...)

[lann]

Thanks. I updated the example above.

[fenollp]

Now that https://github.com/Masterminds/squirrel/pull/140 is closed can we reopen this?

I have this subquery which is the union of 3 subqueries that all have arguments...

[ntbosscher]

Today I was working on something where I needed Union functionality and the Suffix method wasn't good enough for what I was doing. I jammed on an idea I found at https://github.com/Masterminds/squirrel/pull/140#issuecomment-412579733.

Here's my code if anyone wants to clean it up and make a PR

func init() {
    builder.Register(UnionBuilder{}, unionData{})
}

type unionSelect struct {
    op       string // e.g. "UNION"
    selector squirrel.SelectBuilder
}

type unionData struct {
    Selects []*unionSelect
    Limit   string
    OrderBy []string
}

type UnionBuilder builder.Builder

func (u UnionBuilder) setProp(key string, value interface{}) UnionBuilder {
    return builder.Set(u, key, value).(UnionBuilder)
}

func (u UnionBuilder) ToSql() (sql string, args []interface{}, err error) {

    data := builder.GetStruct(u).(unionData)

    if len(data.Selects) == 0 {
        err = errors.New("require a minimum of 1 select clause in UnionBuilder")
        return
    }

    value, _ := builder.Get(data.Selects[0].selector, "PlaceholderFormat")
    placeholderFmt := value.(squirrel.PlaceholderFormat)

    sqlBuf := &bytes.Buffer{}
    var selArgs []interface{}
    var selSql string

    for index, selector := range data.Selects {

        // use a no-change formatter to prevent issues with numbering args
        sel := selector.selector.PlaceholderFormat(squirrel.Question)

        selSql, selArgs, err = sel.ToSql()
        if err != nil {
            return
        }

        if index == 0 {
            sqlBuf.WriteString(selSql) // no operator for first select-clause
        } else {
            sqlBuf.WriteString(" " + selector.op + " ( " + selSql + " ) ")
        }

        args = append(args, selArgs...)
    }

    if len(data.OrderBy) > 0 {
        sqlBuf.WriteString(" ORDER BY ")
        sqlBuf.WriteString(strings.Join(data.OrderBy, ","))
    }

    if data.Limit != "" {
        sqlBuf.WriteString(" LIMIT ")
        sqlBuf.WriteString(data.Limit)
    }

    sql, err = placeholderFmt.ReplacePlaceholders(sqlBuf.String())
    return
}

func (u UnionBuilder) Union(selector squirrel.SelectBuilder) UnionBuilder {
    return builder.Append(u, "Selects", &unionSelect{op: "UNION", selector: selector}).(UnionBuilder)
}

func (u UnionBuilder) setFirstSelect(selector squirrel.SelectBuilder) UnionBuilder {
    return builder.Append(u, "Selects", &unionSelect{op: "", selector: selector}).(UnionBuilder)
}

func (u UnionBuilder) Limit(n uint) UnionBuilder {
    return u.setProp("Limit", fmt.Sprintf("%d", n))
}

func (u UnionBuilder) OrderBy(orderBys ...string) UnionBuilder {
    return u.setProp("OrderBy", orderBys)
}

func Union(a squirrel.SelectBuilder, b squirrel.SelectBuilder) UnionBuilder {
    ub := UnionBuilder{}
    ub = ub.setFirstSelect(a)

    return ub.Union(b)
}

[ntbosscher]

Oh, and please do re-open this - it's still a missing feature

[ntbosscher]

Here's an updated version that fixes some issues with unions in nested selects (same as #183)

// Package squtil is a place for utils to help with github.com/Masterminds/squirrel
package squtil

import (
    "bytes"
    "errors"
    "fmt"
    sq "github.com/Masterminds/squirrel"
    "github.com/lann/builder"
    "strings"
)

func init() {
    builder.Register(UnionBuilder{}, unionData{})
}

type unionSelect struct {
    op       string // e.g. "UNION"
    selector sq.SelectBuilder
}

type unionData struct {
    Selects           []*unionSelect
    Limit             string
    OrderBy           []string
    PlaceholderFormat sq.PlaceholderFormat
}

// UnionBuilder is a (rather hack) implementation of Unions for squirrel query builder. They
// currently don't offer this feature. When they do, this code should be trashed
type UnionBuilder builder.Builder

func (u UnionBuilder) setProp(key string, value interface{}) UnionBuilder {
    return builder.Set(u, key, value).(UnionBuilder)
}

func (u UnionBuilder) ToSql() (sql string, args []interface{}, err error) {
    data := builder.GetStruct(u).(unionData)

    if len(data.Selects) == 0 {
        err = errors.New("require a minimum of 1 select clause in UnionBuilder")
        return
    }

    sqlBuf := &bytes.Buffer{}
    var selArgs []interface{}
    var selSql string

    for index, selector := range data.Selects {

        selSql, selArgs, err = selector.selector.ToSql()
        if err != nil {
            return
        }

        if index == 0 {
            sqlBuf.WriteString(selSql) // no operator for first select-clause
        } else {
            sqlBuf.WriteString(" " + selector.op + " ( " + selSql + " ) ")
        }

        args = append(args, selArgs...)
    }

    if len(data.OrderBy) > 0 {
        sqlBuf.WriteString(" ORDER BY ")
        sqlBuf.WriteString(strings.Join(data.OrderBy, ","))
    }

    if data.Limit != "" {
        sqlBuf.WriteString(" LIMIT ")
        sqlBuf.WriteString(data.Limit)
    }

    sql = sqlBuf.String()
    return
}

func (u UnionBuilder) Union(selector sq.SelectBuilder) UnionBuilder {
    // use ? in children to prevent numbering issues
    selector = selector.PlaceholderFormat(sq.Question)

    return builder.Append(u, "Selects", &unionSelect{op: "UNION", selector: selector}).(UnionBuilder)
}

func (u UnionBuilder) setFirstSelect(selector sq.SelectBuilder) UnionBuilder {

    // copy the PlaceholderFormat value from children since we don't know what it should be
    value, _ := builder.Get(selector, "PlaceholderFormat")
    bld := u.setProp("PlaceholderFormat", value)

    // use ? in children to prevent numbering issues
    selector = selector.PlaceholderFormat(sq.Question)

    return builder.Append(bld, "Selects", &unionSelect{op: "", selector: selector}).(UnionBuilder)
}

func (u UnionBuilder) Limit(n uint) UnionBuilder {
    return u.setProp("Limit", fmt.Sprintf("%d", n))
}

func (u UnionBuilder) OrderBy(orderBys ...string) UnionBuilder {
    return u.setProp("OrderBy", orderBys)
}

func (u UnionBuilder) PlaceholderFormat(fmt sq.PlaceholderFormat) UnionBuilder {
    return u.setProp("PlaceholderFormat", fmt)
}

func Union(a sq.SelectBuilder, b sq.SelectBuilder) UnionBuilder {
    ub := UnionBuilder{}
    ub = ub.setFirstSelect(a)
    return ub.Union(b)
}

func NewUnion() UnionBuilder {
    return UnionBuilder{}
}

func SelectFromUnion(selectBuilder sq.SelectBuilder, union UnionBuilder, alias string) sq.SelectBuilder {
    // use ? in child to prevent numbering issues
    union = union.PlaceholderFormat(sq.Question)

    return builder.Set(selectBuilder, "From", sq.Alias(union, alias)).(sq.SelectBuilder)
}

[johan-lejdung]

Any update on this? I can see that the PR https://github.com/Masterminds/squirrel/pull/140 were closed without any information regarding if there is an implementation plan or not.

[casey-speer]

In case anyone needs this still, I've found that

sq.Select("col").From("table").SuffixExpr(sq.ConcatExpr(" UNION ALL ", sq.Select("col").From("other_table")))

also works. You can also use PrefixExpr or SuffixExpr instead of ConcatExpr I believe.

[johan-lejdung]

Here is a shorthand function we use in code sometimes.

func WithUnion(builder squirrel.SelectBuilder, unionQuery squirrel.SelectBuilder) (squirrel.SelectBuilder, error) {
    query, params, err := unionQuery.ToSql()
    if err != nil {
        return builder, fmt.Errorf("Failed to build union query: %w", err)
    }

    return builder.Suffix("UNION "+query, params...), nil
}