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 }