使用Doctrine2请求JSON类型字段中的键/值 [英] Request on key/value in a JSON type field with Doctrine2

问题描述

我试图弄清楚在symfony 3.4应用程序中如何根据"json"类型列中特定键的值检索(通过存储库方法，例如，使用DQL请求)实体.看到postgre可能有一些东西，但是我用mariaDB没有找到任何东西

I'm trying to figure out how, in a symfony 3.4 app, to retrieve (through a repository method, with a DQL request for example) entities depending on a value for a specific key in a "json" typed column. Saw there's some stuff possible with postgre but I didnt find anything with mariaDB

假设我有一个实体 Letter

具有此属性:

/**
 *
 * @ORM\Column(type="json")
 */
private $metadatas;

其中包含例如:

 {
    "key1": "value",
    "key2": "value"
 }

如何或者是否可以请求数据库获取元数据列中特定键的具有特定值的字母.

How can I, or, Is it possible to request my DB to get letters with a specific value for a specific key in metadatas column.

类似的东西:

public function getByKeyValue($key, $value)
      {
          $em = $this->_em;
          $dql = "SELECT l FROM AppBundle:Letter l
                  WHERE l.metadatas->:key = :value
                  ";

          $query = $em->createQuery($dql);
          $query->setParameter('key', $key);
          $query->setParameter('value', $value);


          return $query->getResult();
      } 

一些信息:

php7.1，mariadb 10.2+，教义/dbal ^ 2.6，教义orm ^ 2.5

php7.1, mariadb 10.2+, doctrine/dbal ^2.6, doctrine orm ^2.5

非常感谢.

推荐答案

您可以使用 ScientaNL/DoctrineJsonFunctions

通过添加以下内容，通过composer安装它:

Install it through composer by adding:

"scienta/doctrine-json-functions": "~4.0",

注册原则配置中所需的json函数，在本例中为 JSON_CONTAINS :

Register the json function that is needed in the doctrine configuration, in this case JSON_CONTAINS:

doctrine:
    orm:
        entity_managers:
            some_em: # usually also "default"
                dql:
                    string_functions:
                        JSON_CONTAINS: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains

就我而言，我刚刚添加:

In my case, I just added:

doctrine:
    orm:
         dql:
              string_functions:
                  JSON_CONTAINS: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains

使用它:

$queryBuilder = $this->getDoctrine()->getRepository('AppBundle:Letter')->createQueryBuilder('lt');
$queryBuilder
        ->where("JSON_CONTAINS(lt.metadatas, :mvalue, '$.key') = 1");

$queryBuilder->setParameter('mvalue', '"value"');
$query = $queryBuilder->getQuery();
return $query->getResult();

在dql中，它应该类似于:

In dql, it should be something like:

$dql = "SELECT l FROM AppBundle:Letter l
              WHERE JSON_CONTAINS(lt.metadatas, :mvalue, '$.key') = 1
              ";

请注意 $.key 是要过滤的json键，并且mvalue应该以json编码格式包含，在这种情况下，应使用双引号.

Note $.key is the json key to filter and mvalue should be included in its json encoded format, in this case with double quotes.

参考文献:

MySql json-search-functions

这篇关于使用Doctrine2请求JSON类型字段中的键/值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！