github.com/yongjacky/phoenix-go-orm-builder@v0.3.5/builder_select_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_Select(t *testing.T) {
    15  	sql, args, err := Select("c, d").From("table1").ToSQL()
    16  	assert.NoError(t, err)
    17  	assert.EqualValues(t, "SELECT c, d FROM table1", sql)
    18  	assert.EqualValues(t, []interface{}(nil), args)
    19  
    20  	sql, args, err = Select("c, d").From("table1").Where(Eq{"a": 1}).ToSQL()
    21  	assert.NoError(t, err)
    22  	assert.EqualValues(t, "SELECT c, d FROM table1 WHERE a=?", sql)
    23  	assert.EqualValues(t, []interface{}{1}, args)
    24  
    25  	_, _, err = Select("c, d").ToSQL()
    26  	assert.Error(t, err)
    27  	assert.EqualValues(t, ErrNoTableName, err)
    28  }
    29  
    30  func TestBuilderSelectGroupBy(t *testing.T) {
    31  	sql, args, err := Select("c").From("table1").GroupBy("c").Having("count(c)=1").ToSQL()
    32  	assert.NoError(t, err)
    33  	assert.EqualValues(t, "SELECT c FROM table1 GROUP BY c HAVING count(c)=1", sql)
    34  	assert.EqualValues(t, 0, len(args))
    35  	fmt.Println(sql, args)
    36  }
    37  
    38  func TestBuilderSelectOrderBy(t *testing.T) {
    39  	sql, args, err := Select("c").From("table1").OrderBy("c DESC").ToSQL()
    40  	assert.NoError(t, err)
    41  	assert.EqualValues(t, "SELECT c FROM table1 ORDER BY c DESC", sql)
    42  	assert.EqualValues(t, 0, len(args))
    43  	fmt.Println(sql, args)
    44  }
    45  
    46  func TestBuilder_From(t *testing.T) {
    47  	// simple one
    48  	sql, args, err := Select("c").From("table1").ToSQL()
    49  	assert.NoError(t, err)
    50  	assert.EqualValues(t, "SELECT c FROM table1", sql)
    51  	assert.EqualValues(t, 0, len(args))
    52  
    53  	// from sub with alias
    54  	sql, args, err = Select("sub.id").From(Select("id").From("table1").Where(Eq{"a": 1}),
    55  		"sub").Where(Eq{"b": 1}).ToSQL()
    56  	assert.NoError(t, err)
    57  	assert.EqualValues(t, "SELECT sub.id FROM (SELECT id FROM table1 WHERE a=?) sub WHERE b=?", sql)
    58  	assert.EqualValues(t, []interface{}{1, 1}, args)
    59  
    60  	// from sub without alias and with conditions
    61  	sql, args, err = Select("sub.id").From(Select("id").From("table1").Where(Eq{"a": 1})).Where(Eq{"b": 1}).ToSQL()
    62  	assert.Error(t, err)
    63  	assert.EqualValues(t, ErrUnnamedDerivedTable, err)
    64  
    65  	// from sub without alias and conditions
    66  	sql, args, err = Select("sub.id").From(Select("id").From("table1").Where(Eq{"a": 1})).ToSQL()
    67  	assert.NoError(t, err)
    68  	assert.EqualValues(t, "SELECT sub.id FROM (SELECT id FROM table1 WHERE a=?)", sql)
    69  	assert.EqualValues(t, []interface{}{1}, args)
    70  
    71  	// from union with alias
    72  	sql, args, err = Select("sub.id").From(
    73  		Select("id").From("table1").Where(Eq{"a": 1}).Union(
    74  			"all", Select("id").From("table1").Where(Eq{"a": 2})), "sub").Where(Eq{"b": 1}).ToSQL()
    75  	assert.NoError(t, err)
    76  	assert.EqualValues(t, "SELECT sub.id FROM ((SELECT id FROM table1 WHERE a=?) UNION ALL (SELECT id FROM table1 WHERE a=?)) sub WHERE b=?", sql)
    77  	assert.EqualValues(t, []interface{}{1, 2, 1}, args)
    78  
    79  	// from union without alias
    80  	_, _, err = Select("sub.id").From(
    81  		Select("id").From("table1").Where(Eq{"a": 1}).Union(
    82  			"all", Select("id").From("table1").Where(Eq{"a": 2}))).Where(Eq{"b": 1}).ToSQL()
    83  	assert.Error(t, err)
    84  	assert.EqualValues(t, ErrUnnamedDerivedTable, err)
    85  
    86  	// will raise error
    87  	_, _, err = Select("c").From(Insert(Eq{"a": 1}).From("table1"), "table1").ToSQL()
    88  	assert.Error(t, err)
    89  	assert.EqualValues(t, ErrUnexpectedSubQuery, err)
    90  
    91  	// will raise error
    92  	_, _, err = Select("c").From(Delete(Eq{"a": 1}).From("table1"), "table1").ToSQL()
    93  	assert.Error(t, err)
    94  	assert.EqualValues(t, ErrUnexpectedSubQuery, err)
    95  
    96  	// from a sub-query in different dialect
    97  	_, _, err = MySQL().Select("sub.id").From(
    98  		Oracle().Select("id").From("table1").Where(Eq{"a": 1}), "sub").Where(Eq{"b": 1}).ToSQL()
    99  	assert.Error(t, err)
   100  	assert.EqualValues(t, ErrInconsistentDialect, err)
   101  
   102  	// from a sub-query (dialect set up)
   103  	sql, args, err = MySQL().Select("sub.id").From(
   104  		MySQL().Select("id").From("table1").Where(Eq{"a": 1}), "sub").Where(Eq{"b": 1}).ToSQL()
   105  	assert.NoError(t, err)
   106  	assert.EqualValues(t, "SELECT sub.id FROM (SELECT id FROM table1 WHERE a=?) sub WHERE b=?", sql)
   107  	assert.EqualValues(t, []interface{}{1, 1}, args)
   108  
   109  	// from a sub-query (dialect not set up)
   110  	sql, args, err = MySQL().Select("sub.id").From(
   111  		Select("id").From("table1").Where(Eq{"a": 1}), "sub").Where(Eq{"b": 1}).ToSQL()
   112  	assert.NoError(t, err)
   113  	assert.EqualValues(t, "SELECT sub.id FROM (SELECT id FROM table1 WHERE a=?) sub WHERE b=?", sql)
   114  	assert.EqualValues(t, []interface{}{1, 1}, args)
   115  }