github.com/RevenueMonster/sqlike@v1.0.6/sql/dialect/mysql/select_test.go (about)

     1  package mysql
     2  
     3  import (
     4  	"testing"
     5  	"time"
     6  
     7  	"github.com/RevenueMonster/sqlike/sql"
     8  	"github.com/RevenueMonster/sqlike/sql/expr"
     9  	sqlstmt "github.com/RevenueMonster/sqlike/sql/stmt"
    10  	"github.com/RevenueMonster/sqlike/sqlike/actions"
    11  	"github.com/stretchr/testify/require"
    12  )
    13  
    14  func TestSelect(t *testing.T) {
    15  	var (
    16  		now = time.Now()
    17  		err error
    18  	)
    19  
    20  	invalids := []interface{}{
    21  		expr.And(),
    22  		nil,
    23  		struct{}{},
    24  		expr.Or(),
    25  		make([]interface{}, 0),
    26  		[]interface{}{},
    27  		[]interface{}(nil),
    28  		map[string]string(nil),
    29  	}
    30  
    31  	filters := []interface{}{
    32  		expr.Equal("A", 1),
    33  		expr.Like("B", "abc%"),
    34  		expr.Between("DateTime", now, now.Add(5*time.Minute)),
    35  	}
    36  	filters = append(filters, invalids...)
    37  
    38  	// Complex select statement
    39  	{
    40  		stmt := sqlstmt.AcquireStmt(MySQL{})
    41  		defer sqlstmt.ReleaseStmt(stmt)
    42  		err = New().Select(
    43  			stmt,
    44  			actions.Find().From("A", "Test").
    45  				Where(
    46  					expr.And(filters...),
    47  					expr.Or(filters...),
    48  					expr.Equal("E", uint(888)),
    49  					expr.NotBetween("Z", -10, 12933),
    50  				).(*actions.FindActions), 0,
    51  		)
    52  		require.NoError(t, err)
    53  		require.Equal(t, "SELECT * FROM `A`.`Test` WHERE ((`A` = ? AND `B` LIKE ? AND `DateTime` BETWEEN ? AND ?) AND (`A` = ? OR `B` LIKE ? OR `DateTime` BETWEEN ? AND ?) AND `E` = ? AND `Z` NOT BETWEEN ? AND ?);", stmt.String())
    54  	}
    55  
    56  	{
    57  		stmt := sql.
    58  			Select(
    59  				expr.As(sql.Select().From("Testing"), "t"),
    60  			).
    61  			From("db", "Hell", expr.Raw("FORCE INDEX")).
    62  			Where(
    63  				expr.Equal("A", 1),
    64  				expr.In("Key", sql.Select().
    65  					From("Inner").
    66  					Where(
    67  						expr.Equal(expr.Column("x", "Z"), true),
    68  					).
    69  					OrderBy(
    70  						expr.Desc(expr.Column("x", "G")),
    71  					).
    72  					Having(
    73  						expr.Equal("L", "ok"),
    74  					).
    75  					Limit(10).
    76  					Offset(1),
    77  				),
    78  				expr.NotNull("B"),
    79  				expr.Or(
    80  					expr.In("C", []string{"1", "2", "3"}),
    81  					expr.Equal("A", 100),
    82  					expr.Between("Time", time.Now(), time.Now().Add(time.Minute*10)),
    83  				),
    84  			).OrderBy(
    85  			expr.Desc("A"),
    86  			expr.Asc("C"),
    87  		).Limit(1)
    88  
    89  		x := New()
    90  		stmt2 := sqlstmt.NewStatement(x)
    91  		err := x.parser.BuildStatement(stmt2, stmt)
    92  		require.NoError(t, err)
    93  	}
    94  }