Adding a filter to criteria - weird result

Question

I'm trying to filter children of a given parent, searching for all children with certain optionId value. This is the base criteria:

        $criteria = new Criteria();
        $criteria
            ->addAssociation('configuratorSettings.option')
            ->addAssociation('configuratorSettings.option.media')
            ->addAssociation('configuratorSettings.option.group')
            ->addAssociation('media')
            ->addAssociation('options.group')
            ->addAssociation('options.variant')
            ->addAssociation('children')
            ->addAssociation('children.cover')
            ->addAssociation('children.optionIds')
            ->addFilter(new EqualsFilter('id', $id));

This is fine (all children are returned), if I add the following:

$criteria->addFilter(new ContainsFilter('children.optionIds.', $additionalFilter));

I get a weird error Unknown column 'product.children.product_version_id' in 'field list'

What am I doing wrong?

Answer 1

You have a trailing . at children.optionIds..
Adding children.optionIds to the associations is not necessary, since it is not an association field.

This is not reproducible on recent versions, you can run the code here on 6.5.1.0. Which version is this happening with? If this is an older version, it could've been fixed since.

Alternatively you could try fetching the children by their parentId. Since you already seem to have the id of the parent, you might not need to fetch the parent again or you could fetch it separately.

$parentCriteria = new Criteria([$parentId]);
$parentCriteria
    ->addAssociation('configuratorSettings.option')
    ->addAssociation('configuratorSettings.option.media')
    ->addAssociation('configuratorSettings.option.group')
    ->addAssociation('media');
    
// ...

$childCriteria = new Criteria();
$childCriteria
    ->addAssociation('cover')
    ->addAssociation('options.group')
    ->addFilter(new EqualsFilter('parentId', $parentId));

The parent itself shouldn't have options, only the children should have options associated.

Futhermore the use of ContainsFilter also depends on what kind of value $additionalFilter has. ContainsFilter is generally more of a string comparison, as it matches the occurrence of a string inside the value of a text field. While it does work for JSON fields, it would likely be more appropriate to use EqualsFilter or EqualsAnyFilter for matching one ore more ids in a list of ids.

// match multiple id in `optionIds`
$childCriteria->addFilter(new EqualsFilter('optionIds', '897d277ce0b14eeda39221c940d4d285'));

// match multiple ids in `optionIds`
$childCriteria->addFilter(new EqualsAnyFilter('optionIds', ['897d277ce0b14eeda39221c940d4d285', 'eef28122c13c4b178cf275e0f30da49b']));