Feature #84115

Missing notInSet equivalent of inSet comparison

Added by Andreas Allacher over 3 years ago. Updated about 1 month ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2018-03-02
Due date:
% Done:

100%

Estimated time:
PHP Version:
7.0
Tags:
Complexity:
Sprint Focus:
Needs Decision

Description

Sometimes one needs to check if a value is NOT in a set, so the opposite of inSet.
The DBAL expressionbuilder provides "like" and "notLike" etc.
However, for inSet that is not available.
It should probably be enough to just
do something like

'NOT (' . inSet(...) . ')' 

to achieve this

#1

Updated by Andreas Fernandez over 3 years ago

  • Status changed from New to Rejected

Hi, thank you for your request.

The QueryBuilder indeed support this by using $queryBuilder->expr()->notIn(). Thus, I will close this ticket.

Kind regards,
Andreas

#2

Updated by Andreas Allacher over 3 years ago

Hi Andreas,

how should $queryBuilder->expr()->notIn()
be the same as a negate of $queryBuilder->expr()->inSet() ?

   return $this->comparison(
            $this->connection->quoteIdentifier($fieldName),
            'NOT IN',
            '(' . implode(', ', (array)$value) . ')'
        );

This is notIn whereas inSet looks like this:

   public function inSet(string $fieldName, string $value, bool $isColumn = false): string
    {
        if ($value === '') {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::inSet() can not be used with an empty string value.',
                1459696089
            );
        }
        if (strpos($value, ',') !== false) {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::inSet() can not be used with values that contain a comma (",").',
                1459696090
            );
        }
        switch ($this->connection->getDatabasePlatform()->getName()) {
            case 'postgresql':
            case 'pdo_postgresql':
                return $this->comparison(
                    $isColumn ? $value . '::text' : $this->literal($this->unquoteLiteral((string)$value)),
                    self::EQ,
                    sprintf(
                        'ANY(string_to_array(%s, %s))',
                        $this->connection->quoteIdentifier($fieldName) . '::text',
                        $this->literal(',')
                    )
                );
                break;
            case 'oci8':
            case 'pdo_oracle':
                throw new \RuntimeException(
                    'FIND_IN_SET support for database platform "Oracle" not yet implemented.',
                    1459696680
                );
                break;
            case 'sqlsrv':
            case 'pdo_sqlsrv':
            case 'mssql':
                // See unit and functional tests for details
                if ($isColumn) {
                    $expression = $this->orX(
                        $this->eq($fieldName, $value),
                        $this->like($fieldName, $value . ' + \',%\''),
                        $this->like($fieldName, '\'%,\' + ' . $value),
                        $this->like($fieldName, '\'%,\' + ' . $value . ' + \',%\'')
                    );
                } else {
                    $likeEscapedValue = str_replace(
                        ['[', '%'],
                        ['[[]', '[%]'],
                        $this->unquoteLiteral($value)
                    );
                    $expression = $this->orX(
                        $this->eq($fieldName, $this->literal($this->unquoteLiteral((string)$value))),
                        $this->like($fieldName, $this->literal($likeEscapedValue . ',%')),
                        $this->like($fieldName, $this->literal('%,' . $likeEscapedValue)),
                        $this->like($fieldName, $this->literal('%,' . $likeEscapedValue . ',%'))
                    );
                }
                return (string)$expression;
            case 'sqlite':
            case 'sqlite3':
            case 'pdo_sqlite':
                if (strpos($value, ':') === 0 || $value === '?') {
                    throw new \InvalidArgumentException(
                        'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.',
                        1476029421
                    );
                }
                return $this->comparison(
                    implode('||', [
                        $this->literal(','),
                        $this->connection->quoteIdentifier($fieldName),
                        $this->literal(','),
                    ]),
                    'LIKE',
                    $this->literal(
                        '%,' . $this->unquoteLiteral($value) . ',%'
                    )
                );
                break;
            default:
                return sprintf(
                    'FIND_IN_SET(%s, %s)',
                    $value,
                    $this->connection->quoteIdentifier($fieldName)
                );
        }
    }

I am quite sure that noIn shouldn't produce the same result.
Even if: There should still be a method notInSet that then just calls notIn.

#3

Updated by Andreas Fernandez over 3 years ago

  • Status changed from Rejected to New

Sorry, I misunderstood the purpose. I've re-opened the ticket again.

#4

Updated by Andreas Allacher over 3 years ago

An example for this method would be to check if a backend user is not in a certain usergroup - where usergroups are stored as a comma separated list.

#5

Updated by Wouter Wolters over 3 years ago

  • Target version deleted (8.7.11)
#6

Updated by Oliver Hader over 3 years ago

  • Subject changed from DBAL: missing notInSet equivalent of inSet comparison to Missing notInSet equivalent of inSet comparison
#7

Updated by Susanne Moog over 1 year ago

  • Sprint Focus set to PRC
#8

Updated by Benni Mack over 1 year ago

  • Sprint Focus changed from PRC to Needs Decision
#9

Updated by Gerrit Code Review 3 months ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/70152

#10

Updated by Gerrit Code Review 3 months ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/70152

#11

Updated by Gerrit Code Review 3 months ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/70152

#12

Updated by Gerrit Code Review 3 months ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/70152

#13

Updated by Benni Mack 3 months ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
#14

Updated by Benni Mack about 1 month ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF