我如何将QueryBuilder与JSON一起使用

Question

我只是 querybuilder 的新手，确切地说我不知道​​如何在 where 子句中使用 json 对象。我将不胜感激您的迅速帮助。

"post_id": 1
"post": ""
"author": [{"id": 2, "email": "[email protected]"}, {"id": 3, "email": "[email protected]"}]

"post_id": 2
"post": ""
"author": [{"id": 4, "email": "[email protected]"}, {"id": 9, "email": "[email protected]"}]

我想查询表Post，其中author->id是current_user。我已经尽力了。我已经安装了 composer require scienta/doctrine-json-functions

当我运行它时，我发现

"An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: json = unknown\nLINE 1: ... w0_ WHERE json_extract_path(w0_.author, $1) = $2 ORDER...\n                                                             ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.",

我没有想法了。

private function addWhere(QueryBuilder $queryBuilder, string $resourceClass): void
    {
        if (WorkshopSession::class !== $resourceClass || $this->security->isGranted('ROLE_ADMIN') || null === $user = $this->security->getUser()) {
            return;
        }
        
        $rootAlias = $queryBuilder->getRootAliases()[0];
        $queryBuilder->andWhere("JSON_EXTRACT_PATH(o.author, :id) = :current_user");
        $queryBuilder->setParameter('id', 'o.id');
        $queryBuilder->setParameter('current_user', $user->getId());
    
    }

我正在使用 api 平台并使用学说扩展。数据库服务器-postgreSQL

Answer 1

您需要做的第一件事是将选项切换到JSONB，这将帮助您轻松地穿越JSON树而不将自己放在盒子里。

select * from post where author @> '[{"id": "2"}]'; 

在传统的SQL中运行此功能应该可以工作，然后将其转换为DQL。这可能很棘手，因为没有“@＆gt;”在DQL中，您可以利用自定义DQL用户定义的功能。我建议这样做，而不是安装第三方库，该库会在版本控制问题（弃用）中添加一个抽象和复杂性。
如下所示，我们使用功能节点，SQLWalker，Lexer和Parser。

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\AST\Node;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\Lexer;
use function sprintf;

class JsonContains extends FunctionNode
{
    /** @var Node */
    /** @psalm-suppress all */
    private $expr1;

    /** @var Node */
    /** @psalm-suppress all */
    private $expr2;

    public function parse(Parser $parser) : void
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->expr1 = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->expr2 = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);

    }

    public function getSql(SqlWalker $sqlWalker) : string
    {
 
        return sprintf(
            '(%s @> %s)',
            $this->expr1->dispatch($sqlWalker),
            $this->expr2->dispatch($sqlWalker)
        );
    }
}

您已经定义了一个，您可以注册它。 Symfony 5 UP建议使用Doctrine.yaml。您可以按照以下方式进行注册，

dql:
            string_functions:
                JSON_CONTAINS: App\Doctrine\Extension\Functions\DQL\UDF\JsonContains

然后在扩展名中，只需使用它即可。

$queryBuilder->andWhere("JSON_CONTAINS(".$rootAlias .".author, :current_user) = true");
       $queryBuilder->setParameter('current_user', json_encode([['id' => $user->getId()]]));
       

这肯定可能会帮助其他人经历它。

干杯!