Feature #84115

Missing notInSet equivalent of inSet comparison

Added by Andreas Allacher about 3 years ago. Updated 11 months ago.

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

0%

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 about 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 almost 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 almost 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 almost 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 almost 3 years ago

  • Target version deleted (8.7.11)
#6

Updated by Oliver Hader almost 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 12 months ago

  • Sprint Focus set to PRC
#8

Updated by Benni Mack 11 months ago

  • Sprint Focus changed from PRC to Needs Decision

Also available in: Atom PDF