Skip to content

Commit 68d3a0f

Browse files
TigrovTigrov
authored
Add json overlaps condition builder (#310)
1 parent a31bdf6 commit 68d3a0f

5 files changed

Lines changed: 120 additions & 0 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@
66
- Enh #273: Implement `ColumnSchemaInterface` classes according to the data type of database table columns
77
for type casting performance. Related with yiisoft/db#752 (@Tigrov)
88
- Chg #307: Replace call of `SchemaInterface::getRawTableName()` to `QuoterInterface::getRawTableName()` (@Tigrov)
9+
- Enh #310: Add JSON overlaps condition builder (@Tigrov)
910

1011
## 1.2.0 March 21, 2024
1112

‎src/Builder/JsonOverlapsConditionBuilder.php‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\Sqlite\Builder;
6+
7+
use Yiisoft\Db\Exception\Exception;
8+
use Yiisoft\Db\Exception\InvalidArgumentException;
9+
use Yiisoft\Db\Exception\InvalidConfigException;
10+
use Yiisoft\Db\Exception\NotSupportedException;
11+
use Yiisoft\Db\Expression\ExpressionInterface;
12+
use Yiisoft\Db\Expression\JsonExpression;
13+
use Yiisoft\Db\QueryBuilder\Condition\Builder\AbstractOverlapsConditionBuilder;
14+
use Yiisoft\Db\QueryBuilder\Condition\JsonOverlapsCondition;
15+
16+
/**
17+
* Builds expressions for {@see JsonOverlapsCondition} for SQLite Server.
18+
*/
19+
final class JsonOverlapsConditionBuilder extends AbstractOverlapsConditionBuilder
20+
{
21+
/**
22+
* Build SQL for {@see JsonOverlapsCondition}.
23+
*
24+
* @param JsonOverlapsCondition $expression The {@see JsonOverlapsCondition} to be built.
25+
*
26+
* @throws Exception
27+
* @throws InvalidArgumentException
28+
* @throws InvalidConfigException
29+
* @throws NotSupportedException
30+
*/
31+
public function build(ExpressionInterface $expression, array &$params = []): string
32+
{
33+
$column = $this->prepareColumn($expression->getColumn());
34+
$values = $expression->getValues();
35+
36+
if (!$values instanceof ExpressionInterface) {
37+
$values = new JsonExpression($values);
38+
}
39+
40+
$values = $this->queryBuilder->buildExpression($values, $params);
41+
42+
return "EXISTS(SELECT value FROM json_each($column) INTERSECT SELECT value FROM json_each($values))=1";
43+
}
44+
}

‎src/DQLQueryBuilder.php‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,10 +12,12 @@
1212
use Yiisoft\Db\Query\QueryInterface;
1313
use Yiisoft\Db\QueryBuilder\AbstractDQLQueryBuilder;
1414
use Yiisoft\Db\QueryBuilder\Condition\InCondition;
15+
use Yiisoft\Db\QueryBuilder\Condition\JsonOverlapsCondition;
1516
use Yiisoft\Db\QueryBuilder\Condition\LikeCondition;
1617
use Yiisoft\Db\Sqlite\Builder\ExpressionBuilder;
1718
use Yiisoft\Db\Sqlite\Builder\InConditionBuilder;
1819
use Yiisoft\Db\Sqlite\Builder\JsonExpressionBuilder;
20+
use Yiisoft\Db\Sqlite\Builder\JsonOverlapsConditionBuilder;
1921
use Yiisoft\Db\Sqlite\Builder\LikeConditionBuilder;
2022

2123
use function array_filter;
@@ -135,6 +137,7 @@ public function buildUnion(array $unions, array &$params = []): string
135137
protected function defaultExpressionBuilders(): array
136138
{
137139
return array_merge(parent::defaultExpressionBuilders(), [
140+
JsonOverlapsCondition::class => JsonOverlapsConditionBuilder::class,
138141
LikeCondition::class => LikeConditionBuilder::class,
139142
InCondition::class => InConditionBuilder::class,
140143
JsonExpression::class => JsonExpressionBuilder::class,

‎tests/QueryBuilderTest.php‎

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,10 +9,12 @@
99
use Yiisoft\Db\Exception\InvalidArgumentException;
1010
use Yiisoft\Db\Exception\InvalidConfigException;
1111
use Yiisoft\Db\Exception\NotSupportedException;
12+
use Yiisoft\Db\Expression\Expression;
1213
use Yiisoft\Db\Expression\ExpressionInterface;
1314
use Yiisoft\Db\Expression\JsonExpression;
1415
use Yiisoft\Db\Query\Query;
1516
use Yiisoft\Db\Query\QueryInterface;
17+
use Yiisoft\Db\QueryBuilder\Condition\JsonOverlapsCondition;
1618
use Yiisoft\Db\Schema\SchemaInterface;
1719
use Yiisoft\Db\Sqlite\Column;
1820
use Yiisoft\Db\Sqlite\Tests\Support\TestTrait;
@@ -776,4 +778,61 @@ public function testSelectScalar(array|bool|float|int|string $columns, string $e
776778
{
777779
parent::testSelectScalar($columns, $expected);
778780
}
781+
782+
public function testJsonOverlapsConditionBuilder(): void
783+
{
784+
$db = $this->getConnection();
785+
$qb = $db->getQueryBuilder();
786+
787+
$params = [];
788+
$sql = $qb->buildExpression(new JsonOverlapsCondition('column', [1, 2, 3]), $params);
789+
790+
$this->assertSame(
791+
'EXISTS(SELECT value FROM json_each(`column`) INTERSECT SELECT value FROM json_each(:qp0))=1',
792+
$sql
793+
);
794+
$this->assertSame([':qp0' => '[1,2,3]'], $params);
795+
796+
// Test column as Expression
797+
$params = [];
798+
$sql = $qb->buildExpression(new JsonOverlapsCondition(new Expression('column'), [1, 2, 3]), $params);
799+
800+
$this->assertSame(
801+
'EXISTS(SELECT value FROM json_each(column) INTERSECT SELECT value FROM json_each(:qp0))=1',
802+
$sql
803+
);
804+
$this->assertSame([':qp0' => '[1,2,3]'], $params);
805+
806+
$db->close();
807+
}
808+
809+
/** @dataProvider \Yiisoft\Db\Sqlite\Tests\Provider\QueryBuilderProvider::overlapsCondition */
810+
public function testJsonOverlapsCondition(iterable|ExpressionInterface $values, int $expectedCount): void
811+
{
812+
$db = $this->getConnection(true);
813+
814+
$count = (new Query($db))
815+
->from('json_type')
816+
->where(new JsonOverlapsCondition('json_col', $values))
817+
->count();
818+
819+
$this->assertSame($expectedCount, $count);
820+
821+
$db->close();
822+
}
823+
824+
/** @dataProvider \Yiisoft\Db\Sqlite\Tests\Provider\QueryBuilderProvider::overlapsCondition */
825+
public function testJsonOverlapsConditionOperator(iterable|ExpressionInterface $values, int $expectedCount): void
826+
{
827+
$db = $this->getConnection(true);
828+
829+
$count = (new Query($db))
830+
->from('json_type')
831+
->where(['json overlaps', 'json_col', $values])
832+
->count();
833+
834+
$this->assertSame($expectedCount, $count);
835+
836+
$db->close();
837+
}
779838
}

‎tests/Support/Fixture/sqlite.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,8 @@ DROP TABLE IF EXISTS "negative_default_values";
1515
DROP TABLE IF EXISTS "animal";
1616
DROP TABLE IF EXISTS "default_pk";
1717
DROP TABLE IF EXISTS "notauto_pk";
18+
DROP TABLE IF EXISTS "timestamp_default";
19+
DROP TABLE IF EXISTS "json_type";
1820
DROP VIEW IF EXISTS "animal_view";
1921
DROP TABLE IF EXISTS "T_constraints_4";
2022
DROP TABLE IF EXISTS "T_constraints_3";
@@ -25,6 +27,7 @@ DROP TABLE IF EXISTS "T_upsert_1";
2527
DROP TABLE IF EXISTS "T_constraints_check";
2628
DROP TABLE IF EXISTS "foreign_keys_parent";
2729
DROP TABLE IF EXISTS "foreign_keys_child";
30+
DROP TABLE IF EXISTS "json_type";
2831

2932
CREATE TABLE "profile" (
3033
id INTEGER NOT NULL,
@@ -173,6 +176,11 @@ CREATE TABLE "timestamp_default" (
173176
timestamp_text TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
174177
); -- STRICT
175178

179+
CREATE TABLE "json_type" (
180+
id INTEGER PRIMARY KEY,
181+
json_col JSON
182+
);
183+
176184
CREATE VIEW "animal_view" AS SELECT * FROM "animal";
177185

178186
INSERT INTO "animal" ("type") VALUES ('yiiunit\data\ar\Cat');
@@ -216,6 +224,11 @@ INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VA
216224
INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (2, 3, 1, 8.0);
217225
INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (3, 2, 1, 40.0);
218226

227+
INSERT INTO "json_type" (json_col) VALUES (null);
228+
INSERT INTO "json_type" (json_col) VALUES ('[]');
229+
INSERT INTO "json_type" (json_col) VALUES ('[1,2,3,null]');
230+
INSERT INTO "json_type" (json_col) VALUES ('[3,4,5]');
231+
219232
/* bit test, see https://github.com/yiisoft/yii2/issues/9006 */
220233

221234
DROP TABLE IF EXISTS "bit_values";

0 commit comments

Comments
 (0)