How do I use QueryBuilder with andWhere for json-CodePudding

please I am just new to querybuilder and precisely I don't know how to work with json object in a where clause. I would appreciate your swift assistance.

"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]"}]

I want to query the table Post where the author->id is current_user. I have tried all I can. I have installed composer require scienta/doctrine-json-functions

When I ran it, I got

"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.",

I am out of ideas.

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());
    
    }

I am using api-platform and using doctrine extensions. Database Server - postgreSQL

CodePudding user response：

First thing you need to do, is to switch option to jsonb that would help you traverse the json tree easily without putting yourself in a box.

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

Running this in your traditional sql should work, then convert this to DQL. This may be tricky as there is no '@>' in DQL but you can take advantage of custom DQL User Defined Function. I recommend doing this instead of installing a third party library that would add a layer of abstraction and complexity with versioning issue (deprecation). As you can see below, we make use of FunctionNode, SqlWalker, Lexer and 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)
        );
    }
}

One you have defined this, you can register it. Symfony 5 up suggests to use doctrine.yaml. You can register it as follows

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

Then in your extensions, you can simply use it.

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

This may definitely help others going through it.

Cheers!