Shopware: IS NULL is not possible?

New week, new task. Our goal was to build a new category page – you’ll be able to purchase it soon as module!

For this we wanted to load all products and their children. To do this we need to filter products simply by parent_id IS NULL.

Carl and me tried hard. All kinds of variants around:

$criteria->addFilter(new NotFilter(MultiFilter::CONNECTION_AND, new EqualsFilter('parentId', null)));

But to no avail.

But why!?

While digging down into the shopware code, the class which takes care of the filters is the interesting piece. There is only one method which contains any NULL at all:

// \Shopware\Core\Framework\DataAbstractionLayer\Search\Parser\SqlQueryParser

private function parseEqualsFilter(EqualsFilter $query, EntityDefinition $definition, string $root, Context $context, bool $negated): ParseResult
{
    $key = $this->getKey();
    $select = $this->queryHelper->getFieldAccessor($query->getField(), $definition, $root, $context);
    $field = $this->queryHelper->getField($query->getField(), $definition, $root);

    $result = new ParseResult();

    if ($field instanceof ListField) {
        if ($query->getValue() === null) {
            $result->addWhere($select . ' IS NULL');

            return $result;
        }
        $result->addWhere('JSON_CONTAINS(' . $select . ', JSON_ARRAY(:' . $key . '))');
        $result->addParameter($key, $query->getValue());

        return $result;
    }

Unfortunately our field is a ParentAssociationField which is a ManyToOneAssociationField which is a AssociationField which is a Field and as you can imagine, none of them is a ListField.

No way to do it

So from our point of view, there is no way with Shopware’s core code to filer by IS NULL.