How can i make associations in Shopware 6?

Question

Basically I want the same result that this SQL would return:

SELECT id FROM property_group_option opt
    LEFT JOIN property_group_option_translation tra 
        ON opt.id = tra.property_group_option_id
    WHERE opt.property_group_id = <id> AND tra.name = "<name>"

The way to go described in the Shopware documentation does not seem to get the right result. I have tried the following:

$criteria = (new Criteria())
        ->addFilter(new EqualsFilter('property_group_id', $propertyGroupId))
        ->getAssociation('property_group_option_translation')
            ->addFilter(new EqualsFilter('name', $value));

$result = $this->propertyGroupOptionRepository->search($criteria, Context::createDefaultContext())->first()->getId();

I did find a workaround which does not seem to be the way to go (and is also not the best performing), but it did return the right result.

$criteria = (new Criteria());
$criteria->addFilter(new EqualsFilter('name', $value));
$criteria->getAssociation('property_group_option')
    ->addFilter(new EqualsFilter('groupId', $propertyGroupId));

$translation = $this->propertyGroupOptionTranslationRepository->search($criteria, Context::createDefaultContext())->first()

if($translation !== null) {
    $criteria = (new Criteria([$translation->get('propertyGroupOptionId')]));
    $result = $this->propertyGroupOptionRepository->search($criteria, Context::createDefaultContext())->first()->getId();
}

is there a proper solution for that?

Answer 1

You are using the table name. Try to use the model name instead.

Answer 2

You have to use addAssociation() first - getAssociation() seems to return a new Criteria instance which is later not used in the actual query, if the association did not exist before:

See in the code.

public function getAssociation(string $path): Criteria
{
    $parts = explode('.', $path);

    $criteria = $this;
    foreach ($parts as $part) {
        if ($part === 'extensions') {
            continue;
        }

        if (!$criteria->hasAssociation($part)) {
            $criteria->associations[$part] = new Criteria(); // this is returned, but not stored
        }

        $criteria = $criteria->associations[$part];
    }

    return $criteria;
}