98 lines
2.6 KiB
PHP
98 lines
2.6 KiB
PHP
<?php
|
|
|
|
namespace KupShop\KupShopBundle\Query;
|
|
|
|
use Query\QueryBuilder;
|
|
|
|
class JsonOperator
|
|
{
|
|
/**
|
|
* @param $column string
|
|
* @param $fieldName string
|
|
*
|
|
* @return string
|
|
*/
|
|
public static function exists($column, $fieldName)
|
|
{
|
|
return "JSON_EXISTS({$column}, '$.{$fieldName}')";
|
|
}
|
|
|
|
/**
|
|
* @param $column string
|
|
* @param $value string
|
|
* @param $mode string
|
|
*/
|
|
public static function search($column, $value, $mode = 'one', $path = '$')
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use (&$counter, $column, $value, $mode, $path) {
|
|
$qb->setParameter("json_search_value_{$counter}", $value);
|
|
$qb->setParameter("json_search_path_{$counter}", $path);
|
|
$cond = "JSON_SEARCH({$column}, '{$mode}', :json_search_value_{$counter}, NULL, :json_search_path_{$counter}) IS NOT NULL";
|
|
$counter++;
|
|
|
|
return $cond;
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @return string
|
|
*/
|
|
public static function contains($column, $fieldName, $value)
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use (&$counter, $column, $fieldName, $value) {
|
|
$qb->setParameter("json_contains_{$counter}", json_encode($value));
|
|
$path = !empty($fieldName) ? ", '$.{$fieldName}'" : '';
|
|
$cond = "JSON_CONTAINS({$column}, :json_contains_{$counter}".$path.')';
|
|
$counter++;
|
|
|
|
return $cond;
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @return string
|
|
*/
|
|
public static function extract($column, $fieldName)
|
|
{
|
|
return "JSON_EXTRACT({$column}, '{$fieldName}')";
|
|
}
|
|
|
|
public static function unquote($value)
|
|
{
|
|
return "JSON_UNQUOTE({$value})";
|
|
}
|
|
|
|
/**
|
|
* @return string
|
|
*/
|
|
public static function value($column, $fieldName, $emptyAsNull = false)
|
|
{
|
|
$expr = "JSON_VALUE({$column}, '$.{$fieldName}')";
|
|
|
|
if ($emptyAsNull) {
|
|
$expr = "IF({$expr}='', NULL, {$expr})";
|
|
}
|
|
|
|
return $expr;
|
|
}
|
|
|
|
/**
|
|
* Calls JSON_SET to set a value in a JSON field while handling null values.
|
|
*/
|
|
public static function set(string $column, string $fieldName, mixed $value): callable
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($counter, $column, $fieldName, $value) {
|
|
$parameter = "json_set_value_{$counter}";
|
|
$qb->setParameter($parameter, json_encode($value));
|
|
|
|
return "JSON_SET(COALESCE({$column}, '{}'), '$.{$fieldName}', JSON_EXTRACT(:{$parameter}, '$'))";
|
|
};
|
|
}
|
|
}
|