github.com/ecodeclub/eorm@v0.0.2-0.20231001112437-dae71da914d0/predicate_test.go (about) 1 // Copyright 2021 ecodeclub 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package eorm 16 17 import ( 18 "database/sql" 19 "fmt" 20 "testing" 21 22 "github.com/stretchr/testify/assert" 23 ) 24 25 func TestPredicate_C(t *testing.T) { 26 db := memoryDB() 27 testCases := []CommonTestCase{ 28 { 29 name: "empty", 30 builder: NewSelector[TestModel](db).Select(Columns("Id")).Where(), 31 wantSql: "SELECT `id` FROM `test_model`;", 32 }, 33 { 34 name: "multiples", 35 // 在传入多个 Predicate 的时候,我们认为它们是用 and 连接起来的 36 builder: NewSelector[TestModel](db).Select(Columns("Id")). 37 Where(C("Id").LT(13), C("Id").GT(4)), 38 wantSql: "SELECT `id` FROM `test_model` WHERE (`id`<?) AND (`id`>?);", 39 wantArgs: []interface{}{13, 4}, 40 }, 41 { 42 name: "and", 43 builder: NewSelector[TestModel](db).Select(Columns("Id")). 44 Where(C("Id").LT(13).And(C("Id").GT(4))), 45 wantSql: "SELECT `id` FROM `test_model` WHERE (`id`<?) AND (`id`>?);", 46 wantArgs: []interface{}{13, 4}, 47 }, 48 { 49 name: "or", 50 builder: NewSelector[TestModel](db).Select(Columns("Id")). 51 Where(C("Id").LT(13).Or(C("Id").GT(4))), 52 wantSql: "SELECT `id` FROM `test_model` WHERE (`id`<?) OR (`id`>?);", 53 wantArgs: []interface{}{13, 4}, 54 }, 55 { 56 name: "not", 57 builder: NewSelector[TestModel](db).Select(Columns("Id")). 58 Where(Not(C("Id").LT(13).Or(C("Id").GT(4)))), 59 wantSql: "SELECT `id` FROM `test_model` WHERE NOT ((`id`<?) OR (`id`>?));", 60 wantArgs: []interface{}{13, 4}, 61 }, 62 { 63 name: "and or", 64 builder: NewSelector[TestModel](db).Select(Columns("Id")). 65 Where(C("Id").LT(13).Or(C("Id").GT(4)).And(C("FirstName").GT("tom"))), 66 wantSql: "SELECT `id` FROM `test_model` WHERE ((`id`<?) OR (`id`>?)) AND (`first_name`>?);", 67 wantArgs: []interface{}{13, 4, "tom"}, 68 }, 69 { 70 name: "cross columns", 71 builder: NewSelector[TestModel](db).Select(Columns("Id")). 72 Where(C("Id").LT(13).Or(C("Age").GT(C("Id")))), 73 wantSql: "SELECT `id` FROM `test_model` WHERE (`id`<?) OR (`age`>`id`);", 74 wantArgs: []interface{}{13}, 75 }, 76 { 77 name: "cross columns mathematical", 78 builder: NewSelector[TestModel](db).Select(Columns("Id")). 79 Where(C("Age").GT(C("Id").Add(40))), 80 wantSql: "SELECT `id` FROM `test_model` WHERE `age`>(`id`+?);", 81 wantArgs: []interface{}{40}, 82 }, 83 { 84 name: "cross columns mathematical", 85 builder: NewSelector[TestModel](db).Select(Columns("Id")). 86 Where(C("Age").GT(C("Id").Multi(C("Age").Add(66)))), 87 wantSql: "SELECT `id` FROM `test_model` WHERE `age`>(`id`*(`age`+?));", 88 wantArgs: []interface{}{66}, 89 }, 90 { 91 name: "Avg with EQ", 92 builder: NewSelector[TestModel](db).Select().GroupBy("FirstName").Having(Avg("Age").EQ(18)), 93 wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING AVG(`age`)=?;", 94 wantArgs: []interface{}{18}, 95 }, 96 { 97 name: "Max with NEQ", 98 builder: NewSelector[TestModel](db).Select().GroupBy("FirstName").Having(Max("Age").NEQ(18)), 99 wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING MAX(`age`)!=?;", 100 wantArgs: []interface{}{18}, 101 }, 102 { 103 name: "Min with LT", 104 builder: NewSelector[TestModel](db).Select().GroupBy("FirstName").Having(Min("Age").LT(18)), 105 wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING MIN(`age`)<?;", 106 wantArgs: []interface{}{18}, 107 }, 108 { 109 name: "Sum with LTEQ", 110 builder: NewSelector[TestModel](db).Select().GroupBy("FirstName").Having(Sum("Age").LTEQ(18)), 111 wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING SUM(`age`)<=?;", 112 wantArgs: []interface{}{18}, 113 }, 114 { 115 name: "Count with GT", 116 builder: NewSelector[TestModel](db).Select().GroupBy("FirstName").Having(Count("Age").GT(18)), 117 wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING COUNT(`age`)>?;", 118 wantArgs: []interface{}{18}, 119 }, 120 { 121 name: "Avg with GTEQ", 122 builder: NewSelector[TestModel](db).Select().GroupBy("FirstName").Having(Avg("Age").GTEQ(18)), 123 wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING AVG(`age`)>=?;", 124 wantArgs: []interface{}{18}, 125 }, 126 { 127 name: "multiples aggregate functions", 128 builder: NewSelector[TestModel](db).Select().GroupBy("FirstName").Having(Avg("Age").GTEQ(18).And(Sum("Age").LTEQ(30))), 129 wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING (AVG(`age`)>=?) AND (SUM(`age`)<=?);", 130 wantArgs: []interface{}{18, 30}, 131 }, 132 } 133 134 for _, tc := range testCases { 135 c := tc 136 t.Run(c.name, func(t *testing.T) { 137 query, err := tc.builder.Build() 138 assert.Equal(t, err, c.wantErr) 139 if err != nil { 140 return 141 } 142 assert.Equal(t, c.wantSql, query.SQL) 143 assert.Equal(t, c.wantArgs, query.Args) 144 }) 145 } 146 } 147 148 type TestModel struct { 149 Id int64 `eorm:"auto_increment,primary_key"` 150 FirstName string 151 Age int8 152 LastName *sql.NullString 153 } 154 155 func (TestModel) CreateSQL() string { 156 return ` 157 CREATE TABLE IF NOT EXISTS test_model( 158 id INTEGER PRIMARY KEY, 159 first_name TEXT NOT NULL, 160 age INTEGER, 161 last_name TEXT NOT NULL 162 ) 163 ` 164 } 165 166 type CommonTestCase struct { 167 name string 168 builder QueryBuilder 169 wantArgs []interface{} 170 wantSql string 171 wantErr error 172 } 173 174 func ExampleNot() { 175 db := memoryDB() 176 query, _ := NewSelector[TestModel](db).Select(Columns("Id")).Where(Not(C("Id").EQ(18))).Build() 177 fmt.Println(query.String()) 178 // Output: 179 // SQL: SELECT `id` FROM `test_model` WHERE NOT (`id`=?); 180 // Args: []interface {}{18} 181 } 182 183 func ExamplePredicate_And() { 184 db := memoryDB() 185 query, _ := NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").EQ(18).And(C("Age").GT(100))).Build() 186 fmt.Println(query.String()) 187 // Output: 188 // SQL: SELECT `id` FROM `test_model` WHERE (`id`=?) AND (`age`>?); 189 // Args: []interface {}{18, 100} 190 } 191 192 func ExamplePredicate_Or() { 193 db := memoryDB() 194 query, _ := NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").EQ(18).Or(C("Age").GT(100))).Build() 195 fmt.Println(query.String()) 196 // Output: 197 // SQL: SELECT `id` FROM `test_model` WHERE (`id`=?) OR (`age`>?); 198 // Args: []interface {}{18, 100} 199 }