Adding a custom sorting to listing with an aggregate in shopware 6

Question

I am trying to build a custom sorting for the product listings in shopware 6. I want to include a foreign table (entity is: leasingPlanEntity), get the min of one of the fields of that table (period_price) and then order the search result by that value.

I have already built a Subscriber, and try it like that, what seems to work.

public static function getSubscribedEvents(): array
{
    return [
        //ProductListingCollectFilterEvent::class => 'addFilter'
        ProductListingCriteriaEvent::class => ['addCriteria', 5000]
    ];
}

public function addCriteria(ProductListingCriteriaEvent $event): void
{
    $criteria = $event->getCriteria();
    $criteria->addAssociation('leasingPlan');
    $criteria->addAggregation(new MinAggregation('min_period_price', 'leasingPlan.periodPrice'));

    // Sortierung hinzufügen.
    $availableSortings = $event->getCriteria()->getExtension('sortings') ?? new ProductSortingCollection();

    $myCustomSorting = new ProductSortingEntity();
    $myCustomSorting->setId(Uuid::randomHex());
    $myCustomSorting->setActive(true);
    $myCustomSorting->setTranslated(['label' => 'My Custom Sorting at runtime']);
    $myCustomSorting->setKey('my-custom-runtime-sort');
    $myCustomSorting->setPriority(5);
    $myCustomSorting->setFields([
        [
            'field' => 'leasingPlan.periodPrice',
            'order' => 'asc',
            'priority' => 1,
            'naturalSorting' => 0,
        ],
    ]);

    $availableSortings->add($myCustomSorting);

    $event->getCriteria()->addExtension('sortings', $availableSortings);
}

Is this already the right way to get the min(periodPrice)? Or is it taking just a random value out of the leasingPlan table to define the sort-order?

I didn't find a way, to define the min_period_price aggregate value in the $myCustomSorting->setFields Methods.

---

Update 1

Some days later, I asked a less complex question in the shopware community on slack:

Is it possible to use the DAL to define a subquery for an association in the product-listing? It should generate something like:

FROM
JOIN (
  SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ...
) AS ...

The answer there was:

Don't think so

---

Update 2

I also did an in-deep anlysis of the DAL-Query-Builder, and it really seems to be not possible, to perform a subquery with the current version.

---

Update 3 - Different approach

A different approach might be, to define custom fields in the main entity. Every time a change is made on the main entity, the values of this custom fields should be recalculated. It is a lot of overhead work, to realize this. Especially when the fields you are adding, are dependend on other data like the availability of a product in the store, for example.

So check, if it is worth the extra work. Would be better, to have a solution for building subqueries.

Answer 1

Unfortunately it seems that in your case there is no easy way to achieve this, if I understand the issue correctly.

Consider the following: for each product you can have multiple leasingPlan entities, and I assume that for a given context (like a specific sales channel or listing) that still holds. This means that you would have to sort the leasingPlan entities by price, then take the one with the lowest price, and then sort the products by their lowest-price leasingPlan's price.

There seems to be no other way to achieve that, and unfortunately for you, sorting is applied at the end, even if it is sort of a subquery.

So, for example, if you have the following snippet

$criteria = $event->getCriteria();
$criteria->addAssociation('leasingPlan');
$criteria->getAssociation('leasingPlan')
    ->addSorting(new FieldSorting('price', FieldSorting::ASCENDING))
    ->setLimit(1)
;

The actual price-sorting would be applied AFTER the leasingPlan entities are fetched - essentially the results would be sorted, meaning that you would not get the cheapest leasing plan per product, instead getting the first one.

You can only do something like that with filters, but in this case there is nothing to filter by - I assume you don't have one leasingPlan per SalesChannel or per language, so that you could limit that list to just one entry that could be used for sorting

That is not to mention that this could not be included in a ProductSortingEntity, but you could always work around that by plugging into the appropriate events and modifying the criteria during runtime

I see two ways to resolve your issue

1. Making another table which would store the cheapest leasingPlan per product and just using that as your association
2. Storing the information about the cheapest leasingPlans in e.g. cache and using that for filtering (caution: a mistake here would probably break the sorting, for example if you end up with too few or too many leasingPlans per product)

public function applyCustomSorting(ProductListingCriteriaEvent $event): void
{
    // One leasingPlan per one product
    $cheapestLeasingPlans = $this->myCustomService->getCheapestLeasingPlanIds();
    $criteria = $event->getCriteria();
    $criteria->addAssociation('leasingPlan');
    $criteria->getAssociation('leasingPlan')
        ->addSorting(new FieldSorting('price', FieldSorting::ASCENDING))
        ->addFilter(new EqualsAnyFilter('id', $cheapestLeasingPlans))
    ;
}

And then you could sort by

$criteria->addSorting(new FieldSorting('leasingPlan.periodPrice', FieldSorting::ASCENDING));

Answer 2

There should be no need to add the association manually and to add the aggregation to the criteria, that should happen automatically behind the scenes if your custom sorting is selected in the storefront.

For more information refer to the official docs.