yiisoft /
db
| Conditions | 1 |
| Paths | 1 |
| Total Lines | 293 |
| Code Lines | 166 |
| Lines | 0 |
| Ratio | 0 % |
| Changes | 5 | ||
| Bugs | 0 | Features | 1 |
Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.
For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.
Commonly applied refactorings include:
If many parameters/temporary variables are present:
| 1 | <?php |
||
| 15 | public function buildConditions(): array |
||
| 16 | { |
||
| 17 | $mock = new Mock(); |
||
| 18 | |||
| 19 | return [ |
||
| 20 | /* empty values */ |
||
| 21 | [['like', 'name', []], '0=1', []], |
||
| 22 | [['not like', 'name', []], '', []], |
||
| 23 | [['or like', 'name', []], '0=1', []], |
||
| 24 | [['or not like', 'name', []], '', []], |
||
| 25 | |||
| 26 | /* not */ |
||
| 27 | [['not', 'name'], 'NOT (name)', []], |
||
| 28 | [ |
||
| 29 | [ |
||
| 30 | 'not', |
||
| 31 | $mock->query()->select('exists')->from('some_table'), |
||
| 32 | ], |
||
| 33 | 'NOT ((SELECT [[exists]] FROM [[some_table]]))', [], |
||
| 34 | ], |
||
| 35 | |||
| 36 | /* and */ |
||
| 37 | [['and', '', ''], '', []], |
||
| 38 | [['and', '', 'id=2'], 'id=2', []], |
||
| 39 | [['and', 'id=1', 'id=2'], '(id=1) AND (id=2)', []], |
||
| 40 | [['and', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) AND ((id=1) OR (id=2))', []], |
||
| 41 | [['and', 'id=1', new Expression('id=:qp0', [':qp0' => 2])], '(id=1) AND (id=:qp0)', [':qp0' => 2]], |
||
| 42 | [ |
||
| 43 | [ |
||
| 44 | 'and', |
||
| 45 | ['expired' => false], |
||
| 46 | $mock->query()->select('count(*) > 1')->from('queue'), |
||
| 47 | ], |
||
| 48 | '([[expired]]=:qp0) AND ((SELECT count(*) > 1 FROM [[queue]]))', |
||
| 49 | [':qp0' => false], |
||
| 50 | ], |
||
| 51 | |||
| 52 | /* or */ |
||
| 53 | [['or', 'id=1', 'id=2'], '(id=1) OR (id=2)', []], |
||
| 54 | [['or', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) OR ((id=1) OR (id=2))', []], |
||
| 55 | [['or', 'type=1', new Expression('id=:qp0', [':qp0' => 1])], '(type=1) OR (id=:qp0)', [':qp0' => 1]], |
||
| 56 | |||
| 57 | /* between */ |
||
| 58 | [['between', 'id', 1, 10], '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]], |
||
| 59 | [['not between', 'id', 1, 10], '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]], |
||
| 60 | [ |
||
| 61 | ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')], |
||
| 62 | '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()', |
||
| 63 | [], |
||
| 64 | ], |
||
| 65 | [ |
||
| 66 | ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123], |
||
| 67 | '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0', |
||
| 68 | [':qp0' => 123], |
||
| 69 | ], |
||
| 70 | [ |
||
| 71 | ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')], |
||
| 72 | '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()', |
||
| 73 | [], |
||
| 74 | ], |
||
| 75 | [ |
||
| 76 | ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123], |
||
| 77 | '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0', |
||
| 78 | [':qp0' => 123], |
||
| 79 | ], |
||
| 80 | [ |
||
| 81 | new BetweenColumnsCondition('2018-02-11', 'BETWEEN', 'create_time', 'update_time'), |
||
| 82 | ':qp0 BETWEEN [[create_time]] AND [[update_time]]', |
||
| 83 | [':qp0' => '2018-02-11'], |
||
| 84 | ], |
||
| 85 | [ |
||
| 86 | new BetweenColumnsCondition('2018-02-11', 'NOT BETWEEN', 'NOW()', 'update_time'), |
||
| 87 | ':qp0 NOT BETWEEN NOW() AND [[update_time]]', |
||
| 88 | [':qp0' => '2018-02-11'], |
||
| 89 | ], |
||
| 90 | [ |
||
| 91 | new BetweenColumnsCondition(new Expression('NOW()'), 'BETWEEN', 'create_time', 'update_time'), |
||
| 92 | 'NOW() BETWEEN [[create_time]] AND [[update_time]]', |
||
| 93 | [], |
||
| 94 | ], |
||
| 95 | [ |
||
| 96 | new BetweenColumnsCondition(new Expression('NOW()'), 'NOT BETWEEN', 'create_time', 'update_time'), |
||
| 97 | 'NOW() NOT BETWEEN [[create_time]] AND [[update_time]]', |
||
| 98 | [], |
||
| 99 | ], |
||
| 100 | [ |
||
| 101 | new BetweenColumnsCondition( |
||
| 102 | new Expression('NOW()'), |
||
| 103 | 'NOT BETWEEN', |
||
| 104 | $mock->query()->select('min_date')->from('some_table'), |
||
| 105 | 'max_date' |
||
| 106 | ), |
||
| 107 | 'NOW() NOT BETWEEN (SELECT [[min_date]] FROM [[some_table]]) AND [[max_date]]', |
||
| 108 | [], |
||
| 109 | ], |
||
| 110 | [ |
||
| 111 | new BetweenColumnsCondition( |
||
| 112 | new Expression('NOW()'), |
||
| 113 | 'NOT BETWEEN', |
||
| 114 | new Expression('min_date'), |
||
| 115 | $mock->query()->select('max_date')->from('some_table'), |
||
| 116 | ), |
||
| 117 | 'NOW() NOT BETWEEN min_date AND (SELECT [[max_date]] FROM [[some_table]])', |
||
| 118 | [], |
||
| 119 | ], |
||
| 120 | |||
| 121 | /* in */ |
||
| 122 | [ |
||
| 123 | ['in', 'id', [1, 2, $mock->query()->select('three')->from('digits')]], |
||
| 124 | '[[id]] IN (:qp0, :qp1, (SELECT [[three]] FROM [[digits]]))', |
||
| 125 | [':qp0' => 1, ':qp1' => 2], |
||
| 126 | ], |
||
| 127 | [ |
||
| 128 | ['not in', 'id', [1, 2, 3]], |
||
| 129 | '[[id]] NOT IN (:qp0, :qp1, :qp2)', |
||
| 130 | [':qp0' => 1, ':qp1' => 2, ':qp2' => 3], |
||
| 131 | ], |
||
| 132 | [ |
||
| 133 | ['in', 'id', $mock->query()->select('id')->from('users')->where(['active' => 1])], |
||
| 134 | '[[id]] IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 135 | [':qp0' => 1], |
||
| 136 | ], |
||
| 137 | [ |
||
| 138 | ['not in', 'id', $mock->query()->select('id')->from('users')->where(['active' => 1])], |
||
| 139 | '[[id]] NOT IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 140 | [':qp0' => 1], |
||
| 141 | ], |
||
| 142 | [['in', 'id', 1], '[[id]]=:qp0', [':qp0' => 1]], |
||
| 143 | [['in', 'id', [1]], '[[id]]=:qp0', [':qp0' => 1]], |
||
| 144 | [['in', 'id', new TraversableObject([1])], '[[id]]=:qp0', [':qp0' => 1]], |
||
| 145 | 'composite in' => [ |
||
| 146 | ['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]], |
||
| 147 | '([[id]], [[name]]) IN ((:qp0, :qp1))', |
||
| 148 | [':qp0' => 1, ':qp1' => 'oy'], |
||
| 149 | ], |
||
| 150 | 'composite in (just one column)' => [ |
||
| 151 | ['in', ['id'], [['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]], |
||
| 152 | '[[id]] IN (:qp0, :qp1)', |
||
| 153 | [':qp0' => 1, ':qp1' => 2], |
||
| 154 | ], |
||
| 155 | 'composite in using array objects (just one column)' => [ |
||
| 156 | [ |
||
| 157 | 'in', |
||
| 158 | new TraversableObject(['id']), |
||
| 159 | new TraversableObject([['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]), |
||
| 160 | ], |
||
| 161 | '[[id]] IN (:qp0, :qp1)', |
||
| 162 | [':qp0' => 1, ':qp1' => 2], |
||
| 163 | ], |
||
| 164 | |||
| 165 | /* in using array objects. */ |
||
| 166 | [['id' => new TraversableObject([1, 2])], '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 167 | [ |
||
| 168 | ['in', 'id', new TraversableObject([1, 2, 3])], |
||
| 169 | '[[id]] IN (:qp0, :qp1, :qp2)', |
||
| 170 | [':qp0' => 1, ':qp1' => 2, ':qp2' => 3], |
||
| 171 | ], |
||
| 172 | |||
| 173 | /* in using array objects containing null value */ |
||
| 174 | [['in', 'id', new TraversableObject([1, null])], '[[id]]=:qp0 OR [[id]] IS NULL', [':qp0' => 1]], |
||
| 175 | [ |
||
| 176 | ['in', 'id', new TraversableObject([1, 2, null])], |
||
| 177 | '[[id]] IN (:qp0, :qp1) OR [[id]] IS NULL', [':qp0' => 1, ':qp1' => 2], |
||
| 178 | ], |
||
| 179 | |||
| 180 | /* not in using array object containing null value */ |
||
| 181 | [ |
||
| 182 | ['not in', 'id', new TraversableObject([1, null])], |
||
| 183 | '[[id]]<>:qp0 AND [[id]] IS NOT NULL', [':qp0' => 1], |
||
| 184 | ], |
||
| 185 | [ |
||
| 186 | ['not in', 'id', new TraversableObject([1, 2, null])], |
||
| 187 | '[[id]] NOT IN (:qp0, :qp1) AND [[id]] IS NOT NULL', |
||
| 188 | [':qp0' => 1, ':qp1' => 2], |
||
| 189 | ], |
||
| 190 | |||
| 191 | /* in using array object containing only null value */ |
||
| 192 | [['in', 'id', new TraversableObject([null])], '[[id]] IS NULL', []], |
||
| 193 | [['not in', 'id', new TraversableObject([null])], '[[id]] IS NOT NULL', []], |
||
| 194 | 'composite in using array objects' => [ |
||
| 195 | [ |
||
| 196 | 'in', |
||
| 197 | new TraversableObject(['id', 'name']), |
||
| 198 | new TraversableObject([['id' => 1, 'name' => 'oy'], ['id' => 2, 'name' => 'yo']]), |
||
| 199 | ], |
||
| 200 | '([[id]], [[name]]) IN ((:qp0, :qp1), (:qp2, :qp3))', |
||
| 201 | [':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'], |
||
| 202 | ], |
||
| 203 | |||
| 204 | /* in object conditions */ |
||
| 205 | [new InCondition('id', 'in', 1), '[[id]]=:qp0', [':qp0' => 1]], |
||
| 206 | [new InCondition('id', 'in', [1]), '[[id]]=:qp0', [':qp0' => 1]], |
||
| 207 | [new InCondition('id', 'not in', 1), '[[id]]<>:qp0', [':qp0' => 1]], |
||
| 208 | [new InCondition('id', 'not in', [1]), '[[id]]<>:qp0', [':qp0' => 1]], |
||
| 209 | [new InCondition('id', 'in', [1, 2]), '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 210 | [new InCondition('id', 'not in', [1, 2]), '[[id]] NOT IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 211 | [new InCondition([], 'in', 1), '0=1', []], |
||
| 212 | [new InCondition([], 'in', [1]), '0=1', []], |
||
| 213 | [new InCondition(['id', 'name'], 'in', []), '0=1', []], |
||
| 214 | [ |
||
| 215 | new InCondition(['id'], 'in', $mock->query()->select('id')->from('users')->where(['active' => 1])), |
||
| 216 | '([[id]]) IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 217 | [':qp0' => 1], |
||
| 218 | ], |
||
| 219 | [ |
||
| 220 | new InCondition(['id', 'name'], 'in', [['id' => 1]]), |
||
| 221 | '([[id]], [[name]]) IN ((:qp0, NULL))', |
||
| 222 | [':qp0' => 1], |
||
| 223 | ], |
||
| 224 | [ |
||
| 225 | new InCondition(['id', 'name'], 'in', [['name' => 'oy']]), |
||
| 226 | '([[id]], [[name]]) IN ((NULL, :qp0))', |
||
| 227 | [':qp0' => 'oy'], |
||
| 228 | ], |
||
| 229 | [ |
||
| 230 | new InCondition(['id', 'name'], 'in', [['id' => 1, 'name' => 'oy']]), |
||
| 231 | '([[id]], [[name]]) IN ((:qp0, :qp1))', |
||
| 232 | [':qp0' => 1, ':qp1' => 'oy'], |
||
| 233 | ], |
||
| 234 | |||
| 235 | /* exists */ |
||
| 236 | [ |
||
| 237 | [ |
||
| 238 | 'exists', |
||
| 239 | $mock->query()->select('id')->from('users')->where(['active' => 1]), |
||
| 240 | ], |
||
| 241 | 'EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 242 | [':qp0' => 1], |
||
| 243 | ], |
||
| 244 | [ |
||
| 245 | ['not exists', $mock->query()->select('id')->from('users')->where(['active' => 1])], |
||
| 246 | 'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', [':qp0' => 1], |
||
| 247 | ], |
||
| 248 | |||
| 249 | /* simple conditions */ |
||
| 250 | [['=', 'a', 'b'], '[[a]] = :qp0', [':qp0' => 'b']], |
||
| 251 | [['>', 'a', 1], '[[a]] > :qp0', [':qp0' => 1]], |
||
| 252 | [['>=', 'a', 'b'], '[[a]] >= :qp0', [':qp0' => 'b']], |
||
| 253 | [['<', 'a', 2], '[[a]] < :qp0', [':qp0' => 2]], |
||
| 254 | [['<=', 'a', 'b'], '[[a]] <= :qp0', [':qp0' => 'b']], |
||
| 255 | [['<>', 'a', 3], '[[a]] <> :qp0', [':qp0' => 3]], |
||
| 256 | [['!=', 'a', 'b'], '[[a]] != :qp0', [':qp0' => 'b']], |
||
| 257 | [ |
||
| 258 | ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)')], |
||
| 259 | '[[date]] >= DATE_SUB(NOW(), INTERVAL 1 MONTH)', |
||
| 260 | [], |
||
| 261 | ], |
||
| 262 | [ |
||
| 263 | ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL :month MONTH)', [':month' => 2])], |
||
| 264 | '[[date]] >= DATE_SUB(NOW(), INTERVAL :month MONTH)', |
||
| 265 | [':month' => 2], |
||
| 266 | ], |
||
| 267 | [ |
||
| 268 | ['=', 'date', $mock->query()->select('max(date)')->from('test')->where(['id' => 5])], |
||
| 269 | '[[date]] = (SELECT max(date) FROM [[test]] WHERE [[id]]=:qp0)', |
||
| 270 | [':qp0' => 5], |
||
| 271 | ], |
||
| 272 | |||
| 273 | /* operand1 is Expression */ |
||
| 274 | [ |
||
| 275 | ['=', new Expression('date'), '2019-08-01'], |
||
| 276 | 'date = :qp0', |
||
| 277 | [':qp0' => '2019-08-01'], |
||
| 278 | ], |
||
| 279 | [ |
||
| 280 | ['=', $mock->query()->select('COUNT(*)')->from('test')->where(['id' => 6]), 0], |
||
| 281 | '(SELECT COUNT(*) FROM [[test]] WHERE [[id]]=:qp0) = :qp1', |
||
| 282 | [':qp0' => 6, ':qp1' => 0], |
||
| 283 | ], |
||
| 284 | |||
| 285 | /* hash condition */ |
||
| 286 | [['a' => 1, 'b' => 2], '([[a]]=:qp0) AND ([[b]]=:qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 287 | [ |
||
| 288 | ['a' => new Expression('CONCAT(col1, col2)'), 'b' => 2], |
||
| 289 | '([[a]]=CONCAT(col1, col2)) AND ([[b]]=:qp0)', |
||
| 290 | [':qp0' => 2], |
||
| 291 | ], |
||
| 292 | [['a' => null], '[[a]] IS NULL', []], |
||
| 293 | |||
| 294 | /* direct conditions */ |
||
| 295 | ['a = CONCAT(col1, col2)', 'a = CONCAT(col1, col2)', []], |
||
| 296 | [ |
||
| 297 | new Expression('a = CONCAT(col1, :param1)', ['param1' => 'value1']), |
||
| 298 | 'a = CONCAT(col1, :param1)', |
||
| 299 | ['param1' => 'value1'], |
||
| 300 | ], |
||
| 301 | |||
| 302 | /* Expression with params as operand of 'not' */ |
||
| 303 | [ |
||
| 304 | ['not', new Expression('any_expression(:a)', [':a' => 1])], |
||
| 305 | 'NOT (any_expression(:a))', [':a' => 1], |
||
| 306 | ], |
||
| 307 | [new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]], |
||
| 308 | ]; |
||
| 311 |
