Filter Does-not-Contains for groups and user column does not work

[Aveyron-RetD]

Steps to reproduce

1. Create a column user
2. Make it possible to add multiple users

3. Create a view
4. use a filter column users
5. select Does not contain userid

Expected behavior

Filter works if there are multiple users

Actual behavior

Filter does not work it only works if the column defaults to one user. I thought it was possible but looks like i cannot see below....

Tables app version

2.1.1

Browser

150

Client operating system

Windows

Operating system

No response

Web server

None

PHP engine version

None

Database

None

Additional info

seen in lib/Db/ColumnTypes/UsergroupColumnQB.php

<?php

/**
 * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
 * SPDX-License-Identifier: AGPL-3.0-or-later
 */

namespace OCA\Tables\Db\ColumnTypes;

use OCP\DB\QueryBuilder\IQueryBuilder;

class UsergroupColumnQB extends SuperColumnQB implements IColumnTypeQB {
	public function passSearchValue(IQueryBuilder $qb, string $unformattedSearchValue, string $operator, string $searchValuePlaceHolder): void {
		// TODO how to handle searching for multiple users/groups?
		$qb->setParameter($searchValuePlaceHolder, $unformattedSearchValue, IQueryBuilder::PARAM_STR);
	}
}

[Aveyron-RetD]

Note for myself patch to try at home.

✅ Full UsergroupColumnQB.php (minimal LIKE‑based fix)

<?php

/**
 * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
 * SPDX-License-Identifier: AGPL-3.0-or-later
 */

namespace OCA\Tables\Db\ColumnTypes;

use OCP\DB\QueryBuilder\IQueryBuilder;
use OCP\DB\QueryBuilder\IQueryFunction;

class UsergroupColumnQB extends SuperColumnQB implements IColumnTypeQB {

    /**
     * Pass raw ID (UUID, groupId, circleId) to the query.
     * No wildcard manipulation here — we handle that in the LIKE expressions.
     */
    public function passSearchValue(IQueryBuilder $qb, string $unformattedSearchValue, string $operator, string $placeholder): void {
        $qb->setParameter($placeholder, $unformattedSearchValue, IQueryBuilder::PARAM_STR);
    }

    /**
     * Custom CONTAINS logic for user/group/circle/team columns.
     * Matches: %"ID"%
     */
    public function getContainsExpression(IQueryBuilder $qb, string $columnName, string $placeholder): mixed {
        $value = $qb->getParameter($placeholder);
        return $qb->expr()->like(
            $columnName,
            $qb->createNamedParameter('%"' . $value . '"%')
        );
    }

    /**
     * Custom DOES-NOT-CONTAIN logic for user/group/circle/team columns.
     * Matches: NOT %"ID"%
     */
    public function getNotContainsExpression(IQueryBuilder $qb, string $columnName, string $placeholder): mixed {
        $value = $qb->getParameter($placeholder);
        return $qb->expr()->notLike(
            $columnName,
            $qb->createNamedParameter('%"' . $value . '"%')
        );
    }

    /**
     * Override filter routing ONLY for contains / does-not-contain.
     * Everything else falls back to SuperColumnQB.
     */
    public function addWhereFilterExpression(IQueryBuilder $qb, array $filter, string $filterId): IQueryFunction {
        $placeholder = 'searchValue' . $filterId;

        // Pass raw ID (UUID, groupId, circleId)
        $this->passSearchValue($qb, $filter['value'], $filter['operator'], $placeholder);

        // Usergroup column always stores JSON in "value"
        $columnName = 'value';

        switch ($filter['operator']) {
            case 'contains':
                return $this->getContainsExpression($qb, $columnName, $placeholder);

            case 'does-not-contain':
                return $this->getNotContainsExpression($qb, $columnName, $placeholder);

            default:
                // All other operators (equal, not-equal, etc.) use generic logic
                return parent::addWhereFilterExpression($qb, $filter, $filterId);
        }
    }
}

[Aveyron-RetD]

Ok after debugging the problem is not in Contains it does work it is in does-not-contain that does not work.... Following Ai debugging we should the search in lowercase

Note to myself again to test at home

<?php

/**
 * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
 * SPDX-License-Identifier: AGPL-3.0-or-later
 */

namespace OCA\Tables\Db\ColumnTypes;

use OCP\DB\QueryBuilder\IQueryBuilder;
use OCP\DB\QueryBuilder\IQueryFunction;

class UsergroupColumnQB extends SuperColumnQB implements IColumnTypeQB {

    /**
     * Pass raw ID (user, group, team, circle).
     * DO NOT lowercase here — we lowercase only inside LIKE patterns.
     */
    public function passSearchValue(IQueryBuilder $qb, string $unformattedSearchValue, string $operator, string $placeholder): void {
        $qb->setParameter($placeholder, $unformattedSearchValue, IQueryBuilder::PARAM_STR);
    }

    /**
     * CONTAINS → %"id"%
     * Lowercase the search value because SuperColumnQB lowercases the JSON.
     */
    public function getContainsExpression(IQueryBuilder $qb, string $columnName, string $placeholder): mixed {
        $value = strtolower($qb->getParameter($placeholder));
        return $qb->expr()->like(
            $columnName,
            $qb->createNamedParameter('%"' . $value . '"%')
        );
    }

    /**
     * DOES-NOT-CONTAIN → NOT %"id"%
     * Exact match, lowercased, safe for multiple IDs and mixed types.
     */
    public function getNotContainsExpression(IQueryBuilder $qb, string $columnName, string $placeholder): mixed {
        $value = strtolower($qb->getParameter($placeholder));
        return $qb->expr()->notLike(
            $columnName,
            $qb->createNamedParameter('%"' . $value . '"%')
        );
    }

    /**
     * Override only contains / does-not-contain.
     * All other operators use SuperColumnQB.
     */
    public function addWhereFilterExpression(IQueryBuilder $qb, array $filter, string $filterId): IQueryFunction {
        $placeholder = 'searchValue' . $filterId;

        // Pass raw ID (UUID, groupId, teamId, circleId)
        $this->passSearchValue($qb, $filter['value'], $filter['operator'], $placeholder);

        // Usergroup JSON is always stored in "value"
        $columnName = 'value';

        switch ($filter['operator']) {
            case 'contains':
                return $this->getContainsExpression($qb, $columnName, $placeholder);

            case 'does-not-contain':
                return $this->getNotContainsExpression($qb, $columnName, $placeholder);

            default:
                return parent::addWhereFilterExpression($qb, $filter, $filterId);
        }
    }
}