Skip to content

Commit 3a06023

Browse files
TigrovTigrov
authored
Add array and json overlaps conditions (#855)
1 parent 37d36b8 commit 3a06023

11 files changed

Lines changed: 268 additions & 7 deletions

‎CHANGELOG.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
- Chg #846: Remove `SchemaInterface::isReadQuery()` and `AbstractSchema::isReadQuery()` methods (@Tigrov)
3030
- Chg #847: Remove `SchemaInterface::getRawTableName()` and `AbstractSchema::getRawTableName()` methods (@Tigrov)
3131
- Enh #852: Add method chaining for column classes (@Tigrov)
32+
- Enh #855: Add array and JSON overlaps conditions (@Tigrov)
3233

3334
## 1.3.0 March 21, 2024
3435

‎docs/guide/en/query/where.md‎

Lines changed: 29 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -211,7 +211,7 @@ Similar to the `not like` operator except that `OR` is used to concatenate the `
211211
Requires one operand which must be an instance of `Yiisoft\Db\Query\Query` representing the sub-query.
212212
It will build an `EXISTS` (sub-query) expression.
213213

214-
## not exists
214+
### not exists
215215

216216
Similar to the `exists` operator and builds a `NOT EXISTS` (sub-query) expression.
217217

@@ -237,6 +237,28 @@ $query->where(['=', $column, $value]);
237237
// $value is safe, but $column name won't be encoded!
238238
```
239239

240+
### array overlaps
241+
242+
Checks if the first array contains at least one element from the second array. Currently supported only by PostgreSQL
243+
and equals to `&&` operator.
244+
245+
Requires two operands:
246+
247+
- Operator 1 should be a column name of an array type or DB expression returning an array;
248+
- Operator 2 should be an array, iterator or DB expression returning an array.
249+
250+
For example, `['array overlaps', 'ids', [1, 2, 3]]` will generate `"ids"::text[] && ARRAY[1,2,3]::text[]`.
251+
252+
### JSON overlaps
253+
254+
Checks if the JSON contains at least one element from the array. Currently supported only by PostgreSQL, MySQL and
255+
SQLite.
256+
257+
Requires two operands:
258+
259+
- Operator 1 should be a column name of a JSON type or DB expression returning a JSON;
260+
- Operator 2 should be an array, iterator or DB expression returning an array.
261+
240262
## Object format
241263

242264
Object format is most powerful yet the most complex way to define conditions.
@@ -272,10 +294,12 @@ Conversion from operator format into object format is performed according
272294
to `Yiisoft\Db\QueryBuilder\AbstractDQLQueryBuilder::conditionClasses` property
273295
that maps operator names to representative class names.
274296

275-
- `AND`, `OR` => `Yiisoft\Db\QueryBuilder\Condition\ConjunctionCondition`.
276-
- `NOT` => `Yiisoft\Db\QueryBuilder\Condition\NotCondition`.
277-
- `IN`, `NOT IN` => `Yiisoft\Db\QueryBuilder\Condition\InCondition`.
278-
- `BETWEEN`, `NOT BETWEEN` => `Yiisoft\Db\QueryBuilder\Condition\BetweenCondition`.
297+
- `AND`, `OR` => `Yiisoft\Db\QueryBuilder\Condition\ConjunctionCondition`;
298+
- `NOT` => `Yiisoft\Db\QueryBuilder\Condition\NotCondition`;
299+
- `IN`, `NOT IN` => `Yiisoft\Db\QueryBuilder\Condition\InCondition`;
300+
- `BETWEEN`, `NOT BETWEEN` => `Yiisoft\Db\QueryBuilder\Condition\BetweenCondition`;
301+
- `ARRAY OVERLAPS` => `Yiisoft\Db\QueryBuilder\Condition\ArrayOverlapsCondition`;
302+
- `JSON OVERLAPS` => `Yiisoft\Db\QueryBuilder\Condition\JsonOverlapsCondition`.
279303

280304
## Appending conditions
281305

‎src/QueryBuilder/AbstractDQLQueryBuilder.php‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -496,6 +496,8 @@ protected function defaultConditionClasses(): array
496496
'OR NOT LIKE' => Condition\LikeCondition::class,
497497
'EXISTS' => Condition\ExistsCondition::class,
498498
'NOT EXISTS' => Condition\ExistsCondition::class,
499+
'ARRAY OVERLAPS' => Condition\ArrayOverlapsCondition::class,
500+
'JSON OVERLAPS' => Condition\JsonOverlapsCondition::class,
499501
];
500502
}
501503

‎src/QueryBuilder/Condition/AbstractOverlapsCondition.php‎

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,84 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\QueryBuilder\Condition;
6+
7+
use Yiisoft\Db\Exception\InvalidArgumentException;
8+
use Yiisoft\Db\Expression\ExpressionInterface;
9+
use Yiisoft\Db\QueryBuilder\Condition\Interface\OverlapsConditionInterface;
10+
11+
use function is_iterable;
12+
use function is_string;
13+
14+
/**
15+
* The base class for classes representing the array and JSON overlaps conditions.
16+
*/
17+
abstract class AbstractOverlapsCondition implements OverlapsConditionInterface
18+
{
19+
public function __construct(
20+
private string|ExpressionInterface $column,
21+
private iterable|ExpressionInterface $values,
22+
) {
23+
}
24+
25+
public function getColumn(): string|ExpressionInterface
26+
{
27+
return $this->column;
28+
}
29+
30+
public function getValues(): iterable|ExpressionInterface
31+
{
32+
return $this->values;
33+
}
34+
35+
/**
36+
* Creates a condition based on the given operator and operands.
37+
*
38+
* @throws InvalidArgumentException If the number of operands isn't 2.
39+
*/
40+
public static function fromArrayDefinition(string $operator, array $operands): static
41+
{
42+
if (!isset($operands[0], $operands[1])) {
43+
throw new InvalidArgumentException("Operator \"$operator\" requires two operands.");
44+
}
45+
46+
/** @psalm-suppress UnsafeInstantiation */
47+
return new static(
48+
self::validateColumn($operator, $operands[0]),
49+
self::validateValues($operator, $operands[1])
50+
);
51+
}
52+
53+
/**
54+
* Validates the given column to be string or `ExpressionInterface`.
55+
*
56+
* @throws InvalidArgumentException If the column isn't a string or `ExpressionInterface`.
57+
*/
58+
private static function validateColumn(string $operator, mixed $column): string|ExpressionInterface
59+
{
60+
if (is_string($column) || $column instanceof ExpressionInterface) {
61+
return $column;
62+
}
63+
64+
throw new InvalidArgumentException(
65+
"Operator \"$operator\" requires column to be string or ExpressionInterface."
66+
);
67+
}
68+
69+
/**
70+
* Validates the given values to be `iterable` or `ExpressionInterface`.
71+
*
72+
* @throws InvalidArgumentException If the values aren't an `iterable` or `ExpressionInterface`.
73+
*/
74+
private static function validateValues(string $operator, mixed $values): iterable|ExpressionInterface
75+
{
76+
if (is_iterable($values) || $values instanceof ExpressionInterface) {
77+
return $values;
78+
}
79+
80+
throw new InvalidArgumentException(
81+
"Operator \"$operator\" requires values to be iterable or ExpressionInterface."
82+
);
83+
}
84+
}

‎src/QueryBuilder/Condition/ArrayOverlapsCondition.php‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\QueryBuilder\Condition;
6+
7+
/**
8+
* Condition that represents `ARRAY OVERLAPS` operator is used to check if a column of array type overlaps another array.
9+
*/
10+
final class ArrayOverlapsCondition extends AbstractOverlapsCondition
11+
{
12+
}

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

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\QueryBuilder\Condition\Builder;
6+
7+
use Yiisoft\Db\Expression\ExpressionBuilderInterface;
8+
use Yiisoft\Db\Expression\ExpressionInterface;
9+
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
10+
11+
/**
12+
* The base class for classes building SQL expressions for array and JSON overlaps conditions.
13+
*/
14+
abstract class AbstractOverlapsConditionBuilder implements ExpressionBuilderInterface
15+
{
16+
public function __construct(protected QueryBuilderInterface $queryBuilder)
17+
{
18+
}
19+
20+
protected function prepareColumn(ExpressionInterface|string $column): string
21+
{
22+
if ($column instanceof ExpressionInterface) {
23+
return $this->queryBuilder->buildExpression($column);
24+
}
25+
26+
return $this->queryBuilder->quoter()->quoteColumnName($column);
27+
}
28+
}

‎src/QueryBuilder/Condition/Interface/OverlapsConditionInterface.php‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\QueryBuilder\Condition\Interface;
6+
7+
use Yiisoft\Db\Expression\ExpressionInterface;
8+
9+
/**
10+
* Represents array and JSON overlaps conditions.
11+
*/
12+
interface OverlapsConditionInterface extends ConditionInterface
13+
{
14+
/**
15+
* @return ExpressionInterface|string The column name or an Expression.
16+
*/
17+
public function getColumn(): string|ExpressionInterface;
18+
19+
/**
20+
* @return ExpressionInterface|iterable An array of values that {@see columns} value should overlap.
21+
*/
22+
public function getValues(): iterable|ExpressionInterface;
23+
}

‎src/QueryBuilder/Condition/Interface/SimpleConditionInterface.php‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,8 +12,7 @@
1212
interface SimpleConditionInterface extends ConditionInterface
1313
{
1414
/**
15-
* @return ExpressionInterface|string The column name. If it's an array, a composite `IN` condition will be
16-
* generated.
15+
* @return ExpressionInterface|string The column name or an Expression.
1716
*/
1817
public function getColumn(): string|ExpressionInterface;
1918

‎src/QueryBuilder/Condition/JsonOverlapsCondition.php‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\QueryBuilder\Condition;
6+
7+
/**
8+
* Condition that represents `JSON OVERLAPS` operator and is used to check if a column of JSON type overlaps an array.
9+
*/
10+
final class JsonOverlapsCondition extends AbstractOverlapsCondition
11+
{
12+
}

‎tests/AbstractQueryBuilderTest.php‎

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,8 @@
2020
use Yiisoft\Db\Expression\ExpressionInterface;
2121
use Yiisoft\Db\Query\Query;
2222
use Yiisoft\Db\Query\QueryInterface;
23+
use Yiisoft\Db\QueryBuilder\Condition\ArrayOverlapsCondition;
24+
use Yiisoft\Db\QueryBuilder\Condition\JsonOverlapsCondition;
2325
use Yiisoft\Db\QueryBuilder\Condition\SimpleCondition;
2426
use Yiisoft\Db\Schema\Builder\ColumnInterface;
2527
use Yiisoft\Db\Schema\QuoterInterface;
@@ -1545,6 +1547,57 @@ public function testsCreateConditionFromArray(): void
15451547
);
15461548
}
15471549
1550+
public function testCreateOverlapsConditionFromArray(): void
1551+
{
1552+
$db = $this->getConnection();
1553+
$qb = $db->getQueryBuilder();
1554+
1555+
$condition = $qb->createConditionFromArray(['array overlaps', 'column', [1, 2, 3]]);
1556+
1557+
$this->assertInstanceOf(ArrayOverlapsCondition::class, $condition);
1558+
$this->assertSame('column', $condition->getColumn());
1559+
$this->assertSame([1, 2, 3], $condition->getValues());
1560+
1561+
$condition = $qb->createConditionFromArray(['json overlaps', 'column', [1, 2, 3]]);
1562+
1563+
$this->assertInstanceOf(JsonOverlapsCondition::class, $condition);
1564+
$this->assertSame('column', $condition->getColumn());
1565+
$this->assertSame([1, 2, 3], $condition->getValues());
1566+
}
1567+
1568+
public function testCreateOverlapsConditionFromArrayWithInvalidOperandsCount(): void
1569+
{
1570+
$db = $this->getConnection();
1571+
$qb = $db->getQueryBuilder();
1572+
1573+
$this->expectException(InvalidArgumentException::class);
1574+
$this->expectExceptionMessage('Operator "JSON OVERLAPS" requires two operands.');
1575+
1576+
$qb->createConditionFromArray(['json overlaps', 'column']);
1577+
}
1578+
1579+
public function testCreateOverlapsConditionFromArrayWithInvalidColumn(): void
1580+
{
1581+
$db = $this->getConnection();
1582+
$qb = $db->getQueryBuilder();
1583+
1584+
$this->expectException(InvalidArgumentException::class);
1585+
$this->expectExceptionMessage('Operator "JSON OVERLAPS" requires column to be string or ExpressionInterface.');
1586+
1587+
$qb->createConditionFromArray(['json overlaps', ['column'], [1, 2, 3]]);
1588+
}
1589+
1590+
public function testCreateOverlapsConditionFromArrayWithInvalidValues(): void
1591+
{
1592+
$db = $this->getConnection();
1593+
$qb = $db->getQueryBuilder();
1594+
1595+
$this->expectException(InvalidArgumentException::class);
1596+
$this->expectExceptionMessage('Operator "JSON OVERLAPS" requires values to be iterable or ExpressionInterface.');
1597+
1598+
$qb->createConditionFromArray(['json overlaps', 'column', 1]);
1599+
}
1600+
15481601
/**
15491602
* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::createIndex
15501603
*/

0 commit comments

Comments
 (0)