如何将QueryBuilder与andWhere一起用于json

Question

请注意，我只是querybuilder的新手，确切地说，我不知道如何在where子句中处理json对象。我将感谢你的迅速协助。

"post_id": 1
"post": ""
"author": [{"id": 2, "email": "[e-mail]"}, {"id": 3, "email": "[e-mail]"}]
"post_id": 2
"post": ""
"author": [{"id": 4, "email": "[e-mail]"}, {"id": 9, "email": "[e-mail]"}]

我想查询表Post，其中作者->id是current_user。我已经尽力了。我已经安装了composer require scinta/protice json函数

当我运行它时，我得到了

"An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: json = unknownnLINE 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。这可能很棘手，因为没有"@>"在DQL中，但您可以利用自定义DQL用户定义函数。我建议这样做，而不是安装第三方库，因为它会增加一层抽象和复杂性，并带来版本控制问题(弃用(。正如您在下面看到的，我们使用了FunctionNode、SqlWalker、Lexer和Parser。

use DoctrineORMQueryASTFunctionsFunctionNode;
use DoctrineORMQuerySqlWalker;
use DoctrineORMQueryASTNode;
use DoctrineORMQueryParser;
use DoctrineORMQueryLexer;
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 5up建议使用doctrine.yaml。你可以按照注册它

dql:
string_functions:
JSON_CONTAINS: AppDoctrineExtensionFunctionsDQLUDFJsonContains

然后在你的扩展中，你可以简单地使用它。

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

这肯定会帮助其他人度过难关

干杯！