Skip to content

Commit 6d2af5e

Browse files
TigrovTigrov
authored
Refactor CaseX (#1067)
1 parent 0d64ed6 commit 6d2af5e

8 files changed

Lines changed: 153 additions & 121 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -103,7 +103,7 @@
103103
- New #984: Add `createQuery()` and `select()` methods to `ConnectionInterface` (@Tigrov)
104104
- Chg #985: Rename `insertWithReturningPks()` to `insertReturningPks()` in `CommandInterface` and `DMLQueryBuilderInterface` (@Tigrov)
105105
- Enh #992: Add optional type casting to `DataReaderInterface` using columns (@Tigrov)
106-
- New #988, #1053: Add `CaseExpression` and `CaseExpressionBuilder` to build `CASE-WHEN-THEN-ELSE` SQL expressions (@Tigrov)
106+
- New #988, #1053, #1067: Add `CaseX` and `CaseXBuilder` to build `CASE-WHEN-THEN-ELSE` SQL expressions (@Tigrov)
107107
- Enh #991: Improve types in `ConnectionInterface::transaction()` (@kikara)
108108
- Chg #998: Add `yiisoft/db-implementation` virtual package as dependency (@vjik)
109109
- Chg #999: Remove `requireTransaction()` method and `$isolationLevel` property from `AbstractCommand` (@vjik)

‎docs/guide/en/expressions/statements.md‎

Lines changed: 28 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,6 @@
11
# Statement Expressions
22

33
The library provides classes to represent SQL statements as expressions.
4-
5-
> [!WARNING]
6-
> The statements do not quote string values or column names, use [Value](../../../../src/Expression/Value/Value.php)
7-
> object for string values and [ColumnName](../../../../src/Expression/Value/ColumnName.php) object for column names
8-
> or quote the values directly.
94

105
The following expression classes are available:
116

@@ -15,11 +10,35 @@ The following expression classes are available:
1510

1611
The [CaseX](../../../../src/Expression/Statement/CaseX.php) expression allows you to create SQL `CASE` statements.
1712

13+
The `CaseX` class accepts the following arguments:
14+
15+
- `value` comparison condition in the `CASE` expression:
16+
- `string` is treated as a table column name which will be quoted before usage in the SQL statement;
17+
- `array` is treated as a condition to check, see `QueryInterface::where()`;
18+
- other values will be converted to their string representation using `QueryBuilderInterface::buildValue()`.
19+
If not provided, the `CASE` expression will be a WHEN-THEN structure without a specific case value.
20+
- `valueType` optional data type of the `CASE` expression which can be used in some DBMS to specify the expected type;
21+
- `...args` List of `WHEN-THEN` conditions and their corresponding results represented
22+
as [WhenThen](https://github.com/yiisoft/db/blob/master/src/Expression/Statement/WhenThen.php) instances
23+
or `ELSE` value in the `CASE` expression. String `ELSE` value will be quoted before usage in the SQL statement.
24+
25+
For example:
26+
1827
```php
1928
$case = new CaseX(
20-
new Column('status'),
21-
when1: new Wnen(new Value('active'), new Value('Active User')),
22-
when2: new Wnen("'inactive'", "'Inactive User'"),
23-
else: new Value('Unknown Status'),
29+
'status',
30+
when1: new WnenThen('active', 'Active User'),
31+
when2: new WnenThen('inactive', 'Inactive User'),
32+
else: 'Unknown Status',
2433
);
2534
```
35+
36+
This will generate the following SQL:
37+
38+
```sql
39+
CASE "status"
40+
WHEN 'active' THEN 'Active User'
41+
WHEN 'inactive' THEN 'Inactive User'
42+
ELSE 'Unknown Status'
43+
END
44+
```

‎src/Expression/Statement/Builder/CaseXBuilder.php‎

Lines changed: 18 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
1212

1313
use function gettype;
14-
use function is_string;
14+
use function is_array;
1515

1616
/**
1717
* Builds expressions for {@see CaseX}.
@@ -37,50 +37,50 @@ public function build(ExpressionInterface $expression, array &$params = []): str
3737
$sql = 'CASE';
3838

3939
if ($expression->value !== null) {
40-
$sql .= ' ' . $this->buildCondition($expression->value, $params);
40+
$sql .= ' ' . $this->buildCaseValue($expression->value, $params);
4141
}
4242

43-
foreach ($expression->when as $when) {
44-
$sql .= ' WHEN ' . $this->buildCondition($when->condition, $params);
45-
$sql .= ' THEN ' . $this->buildResult($when->result, $params);
43+
foreach ($expression->whenThen as $whenThen) {
44+
$sql .= ' WHEN ' . $this->buildCondition($whenThen->when, $params);
45+
$sql .= ' THEN ' . $this->queryBuilder->buildValue($whenThen->then, $params);
4646
}
4747

4848
if ($expression->hasElse()) {
49-
$sql .= ' ELSE ' . $this->buildResult($expression->else, $params);
49+
$sql .= ' ELSE ' . $this->queryBuilder->buildValue($expression->else, $params);
5050
}
5151

5252
return $sql . ' END';
5353
}
5454

5555
/**
56-
* Builds the condition part of the CASE expression based on their type.
56+
* Builds the case value part of the CASE expression based on their type.
5757
*
5858
* @return string The SQL condition string.
5959
*/
60-
protected function buildCondition(mixed $condition, array &$params): string
60+
protected function buildCaseValue(mixed $value, array &$params): string
6161
{
6262
/**
6363
* @var string
6464
* @psalm-suppress MixedArgument
6565
*/
66-
return match (gettype($condition)) {
67-
GettypeResult::ARRAY => $this->queryBuilder->buildCondition($condition, $params),
68-
GettypeResult::STRING => $condition,
69-
default => $this->queryBuilder->buildValue($condition, $params),
66+
return match (gettype($value)) {
67+
GettypeResult::ARRAY => $this->queryBuilder->buildCondition($value, $params),
68+
GettypeResult::STRING => $this->queryBuilder->getQuoter()->quoteColumnName($value),
69+
default => $this->queryBuilder->buildValue($value, $params),
7070
};
7171
}
7272

7373
/**
74-
* Builds the result part of the `CASE` expression based on its type.
74+
* Builds the condition part of the CASE expression based on their type.
7575
*
76-
* @return string The SQL result string.
76+
* @return string The SQL condition string.
7777
*/
78-
protected function buildResult(mixed $result, array &$params): string
78+
protected function buildCondition(mixed $condition, array &$params): string
7979
{
80-
if (is_string($result)) {
81-
return $result;
80+
if (is_array($condition)) {
81+
return $this->queryBuilder->buildCondition($condition, $params);
8282
}
8383

84-
return $this->queryBuilder->buildValue($result, $params);
84+
return $this->queryBuilder->buildValue($condition, $params);
8585
}
8686
}

‎src/Expression/Statement/CaseX.php‎

Lines changed: 29 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -22,49 +22,49 @@
2222
*
2323
* ```php
2424
* $case = new CaseX(
25-
* when1: new When('condition1', 'result1'),
26-
* when2: new When('condition2', 'result2'),
27-
* else: 'defaultResult',
25+
* when1: new WhenThen(true, 'result1'),
26+
* when2: new WhenThen(false, 'result2'),
27+
* else: 'default result',
2828
* );
2929
* ```
3030
*
3131
* This will be generated into a SQL `CASE` expression like:
3232
*
3333
* ```sql
3434
* CASE
35-
* WHEN condition1 THEN result1
36-
* WHEN condition2 THEN result2
37-
* ELSE defaultResult
35+
* WHEN TRUE THEN 'result1'
36+
* WHEN FALSE THEN 'result2'
37+
* ELSE 'default result'
3838
* END
3939
* ```
4040
*
4141
* Example with a specific case value:
4242
*
4343
* ```php
4444
* $case = new CaseX(
45-
* 'expression',
46-
* when1: new When(1, 'result1'),
47-
* when2: new When(2, 'result2'),
48-
* else: 'defaultResult',
45+
* 'column_name',
46+
* when1: new WhenThen('one', 'result1'),
47+
* when2: new WhenThen('two', 'result2'),
48+
* else: 'default result',
4949
* );
5050
* ```
5151
*
5252
* This will be generated into a SQL `CASE` expression like:
5353
*
5454
* ```sql
55-
* CASE expression
56-
* WHEN 1 THEN result1
57-
* WHEN 2 THEN result2
58-
* ELSE defaultResult
55+
* CASE "column_name"
56+
* WHEN 'one' THEN 'result1'
57+
* WHEN 'two' THEN 'result2'
58+
* ELSE 'default result'
5959
* END
6060
* ```
6161
*/
6262
final class CaseX implements ExpressionInterface
6363
{
6464
/**
65-
* @var When[] List of `WHEN` conditions and their corresponding results in the `CASE` expression.
65+
* @var WhenThen[] List of `WHEN-THEN` conditions and their corresponding results in the `CASE` expression.
6666
*/
67-
public readonly array $when;
67+
public readonly array $whenThen;
6868
/**
6969
* @var mixed The result to return if no conditions match in the CASE expression.
7070
* If not set, the `CASE` expression will not have an `ELSE` clause.
@@ -75,37 +75,38 @@ final class CaseX implements ExpressionInterface
7575

7676
/**
7777
* @param mixed $value Comparison condition in the `CASE` expression:
78-
* - `string` is treated as a SQL expression;
78+
* - `string` is treated as a table column name which will be quoted before usage in the SQL statement;
7979
* - `array` is treated as a condition to check, see {@see QueryInterface::where()};
8080
* - other values will be converted to their string representation using {@see QueryBuilderInterface::buildValue()}.
81-
* If not provided, the `CASE` expression will be a WHEN-THEN structure without a specific case value.
82-
* @param ColumnInterface|string $valueType Optional data type of the CASE expression which can be used in some DBMS
83-
* to specify the expected type (for example in PostgreSQL).
84-
* @param mixed|When ...$args List of `WHEN` conditions and their corresponding results represented
85-
* as {@see When} instances or `ELSE` value in the `CASE` expression.
81+
* If not provided, the `CASE` expression will be a `WHEN-THEN` structure without a specific case value.
82+
* @param ColumnInterface|string $valueType Optional data type of the `CASE` expression which can be used
83+
* in some DBMS to specify the expected type (for example, in PostgreSQL).
84+
* @param mixed|WhenThen ...$args List of `WHEN-THEN` conditions and their corresponding results represented
85+
* as {@see WhenThen} instances or `ELSE` value in the `CASE` expression. String `ELSE` value will be quoted
86+
* before usage in the SQL statement.
8687
*/
8788
public function __construct(
8889
public readonly mixed $value = null,
8990
public readonly string|ColumnInterface $valueType = '',
9091
mixed ...$args,
9192
) {
92-
$when = [];
93+
$whenThen = [];
9394

9495
foreach ($args as $arg) {
95-
if ($arg instanceof When) {
96-
$when[] = $arg;
96+
if ($arg instanceof WhenThen) {
97+
$whenThen[] = $arg;
9798
} elseif ($this->hasElse()) {
9899
throw new InvalidArgumentException('`CASE` expression can have only one `ELSE` value.');
99100
} else {
100101
$this->else = $arg;
101102
}
102103
}
103104

104-
if (empty($when)) {
105-
throw new InvalidArgumentException('`CASE` expression must have at least one `WHEN` clause.');
105+
if (empty($whenThen)) {
106+
throw new InvalidArgumentException('`CASE` expression must have at least one `WHEN-THEN` clause.');
106107
}
107108

108-
$this->when = $when;
109+
$this->whenThen = $whenThen;
109110
}
110111

111112
/**

‎src/Expression/Statement/When.php‎

Lines changed: 0 additions & 31 deletions
This file was deleted.

‎src/Expression/Statement/WhenThen.php‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\Expression\Statement;
6+
7+
use Yiisoft\Db\Query\QueryInterface;
8+
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
9+
10+
/**
11+
* Represents the condition and the result of a `WHEN-THEN` clause in a SQL `CASE` statement.
12+
*
13+
* @see CaseX
14+
*/
15+
final class WhenThen
16+
{
17+
/**
18+
* @param mixed $when The value or condition for the `WHEN-THEN` clause:
19+
* - `array` is treated as a condition to check, see {@see QueryInterface::where()};
20+
* - other values will be converted to their string representation using {@see QueryBuilderInterface::buildValue()}.
21+
* @param mixed $then The result to return if the condition is `true`. The value will be converted to its string
22+
* representation using {@see QueryBuilderInterface::buildValue()}.
23+
*/
24+
public function __construct(
25+
public readonly mixed $when,
26+
public readonly mixed $then,
27+
) {
28+
}
29+
}

0 commit comments

Comments
 (0)