Uh oh!
There was an error while loading.Please reload this page.
- Notifications
You must be signed in to change notification settings - Fork5.2k
[Workflow] Add tip on how to add a WHERE constraint on a multi-state marking store#20086
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.
Already on GitHub?Sign in to your account
base:5.4
Are you sure you want to change the base?
Uh oh!
There was an error while loading.Please reload this page.
Conversation
Ping@lyrixx in case you can review this proposal. Thanks! |
| `scienta/doctrine-json-functions` and enable the `JSON_CONTAINS_PATH` doctrine | ||
| function. Then you can filter for a current place as follows: | ||
| ``$qb->andWhere("JSON_CONTAINS_PATH(item.currentPlaces, 'one', '$.draft') <> 0")`` | ||
| (where `draft` is the place to be checked) |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
I don't know if it works.
I personally use:
privatefunctionfilterByMarking(QueryBuilder$queryBuilder,array$markings,bool$and =false):void {$entityAlias =$queryBuilder->getRootAliases()[0];$contains = [];foreach ($markingsas$marking) {$contains[] =$queryBuilder->expr()->eq(sprintf('JSON_CONTAINS(%s.marking, %s)',$entityAlias,$queryBuilder->expr()->literal(json_encode([$marking =>1], \JSON_THROW_ON_ERROR)), ),1); }if ($and) {$queryBuilder->andWhere($queryBuilder->expr()->andX(...$contains)); }else {$queryBuilder->andWhere($queryBuilder->expr()->orX(...$contains)); }$queryBuilder->andWhere($queryBuilder->expr()->isNotNull("{$entityAlias}.marking")); }
With this class
useDoctrine\ORM\Query\AST\Functions\FunctionNode;useDoctrine\ORM\Query\AST\Node;useDoctrine\ORM\Query\Lexer;useDoctrine\ORM\Query\Parser;useDoctrine\ORM\Query\SqlWalker;class JsonContainsextends FunctionNode{privateconststringFUNCTION_NAME ='JSON_CONTAINS';public ?Node$jsonDocExpr =null;public ?Node$jsonValExpr =null;public ?Node$jsonPathExpr =null;publicfunctiongetSql(SqlWalker$sqlWalker):string {$jsonDoc =$sqlWalker->walkStringPrimary($this->jsonDocExpr);$jsonVal =$sqlWalker->walkStringPrimary($this->jsonValExpr);$jsonPath ='';if ($this->jsonPathExpr) {$jsonPath =',' .$sqlWalker->walkStringPrimary($this->jsonPathExpr); }returnsprintf('%s(%s, %s)',self::FUNCTION_NAME,$jsonDoc,"{$jsonVal}{$jsonPath}"); }publicfunctionparse(Parser$parser):void {$parser->match(Lexer::T_IDENTIFIER);$parser->match(Lexer::T_OPEN_PARENTHESIS);$this->jsonDocExpr =$parser->StringPrimary();$parser->match(Lexer::T_COMMA);$this->jsonValExpr =$parser->StringPrimary();if ($parser->getLexer()->isNextToken(Lexer::T_COMMA)) {$parser->match(Lexer::T_COMMA);$this->jsonPathExpr =$parser->StringPrimary(); }$parser->match(Lexer::T_CLOSE_PARENTHESIS); }}
Anyway, before merging this PR,item.currentPlaces,one, and$.draft must be explained
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
BTW, PostgreSQL and mariadb have different option to deal with JSON
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
I'm not sure we should document this in detail. It looks too convoluted. If this is truly useful/common, could Symfony provide something to ease this? Otherwise, maybe we can just mention that you have to install some package or create your own JSON function to query this.
Not sure what to do here 😐
I think JSON CONTAINS and JSON_CONTAINS PATH in these usages are functional equivalent in these cases but I wonder what is more performant :-) |
Filtering a single state marking store might be straightforward (i.e.,
WHERE currentPlaces = :place).I found filtering on a multiple state marking store with JSON schema more challenging (because of the key-value structure as well).
Also, JSON functions are not default to Doctrine and not standardized among databases.