Project

General

Profile

Actions

Feature #84115

closed

Missing notInSet equivalent of inSet comparison

Added by Andreas Allacher almost 6 years ago. Updated about 2 years 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

Actions #1

Updated by Andreas Kienast almost 6 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

Actions #2

Updated by Andreas Allacher almost 6 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.

Actions #3

Updated by Andreas Kienast almost 6 years ago

  • Status changed from Rejected to New

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

Actions #4

Updated by Andreas Allacher almost 6 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.

Actions #5

Updated by Wouter Wolters almost 6 years ago

  • Target version deleted (8.7.11)
Actions #6

Updated by Oliver Hader almost 6 years ago

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

Updated by Susanne Moog over 3 years ago

  • Sprint Focus set to PRC
Actions #8

Updated by Benni Mack over 3 years ago

  • Sprint Focus changed from PRC to Needs Decision
Actions #9

Updated by Gerrit Code Review over 2 years 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

Actions #10

Updated by Gerrit Code Review over 2 years 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

Actions #11

Updated by Gerrit Code Review over 2 years 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

Actions #12

Updated by Gerrit Code Review over 2 years 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

Actions #13

Updated by Benni Mack over 2 years ago

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

Updated by Benni Mack about 2 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF