daccess1/yii2-json-query-helper

Yii2 helper class, used to generate query expressions for JSON columns.

v1.0.0-stable 2020-12-21 20:21 UTC

Requires

• yiisoft/yii2: ~2.0.0

MIT

• b.kruchinkin

README

JSON Query Helper for Yii 2

This package provides a JSONQueryHelper class, that allows you to generate yii\db\Expression expressions for searching by content of JSON columns.

Installation

The preferred way to install this package is through composer:

php composer.phar require --prefer-dist daccess1/yii2-json-query-helper

Usage

To use this helper, simply it's result to ActiveQuery builder, like this

use daccess1\JSONQueryHelper\JSONQueryHelper;

Model::find()->where(JSONQueryHelper::JSONContains(['json_column', [123, 'value_2']]))->all();

The code above will generate an SQL query like this:

SELECT * FROM `model` WHERE (JSON_CONTAINS(`json_column`, "123", "$") OR JSON_CONTAINS(`json_column`, "value_2", "$"))

Values array can contain any number of elements with mixed types. Parameters array can contain either two or three values, it's structure is similar to default Yii2 query builder params. See examples of possible ways of usage below for more details.

//Check if single value is present in JSON
JSONQueryHelper::JSONContains(['json_column', 'value_1'])

//Check if any of values is present in JSON
JSONQueryHelper::JSONContains(['or', 'json_column', ['value_1', 'value_2' ... 'value_n']])
//Equivalent shorthand
JSONQueryHelper::JSONContains(['json_column', ['value_1', 'value_2' ... 'value_n']])

//Check if all values is present in JSON
JSONQueryHelper::JSONContains(['and', 'json_column', ['value_1', 'value_2' ... 'value_n']])

You may also set the specific path in for JSON where to find the value. To do so, set the field as an array, with field name as the first item and path as second one. See SQL JSON_CONTAINS documentation for more details on formatting path. Example usage:

//Check if single value is present in JSON field in path 'values'
JSONQueryHelper::JSONContains(['and', ['json_column', '$.values'], ['value_1', 'value_2']])
//This expression will be generated by the code above
JSON_CONTAINS(`json_column`, "value_1", "$.values") AND JSON_CONTAINS(`json_column`, "value_2", "$.values")

//You can specify path with any usage cases shown in examples above, like this
JSONQueryHelper::JSONContains([['json_column', '$.values'], 'value_1'])
JSONQueryHelper::JSONContains([['json_column', '$.values'], ['value_1', 'value_2' ... 'value_n']])

Requirements

• PHP 7.2 - 7.4