github.com/yongjacky/phoenix-go-orm-builder@v0.3.5/builder_union_test.go (about)

     1  // Copyright 2018 The Xorm Authors. All rights reserved.
     2  // Use of this source code is governed by a BSD-style
     3  // license that can be found in the LICENSE file.
     4  
     5  package builder
     6  
     7  import (
     8  	"fmt"
     9  	"testing"
    10  
    11  	"github.com/stretchr/testify/assert"
    12  )
    13  
    14  func TestBuilder_Union(t *testing.T) {
    15  	sql, args, err := Select("*").From("t1").Where(Eq{"status": "1"}).
    16  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    17  		Union("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
    18  		Union("", Select("*").From("t2").Where(Eq{"status": "3"})).
    19  		ToSQL()
    20  	assert.NoError(t, err)
    21  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) UNION ALL (SELECT * FROM t2 WHERE status=?) UNION DISTINCT (SELECT * FROM t2 WHERE status=?) UNION  (SELECT * FROM t2 WHERE status=?)", sql)
    22  	assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
    23  
    24  	// sub-query will inherit dialect from the main one
    25  	sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
    26  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
    27  		Union("", Select("*").From("t2").Where(Eq{"status": "3"})).
    28  		ToSQL()
    29  	assert.NoError(t, err)
    30  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) UNION ALL (SELECT * FROM t2 WHERE status=? LIMIT 10) UNION  (SELECT * FROM t2 WHERE status=?)", sql)
    31  	assert.EqualValues(t, []interface{}{"1", "2", "3"}, args)
    32  
    33  	// will raise error
    34  	sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
    35  		Union("all", Oracle().Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
    36  		ToSQL()
    37  	assert.Error(t, err)
    38  	assert.EqualValues(t, ErrInconsistentDialect, err)
    39  
    40  	// will raise error
    41  	sql, args, err = Select("*").From("table1").Where(Eq{"a": "1"}).
    42  		Union("all", Select("*").From("table2").Where(Eq{"a": "2"})).
    43  		Where(Eq{"a": 2}).Limit(5, 10).
    44  		ToSQL()
    45  	assert.Error(t, err)
    46  	assert.EqualValues(t, ErrNotUnexpectedUnionConditions, err)
    47  
    48  	// will raise error
    49  	sql, args, err = Delete(Eq{"a": 1}).From("t1").
    50  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).ToSQL()
    51  	assert.Error(t, err)
    52  	assert.EqualValues(t, ErrUnsupportedUnionMembers, err)
    53  
    54  	// will be overwrote by SELECT op
    55  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
    56  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    57  		Select("*").From("t2").ToSQL()
    58  	assert.NoError(t, err)
    59  	fmt.Println(sql, args)
    60  
    61  	// will be overwrote by DELETE op
    62  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
    63  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    64  		Delete(Eq{"status": "1"}).From("t2").ToSQL()
    65  	assert.NoError(t, err)
    66  	fmt.Println(sql, args)
    67  
    68  	// will be overwrote by INSERT op
    69  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
    70  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    71  		Insert(Eq{"status": "1"}).Into("t2").ToSQL()
    72  	assert.NoError(t, err)
    73  	fmt.Println(sql, args)
    74  }