github.com/acoshift/pgsql@v0.15.3/pgstmt/union_test.go (about)

     1  package pgstmt_test
     2  
     3  import (
     4  	"testing"
     5  
     6  	"github.com/stretchr/testify/assert"
     7  
     8  	"github.com/acoshift/pgsql/pgstmt"
     9  )
    10  
    11  func TestUnion(t *testing.T) {
    12  	t.Parallel()
    13  
    14  	cases := []struct {
    15  		name   string
    16  		result *pgstmt.Result
    17  		query  string
    18  		args   []any
    19  	}{
    20  		{
    21  			"union select",
    22  			pgstmt.Union(func(b pgstmt.UnionStatement) {
    23  				b.Select(func(b pgstmt.SelectStatement) {
    24  					b.Columns("id")
    25  					b.From("table1")
    26  				})
    27  				b.AllSelect(func(b pgstmt.SelectStatement) {
    28  					b.Columns("id")
    29  					b.From("table2")
    30  				})
    31  				b.OrderBy("id")
    32  				b.Limit(10)
    33  				b.Offset(2)
    34  			}),
    35  			`
    36  				(select id from table1)
    37  				union all (select id from table2)
    38  				order by id
    39  				limit 10 offset 2
    40  			`,
    41  			nil,
    42  		},
    43  		{
    44  			"union nested",
    45  			pgstmt.Union(func(b pgstmt.UnionStatement) {
    46  				b.Union(func(b pgstmt.UnionStatement) {
    47  					b.Select(func(b pgstmt.SelectStatement) {
    48  						b.Columns("id")
    49  						b.From("table1")
    50  					})
    51  					b.Select(func(b pgstmt.SelectStatement) {
    52  						b.Columns("id")
    53  						b.From("table2")
    54  					})
    55  				})
    56  				b.Select(func(b pgstmt.SelectStatement) {
    57  					b.Columns("id")
    58  					b.From("table3")
    59  				})
    60  				b.AllUnion(func(b pgstmt.UnionStatement) {
    61  					b.Select(func(b pgstmt.SelectStatement) {
    62  						b.Columns("id")
    63  						b.From("table4")
    64  					})
    65  					b.Select(func(b pgstmt.SelectStatement) {
    66  						b.Columns("id")
    67  						b.From("table5")
    68  					})
    69  				})
    70  			}),
    71  			`
    72  				(
    73  					(select id from table1)
    74  					union (select id from table2)
    75  				)
    76  				union (select id from table3)
    77  				union all (
    78  					(select id from table4)
    79  					union
    80  					(select id from table5)
    81  				)
    82  			`,
    83  			nil,
    84  		},
    85  	}
    86  
    87  	for _, tC := range cases {
    88  		t.Run(tC.name, func(t *testing.T) {
    89  			q, args := tC.result.SQL()
    90  			assert.Equal(t, stripSpace(tC.query), q)
    91  			assert.EqualValues(t, tC.args, args)
    92  		})
    93  	}
    94  }