TYPO3 Core

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.

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.

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

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

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