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

     1  package builder
     2  
     3  import (
     4  	"testing"
     5  )
     6  
     7  func TestBuilderCond(t *testing.T) {
     8  	table := T(DB("db"), "t")
     9  
    10  	exprCases{
    11  		Case("CondRules",
    12  			NewCondRules().
    13  				When(true, Col(table, "a").Eq(1)).
    14  				When(true, Col(table, "b").Like(`g`)).
    15  				When(false, Col(table, "b").Like(`g`)).
    16  				ToCond(),
    17  			Expr(
    18  				"(`a` = ?) AND (`b` LIKE ?)",
    19  				1, "%g%",
    20  			),
    21  		),
    22  		Case(
    23  			"Chain Condition",
    24  			Col(table, "a").Eq(1).
    25  				And(Col(table, "b").LeftLike("c")).
    26  				Or(Col(table, "a").Eq(2)).
    27  				Xor(Col(table, "b").RightLike("g")).Expr(),
    28  			Expr(
    29  				"(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)",
    30  				1, "%c", 2, "g%",
    31  			),
    32  		),
    33  		Case(
    34  			"Compose Condition",
    35  			Xor(
    36  				Or(
    37  					And(
    38  						Col(table, "a").Eq(1),
    39  						Col(table, "b").Like("c"),
    40  					),
    41  					Col(table, "a").Eq(2),
    42  				),
    43  				Col(table, "b").Like("g"),
    44  			).Expr(),
    45  			Expr(
    46  				"(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)",
    47  				1, "%c%", 2, "%g%",
    48  			),
    49  		),
    50  		Case(
    51  			"Skip nil",
    52  			Xor(
    53  				Col(table, "a").In(),
    54  				Or(
    55  					Col(table, "a").NotIn(),
    56  					And(
    57  						nil,
    58  						Col(table, "a").Eq(1),
    59  						Col(table, "b").Like("c"),
    60  					),
    61  					Col(table, "a").Eq(2),
    62  				),
    63  				Col(table, "b").Like("g"),
    64  			).Expr(),
    65  			Expr(
    66  				"(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)",
    67  				1, "%c%", 2, "%g%",
    68  			),
    69  		),
    70  		Case(
    71  			"XOR",
    72  			Xor(
    73  				Col(table, "a").In(),
    74  				Or(
    75  					Col(table, "a").NotIn(),
    76  					And(
    77  						nil,
    78  						Col(table, "a").Eq(1),
    79  						Col(table, "b").Like("c"),
    80  					),
    81  					Col(table, "a").Eq(2),
    82  				),
    83  				Col(table, "b").Like("g"),
    84  			).Expr(),
    85  			Expr(
    86  				"(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)",
    87  				1, "%c%", 2, "%g%",
    88  			),
    89  		),
    90  		Case(
    91  			"XOR",
    92  			Xor(
    93  				Col(table, "a").Eq(1),
    94  				Col(table, "b").Like("g"),
    95  			).Expr(),
    96  			Expr(
    97  				"(`a` = ?) XOR (`b` LIKE ?)",
    98  				1, "%g%",
    99  			),
   100  		),
   101  		Case(
   102  			"Like",
   103  			Col(table, "d").Like("e").Expr(),
   104  			Expr(
   105  				"`d` LIKE ?",
   106  				"%e%",
   107  			),
   108  		),
   109  		Case(
   110  			"Not like",
   111  			Col(table, "d").NotLike("e").Expr(),
   112  			Expr(
   113  				"`d` NOT LIKE ?",
   114  				"%e%",
   115  			),
   116  		),
   117  		Case(
   118  			"Equal",
   119  			Col(table, "d").Eq("e").Expr(),
   120  			Expr(
   121  				"`d` = ?",
   122  				"e",
   123  			),
   124  		),
   125  		Case(
   126  			"Not Equal",
   127  			Col(table, "d").Neq("e").Expr(),
   128  			Expr(
   129  				"`d` <> ?",
   130  				"e",
   131  			),
   132  		),
   133  		Case(
   134  			"In",
   135  			Col(table, "d").In("e", "f").Expr(),
   136  			Expr(
   137  				"`d` IN (?,?)",
   138  				"e", "f",
   139  			),
   140  		),
   141  		Case(
   142  			"In With Select",
   143  			Col(table, "d").In(SelectFrom(table).Where(Col(table, "d").Eq(1))).Expr(),
   144  			Expr(
   145  				"`d` IN (SELECT * FROM `db`.`t` WHERE `d` = ?)",
   146  				1,
   147  			),
   148  		),
   149  		Case(
   150  			"NotIn",
   151  			Col(table, "d").NotIn("e", "f").Expr(),
   152  			Expr(
   153  				"`d` NOT IN (?,?)",
   154  				"e", "f",
   155  			),
   156  		),
   157  		Case(
   158  			"Not In With Select",
   159  			Col(table, "d").NotIn(SelectFrom(table).Where(Col(table, "d").Eq(1))).Expr(),
   160  			Expr(
   161  				"`d` NOT IN (SELECT * FROM `db`.`t` WHERE `d` = ?)",
   162  				1,
   163  			),
   164  		),
   165  		Case(
   166  			"Less than",
   167  			Col(table, "d").Lt(3).Expr(),
   168  			Expr(
   169  				"`d` < ?",
   170  				3,
   171  			),
   172  		),
   173  		Case(
   174  			"Less or equal than",
   175  			Col(table, "d").Lte(3).Expr(),
   176  			Expr(
   177  				"`d` <= ?",
   178  				3,
   179  			),
   180  		),
   181  		Case(
   182  			"Greater than",
   183  			Col(table, "d").Gt(3).Expr(),
   184  			Expr(
   185  				"`d` > ?",
   186  				3,
   187  			),
   188  		),
   189  		Case(
   190  			"Greater or equal than",
   191  			Col(table, "d").Gte(3).Expr(),
   192  			Expr(
   193  				"`d` >= ?",
   194  				3,
   195  			),
   196  		),
   197  		Case(
   198  			"Between",
   199  			Col(table, "d").Between(0, 2).Expr(),
   200  			Expr(
   201  				"`d` BETWEEN ? AND ?",
   202  				0, 2,
   203  			),
   204  		),
   205  		Case(
   206  			"Not between",
   207  			Col(table, "d").NotBetween(0, 2).Expr(),
   208  			Expr(
   209  				"`d` NOT BETWEEN ? AND ?",
   210  				0, 2,
   211  			),
   212  		),
   213  		Case(
   214  			"Is null",
   215  			Col(table, "d").IsNull().Expr(),
   216  			Expr(
   217  				"`d` IS NULL",
   218  			),
   219  		),
   220  		Case(
   221  			"Is not null",
   222  			Col(table, "d").IsNotNull().Expr(),
   223  			Expr(
   224  				"`d` IS NOT NULL",
   225  			),
   226  		),
   227  	}.Run(t, "Condition")
   228  }