Skip to content

Commit 0326d09

Browse files
vjikvjik
authored
Optimize SQL generation for Not condition (#1031)
1 parent eec09d5 commit 0326d09

3 files changed

Lines changed: 142 additions & 28 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -131,6 +131,7 @@
131131
- New #1024: Add `ColumnName` and `Value` expressions (@vjik)
132132
- Chg #1025: Move expression builders to `Yiisoft\Db\Expression\Builder` namespace (@vjik)
133133
- Chg #1026: Remove `precision()`, `getPrecision()` and `getPhpType()` methods from `ColumnInterface` (@vjik)
134+
- Enh #1031: Optimize SQL generation for `Not` condition (@vjik)
134135

135136
## 1.3.0 March 21, 2024
136137

‎src/QueryBuilder/Condition/Builder/NotBuilder.php‎

Lines changed: 70 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10,9 +10,26 @@
1010
use Yiisoft\Db\Exception\NotSupportedException;
1111
use Yiisoft\Db\Expression\Builder\ExpressionBuilderInterface;
1212
use Yiisoft\Db\Expression\ExpressionInterface;
13+
use Yiisoft\Db\QueryBuilder\Condition\Between;
14+
use Yiisoft\Db\QueryBuilder\Condition\ConditionInterface;
15+
use Yiisoft\Db\QueryBuilder\Condition\Equals;
16+
use Yiisoft\Db\QueryBuilder\Condition\Exists;
17+
use Yiisoft\Db\QueryBuilder\Condition\GreaterThan;
18+
use Yiisoft\Db\QueryBuilder\Condition\GreaterThanOrEqual;
19+
use Yiisoft\Db\QueryBuilder\Condition\In;
20+
use Yiisoft\Db\QueryBuilder\Condition\LessThan;
21+
use Yiisoft\Db\QueryBuilder\Condition\LessThanOrEqual;
22+
use Yiisoft\Db\QueryBuilder\Condition\Like;
1323
use Yiisoft\Db\QueryBuilder\Condition\Not;
24+
use Yiisoft\Db\QueryBuilder\Condition\NotBetween;
25+
use Yiisoft\Db\QueryBuilder\Condition\NotEquals;
26+
use Yiisoft\Db\QueryBuilder\Condition\NotExists;
27+
use Yiisoft\Db\QueryBuilder\Condition\NotIn;
28+
use Yiisoft\Db\QueryBuilder\Condition\NotLike;
1429
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
1530

31+
use function is_array;
32+
1633
/**
1734
* Build an object of {@see Not} into SQL expressions.
1835
*
@@ -36,19 +53,66 @@ public function __construct(private readonly QueryBuilderInterface $queryBuilder
3653
*/
3754
public function build(ExpressionInterface $expression, array &$params = []): string
3855
{
39-
$operand = $expression->condition;
56+
$condition = is_array($expression->condition)
57+
? $this->queryBuilder->createConditionFromArray($expression->condition)
58+
: $expression->condition;
4059

41-
if ($operand === '') {
60+
if ($condition === null || $condition === '') {
4261
return '';
4362
}
4463

45-
$expressionValue = $this->queryBuilder->buildCondition($operand, $params);
64+
if ($condition instanceof ConditionInterface) {
65+
$negatedCondition = $this->createNegatedCondition($condition);
66+
if ($negatedCondition !== null) {
67+
return $this->queryBuilder->buildCondition($negatedCondition, $params);
68+
}
69+
}
4670

47-
return "{$this->getNegationOperator()} ($expressionValue)";
71+
$sql = $this->queryBuilder->buildCondition($condition, $params);
72+
return $sql === '' ? '' : "NOT ($sql)";
4873
}
4974

50-
protected function getNegationOperator(): string
75+
protected function createNegatedCondition(ConditionInterface $condition): array|string|ExpressionInterface|null
5176
{
52-
return 'NOT';
77+
return match ($condition::class) {
78+
LessThan::class => new GreaterThanOrEqual($condition->column, $condition->value),
79+
LessThanOrEqual::class => new GreaterThan($condition->column, $condition->value),
80+
GreaterThan::class => new LessThanOrEqual($condition->column, $condition->value),
81+
GreaterThanOrEqual::class => new LessThan($condition->column, $condition->value),
82+
In::class => new NotIn($condition->column, $condition->values),
83+
NotIn::class => new In($condition->column, $condition->values),
84+
Between::class => new NotBetween(
85+
$condition->column,
86+
$condition->intervalStart,
87+
$condition->intervalEnd,
88+
),
89+
NotBetween::class => new Between(
90+
$condition->column,
91+
$condition->intervalStart,
92+
$condition->intervalEnd,
93+
),
94+
Equals::class => new NotEquals($condition->column, $condition->value),
95+
NotEquals::class => new Equals($condition->column, $condition->value),
96+
Exists::class => new NotExists($condition->query),
97+
NotExists::class => new Exists($condition->query),
98+
Like::class => new NotLike(
99+
$condition->column,
100+
$condition->value,
101+
$condition->caseSensitive,
102+
$condition->escape,
103+
$condition->mode,
104+
$condition->conjunction,
105+
),
106+
NotLike::class => new Like(
107+
$condition->column,
108+
$condition->value,
109+
$condition->caseSensitive,
110+
$condition->escape,
111+
$condition->mode,
112+
$condition->conjunction,
113+
),
114+
Not::class => $condition->condition,
115+
default => null,
116+
};
53117
}
54118
}

‎tests/Provider/QueryBuilderProvider.php‎

Lines changed: 71 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -19,12 +19,14 @@
1919
use Yiisoft\Db\Expression\Value;
2020
use Yiisoft\Db\Query\Query;
2121
use Yiisoft\Db\QueryBuilder\Condition\Between;
22+
use Yiisoft\Db\QueryBuilder\Condition\Exists;
2223
use Yiisoft\Db\QueryBuilder\Condition\In;
2324
use Yiisoft\Db\QueryBuilder\Condition\Like;
2425
use Yiisoft\Db\QueryBuilder\Condition\LikeConjunction;
2526
use Yiisoft\Db\QueryBuilder\Condition\LikeMode;
26-
use Yiisoft\Db\QueryBuilder\Condition\NotIn;
27+
use Yiisoft\Db\QueryBuilder\Condition\Not;
2728
use Yiisoft\Db\QueryBuilder\Condition\NotBetween;
29+
use Yiisoft\Db\QueryBuilder\Condition\NotIn;
2830
use Yiisoft\Db\QueryBuilder\Condition\NotLike;
2931
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
3032
use Yiisoft\Db\Schema\Column\ColumnBuilder;
@@ -287,8 +289,46 @@ public static function buildCondition(): array
287289
'not',
288290
(new Query(static::getDb()))->select('exists')->from('some_table'),
289291
],
290-
'NOT ((SELECT [[exists]] FROM [[some_table]]))', [],
292+
'NOT ((SELECT [[exists]] FROM [[some_table]]))',
293+
[],
294+
],
295+
[new Not(''), '', []],
296+
[new Not(new Between('id', 1, 10)), '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
297+
[new Not(new NotBetween('id', 1, 10)), '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
298+
[new Not(new In('id', [1, 2, 3])), '[[id]] NOT IN (1, 2, 3)', []],
299+
[new Not(new NotIn('id', [1, 2, 3])), '[[id]] IN (1, 2, 3)', []],
300+
'not: like' => [
301+
new Not(new Like('name', 'test')),
302+
'[[name]] NOT LIKE :qp0' . static::$likeEscapeCharSql,
303+
[':qp0' => new Param('%test%', DataType::STRING)],
304+
],
305+
'not: not like' => [
306+
new Not(new NotLike('name', 'test')),
307+
'[[name]] LIKE :qp0' . static::$likeEscapeCharSql,
308+
[':qp0' => new Param('%test%', DataType::STRING)],
309+
],
310+
'not: not empty string' => [new Not(new Not('')), '', []],
311+
'not: not null' => [new Not(new Not(null)), '', []],
312+
[new Not(new Not('id=1')), 'id=1', []],
313+
[new Not(['=', 'status', 'active']), '[[status]] <> :qp0', [':qp0' => new Param('active', DataType::STRING)]],
314+
[new Not(['!=', 'status', 'inactive']), '[[status]] = :qp0', [':qp0' => new Param('inactive', DataType::STRING)]],
315+
[new Not(['<', 'score', 50]), '[[score]] >= 50', []],
316+
[new Not(['<=', 'score', 50]), '[[score]] > 50', []],
317+
[new Not(['>', 'score', 50]), '[[score]] <= 50', []],
318+
[new Not(['>=', 'score', 50]), '[[score]] < 50', []],
319+
[
320+
new Not(['exists', (new Query(static::getDb()))->select('id')->from('users')]),
321+
'NOT EXISTS (SELECT [[id]] FROM [[users]])',
322+
[],
291323
],
324+
[
325+
new Not(['not exists', (new Query(static::getDb()))->select('id')->from('users')]),
326+
'EXISTS (SELECT [[id]] FROM [[users]])',
327+
[],
328+
],
329+
[new Not('custom_condition'), 'NOT (custom_condition)', []],
330+
[new Not(['and', 'id=1', 'name="test"']), 'NOT ((id=1) AND (name="test"))', []],
331+
[new Not(new Expression('COMPLEX_FUNCTION()')), 'NOT (COMPLEX_FUNCTION())', []],
292332

293333
/* and */
294334
[['and', '', ''], '', []],
@@ -530,7 +570,8 @@ public static function buildCondition(): array
530570
'not exists',
531571
(new Query(static::getDb()))->select('id')->from('users')->where(['active' => 1]),
532572
],
533-
'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]] = 1)', [],
573+
'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]] = 1)',
574+
[],
534575
],
535576

536577
/* simple conditions */
@@ -594,7 +635,8 @@ public static function buildCondition(): array
594635
/* Expression with params as operand of 'not' */
595636
[
596637
['not', new Expression('any_expression(:a)', [':a' => 1])],
597-
'NOT (any_expression(:a))', [':a' => 1],
638+
'NOT (any_expression(:a))',
639+
[':a' => 1],
598640
],
599641
[new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]],
600642

@@ -606,13 +648,16 @@ public static function buildCondition(): array
606648
[':qp0' => '%b%'],
607649
],
608650
'like-custom-3' => [
609-
['like', new Expression('CONCAT(col1, col2)'), 'b'], 'CONCAT(col1, col2) LIKE :qp0', [':qp0' => new Param('%b%', DataType::STRING)],
651+
['like', new Expression('CONCAT(col1, col2)'), 'b'],
652+
'CONCAT(col1, col2) LIKE :qp0',
653+
[':qp0' => new Param('%b%', DataType::STRING)],
610654
],
611655

612656
/* json conditions */
613657
'search by property in JSON column' => [
614658
['=', new Expression("(json_col->>'$.someKey')"), 42],
615-
"(json_col->>'$.someKey') = 42", [],
659+
"(json_col->>'$.someKey') = 42",
660+
[],
616661
],
617662
];
618663

@@ -850,15 +895,17 @@ public static function buildLikeCondition(): array
850895
],
851896
[
852897
new Like('name', [new Expression('CONCAT("test", name, "%")'), '\ab_c'], conjunction: LikeConjunction::Or),
853-
'[[name]] LIKE CONCAT("test", name, "%") OR [[name]] LIKE :qp0', [':qp0' => new Param('%\\\ab\_c%', DataType::STRING)],
898+
'[[name]] LIKE CONCAT("test", name, "%") OR [[name]] LIKE :qp0',
899+
[':qp0' => new Param('%\\\ab\_c%', DataType::STRING)],
854900
],
855901
[
856902
new NotLike(
857903
'name',
858904
[new Expression('CONCAT("test", name, "%")'), '\ab_c'],
859905
conjunction: LikeConjunction::Or,
860906
),
861-
'[[name]] NOT LIKE CONCAT("test", name, "%") OR [[name]] NOT LIKE :qp0', [':qp0' => new Param('%\\\ab\_c%', DataType::STRING)],
907+
'[[name]] NOT LIKE CONCAT("test", name, "%") OR [[name]] NOT LIKE :qp0',
908+
[':qp0' => new Param('%\\\ab\_c%', DataType::STRING)],
862909
],
863910

864911
/* like with expression as columnName */
@@ -922,13 +969,13 @@ public static function createIndex(): array
922969
<<<SQL
923970
CREATE INDEX [[$name1]] ON {{{$tableName}}} ([[C_index_1]])
924971
SQL,
925-
static fn (QueryBuilderInterface $qb) => $qb->createIndex($tableName, $name1, 'C_index_1'),
972+
static fn(QueryBuilderInterface $qb) => $qb->createIndex($tableName, $name1, 'C_index_1'),
926973
],
927974
'create (2 columns)' => [
928975
<<<SQL
929976
CREATE INDEX [[$name2]] ON {{{$tableName}}} ([[C_index_2_1]], [[C_index_2_2]])
930977
SQL,
931-
static fn (QueryBuilderInterface $qb) => $qb->createIndex(
978+
static fn(QueryBuilderInterface $qb) => $qb->createIndex(
932979
$tableName,
933980
$name2,
934981
'C_index_2_1,
@@ -939,7 +986,7 @@ public static function createIndex(): array
939986
<<<SQL
940987
CREATE UNIQUE INDEX [[$name1]] ON {{{$tableName}}} ([[C_index_1]])
941988
SQL,
942-
static fn (QueryBuilderInterface $qb) => $qb->createIndex(
989+
static fn(QueryBuilderInterface $qb) => $qb->createIndex(
943990
$tableName,
944991
$name1,
945992
'C_index_1',
@@ -950,7 +997,7 @@ public static function createIndex(): array
950997
<<<SQL
951998
CREATE UNIQUE INDEX [[$name2]] ON {{{$tableName}}} ([[C_index_2_1]], [[C_index_2_2]])
952999
SQL,
953-
static fn (QueryBuilderInterface $qb) => $qb->createIndex(
1000+
static fn(QueryBuilderInterface $qb) => $qb->createIndex(
9541001
$tableName,
9551002
$name2,
9561003
'C_index_2_1, C_index_2_2',
@@ -1298,13 +1345,15 @@ public static function update(): array
12981345
],
12991346
'Expressions with nested Expressions' => [
13001347
'{{table}}',
1301-
['name' => new Expression(
1302-
':val || :val_0',
1303-
[
1304-
'val' => new Expression('LOWER(:val || :val_0)', ['val' => 'A', 'val_0' => 'B']),
1305-
'val_0' => new Param('C', DataType::STRING),
1306-
],
1307-
)],
1348+
[
1349+
'name' => new Expression(
1350+
':val || :val_0',
1351+
[
1352+
'val' => new Expression('LOWER(:val || :val_0)', ['val' => 'A', 'val_0' => 'B']),
1353+
'val_0' => new Param('C', DataType::STRING),
1354+
],
1355+
),
1356+
],
13081357
'[[name]] != :val || :val_0',
13091358
[
13101359
'val_0' => new Param('F', DataType::STRING),
@@ -1554,8 +1603,8 @@ public static function overlapsCondition(): array
15541603
[new ArrayIterator([0, 1, 2, 7]), 1],
15551604
'null' => [[null], 1],
15561605
'expression' => [new Expression("'[0,1,2,7]'"), 1],
1557-
'json expression' => [new JsonExpression([0,1,2,7]), 1],
1558-
'query expression' => [(new Query(static::getDb()))->select(new JsonExpression([0,1,2,7])), 1],
1606+
'json expression' => [new JsonExpression([0, 1, 2, 7]), 1],
1607+
'query expression' => [(new Query(static::getDb()))->select(new JsonExpression([0, 1, 2, 7])), 1],
15591608
];
15601609
}
15611610

@@ -1564,7 +1613,7 @@ public static function buildColumnDefinition(): array
15641613
$reference = new ForeignKey(
15651614
foreignTableName: 'ref_table',
15661615
foreignColumnNames: ['id'],
1567-
onDelete:ReferentialAction::SET_NULL,
1616+
onDelete: ReferentialAction::SET_NULL,
15681617
onUpdate: ReferentialAction::CASCADE,
15691618
);
15701619

0 commit comments

Comments
 (0)