github.com/artisanhe/tools@v1.0.1-0.20210607022958-19a8fef2eb04/sqlx/builder/stmt_select_test.go (about)

     1  package builder
     2  
     3  import (
     4  	"testing"
     5  )
     6  
     7  func TestStmtSelect(t *testing.T) {
     8  	table := T(DB("db"), "t")
     9  
    10  	if table.Select().Type() != STMT_SELECT {
    11  		panic("select type should be STMT_SELECT")
    12  	}
    13  
    14  	exprCases{
    15  		Case(
    16  			"Select with modifier",
    17  			table.Select().Modifier("DISTINCT").Where(
    18  				Col(table, "F_a").Eq(1),
    19  			).Expr(),
    20  			Expr(
    21  				"SELECT DISTINCT * FROM `db`.`t` WHERE `F_a` = ?",
    22  				1,
    23  			),
    24  		),
    25  		Case(
    26  			"SELECT simple",
    27  			table.Select().Comment("Comment").Where(
    28  				Col(table, "F_a").Eq(1),
    29  			).Expr(),
    30  			Expr(
    31  				"/* Comment */ SELECT * FROM `db`.`t` WHERE `F_a` = ?",
    32  				1,
    33  			),
    34  		),
    35  		Case(
    36  			"SELECT with select expr",
    37  			table.Select().For(Col(table, "F_a")).Where(
    38  				Col(table, "F_a").Eq(1),
    39  			).Expr(),
    40  			Expr(
    41  				"SELECT `F_a` FROM `db`.`t` WHERE `F_a` = ?",
    42  				1,
    43  			),
    44  		),
    45  		Case(
    46  			"Select for update",
    47  			table.Select().
    48  				Where(Col(table, "F_a").Eq(1)).
    49  				ForUpdate().
    50  				Expr(),
    51  			Expr(
    52  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? FOR UPDATE",
    53  				1,
    54  			),
    55  		),
    56  		Case(
    57  			"Select with group by",
    58  			table.Select().
    59  				Where(Col(table, "F_a").Eq(1)).
    60  				GroupBy(Col(table, "F_a")).
    61  				Having(Col(table, "F_a").Eq(1)).
    62  				Expr(),
    63  			Expr(
    64  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? GROUP BY (`F_a`) HAVING `F_a` = ?",
    65  				1, 1,
    66  			),
    67  		),
    68  		Case(
    69  			"Select with group by with rollup",
    70  			table.Select().
    71  				Where(Col(table, "F_a").Eq(1)).
    72  				GroupBy(Col(table, "F_a")).
    73  				WithRollup().
    74  				Having(Col(table, "F_a").Eq(1)).
    75  				Expr(),
    76  			Expr(
    77  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? GROUP BY (`F_a`) WITH ROLLUP HAVING `F_a` = ?",
    78  				1, 1,
    79  			),
    80  		),
    81  		Case(
    82  			"Select with desc group by",
    83  			table.Select().
    84  				Where(Col(table, "F_a").Eq(1)).
    85  				GroupDescBy(Col(table, "F_b")).
    86  				Expr(),
    87  			Expr(
    88  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? GROUP BY (`F_b`) DESC",
    89  				1,
    90  			),
    91  		),
    92  		Case(
    93  			"Select with combined ordered group by ",
    94  			table.Select().
    95  				Where(Col(table, "F_a").Eq(1)).
    96  				GroupAscBy(Col(table, "F_a")).
    97  				GroupDescBy(Col(table, "F_b")).
    98  				Expr(),
    99  			Expr(
   100  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? GROUP BY (`F_a`) ASC, (`F_b`) DESC",
   101  				1,
   102  			),
   103  		),
   104  		Case(
   105  			"Select with having only should skip",
   106  			table.Select().
   107  				Where(Col(table, "F_a").Eq(1)).
   108  				Having(Col(table, "F_a").Eq(1)).
   109  				Expr(),
   110  			Expr(
   111  				"SELECT * FROM `db`.`t` WHERE `F_a` = ?",
   112  				1,
   113  			),
   114  		),
   115  		Case(
   116  			"Select with limit",
   117  			table.Select().
   118  				Where(
   119  					Col(table, "F_a").Eq(1),
   120  				).
   121  				Limit(1).
   122  				Expr(),
   123  			Expr(
   124  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? LIMIT 1",
   125  				1,
   126  			),
   127  		),
   128  		Case(
   129  			"Select with limit with offset",
   130  			table.Select().
   131  				Where(
   132  					Col(table, "F_a").Eq(1),
   133  				).
   134  				Offset(200).
   135  				Limit(1).
   136  				Expr(),
   137  			Expr(
   138  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? LIMIT 1 OFFSET 200",
   139  				1,
   140  			),
   141  		),
   142  		Case(
   143  			"Select with order",
   144  			table.Select().
   145  				Where(Col(table, "F_a").Eq(1)).
   146  				OrderAscBy(Col(table, "F_a")).
   147  				OrderDescBy(Col(table, "F_b")).
   148  				Expr(),
   149  			Expr(
   150  				"SELECT * FROM `db`.`t` WHERE `F_a` = ? ORDER BY (`F_a`) ASC, (`F_b`) DESC",
   151  				1,
   152  			),
   153  		),
   154  	}.Run(t, "Stmt select")
   155  }