Feature #84115

Missing notInSet equivalent of inSet comparison

Added by Andreas Allacher about 1 year ago. Updated about 1 year ago.

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

0%

PHP Version:
7.0
Tags:
Complexity:
Sprint Focus:

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

History

#1 Updated by Andreas Fernandez about 1 year 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 about 1 year 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 about 1 year ago

  • Status changed from Rejected to New

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

#4 Updated by Andreas Allacher about 1 year 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 about 1 year ago

  • Target version deleted (8.7.11)

#6 Updated by Oliver Hader about 1 year ago

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

Also available in: Atom PDF