Pull Certain Products to Front by Adding Custom SQL to Criteria

Question

Our aim is to pull certain promoted products in the product listing to front.

Important: The promoted products differ by category / filter, so it would not work to just insert a custom field or use the "promoted products" flag which is already built in. We already have access to the the product IDs to pull to front, we just need to sort the list accordingly.

We subscribed to ProductListingCriteriaEvent::class and tried something - based on https://stackoverflow.com/a/6810827/288568 - like this:

$criteria = $event->getCriteria()
$sortings = $criteria->getSorting();
$criteria->resetSorting();
 
$criteria->addSorting(new FieldSorting('FIELD(id, 0x123456...)', FieldSorting::DESCENDING));

foreach($sortings as $sorting) {
    $criteria->addSorting($sorting);
}

Where 0x123456... would be the UUID of the product to to pull to front.

This of course does not work, because Shopware expects a field.

Is it possible to create something like a "virtual" field for this reason or are there other ways to insert such a raw SQL part?

Answer 1

Adding a New Custom Sorting facility by decorating the QueryBuilder

We can implement a new Sorting class which takes specific ids to pull to front and then decorate the CriteriaQueryBuilder to add this new sorting type.

Implementation details (tested on Shopware 6.4.6.0)

First we define a class to hold the information for the new sorting method:

CustomSorting.php

<?php declare(strict_types=1);

namespace ExampleProductListing\Framework\DataAbstractionLayer\Search\Sorting;

use Shopware\Core\Framework\DataAbstractionLayer\Search\Sorting\FieldSorting;


class CustomSorting extends FieldSorting
{
    private array $ids = [];


    public function addId(string $id)
    {
        $this->ids[] = $id;
    }

    public function getIds()
    {
        return $this->ids;
    }
}

Next, we define a decorator for the CriteriaQueryBuilder:

services.xml

<service id="ExampleProductListing\Framework\DataAbstractionLayer\Dbal\CriteriaQueryBuilderDecorator"
             decorates="Shopware\Core\Framework\DataAbstractionLayer\Dbal\CriteriaQueryBuilder">
    <argument type="service" id="ExampleProductListing\Framework\DataAbstractionLayer\Dbal\CriteriaQueryBuilderDecorator.inner"/>
    <argument type="service" id="Shopware\Core\Framework\DataAbstractionLayer\Search\Parser\SqlQueryParser"/>
    <argument type="service" id="Shopware\Core\Framework\DataAbstractionLayer\Dbal\EntityDefinitionQueryHelper"/>
    <argument type="service" id="Shopware\Core\Framework\DataAbstractionLayer\Search\Term\SearchTermInterpreter"/>
    <argument type="service" id="Shopware\Core\Framework\DataAbstractionLayer\Search\Term\EntityScoreQueryBuilder"/>
    <argument type="service" id="Shopware\Core\Framework\DataAbstractionLayer\Dbal\JoinGroupBuilder"/>
    <argument type="service"
              id="Shopware\Core\Framework\DataAbstractionLayer\Dbal\FieldResolver\CriteriaPartResolver"/>

</service>

Next, we implement the decorator, which holds the new logic for generating the SQL with the FIELD() method.

CriteriaQueryBuilderDecorator.php

<?php declare(strict_types=1);

namespace ExampleProductListing\Framework\DataAbstractionLayer\Dbal;

use ExampleProductListing\Framework\DataAbstractionLayer\Search\Sorting\CustomSorting;
use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\Dbal\CriteriaQueryBuilder;
use Shopware\Core\Framework\DataAbstractionLayer\Dbal\EntityDefinitionQueryHelper;
use Shopware\Core\Framework\DataAbstractionLayer\Dbal\FieldResolver\CriteriaPartResolver;
use Shopware\Core\Framework\DataAbstractionLayer\Dbal\JoinGroupBuilder;
use Shopware\Core\Framework\DataAbstractionLayer\Dbal\QueryBuilder;
use Shopware\Core\Framework\DataAbstractionLayer\EntityDefinition;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\Filter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Parser\SqlQueryParser;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Term\EntityScoreQueryBuilder;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Term\SearchTermInterpreter;

class CriteriaQueryBuilderDecorator extends CriteriaQueryBuilder
{
    private $decoratedService;


    /***
     * @var EntityDefinitionQueryHelper
     */
    private $helper;

    public function __construct(
        CriteriaQueryBuilder        $decoratedService,
        SqlQueryParser              $parser,
        EntityDefinitionQueryHelper $helper,
        SearchTermInterpreter       $interpreter,
        EntityScoreQueryBuilder     $scoreBuilder,
        JoinGroupBuilder            $joinGrouper,
        CriteriaPartResolver        $criteriaPartResolver
    )
    {
        $this->decoratedService = $decoratedService;
        $this->helper = $helper;

        parent::__construct($parser, $helper,$interpreter, $scoreBuilder, $joinGrouper, $criteriaPartResolver);
    }

    public function getDecorated(): CriteriaQueryBuilder
    {
        return $this->decoratedService;
    }

    public function addSortings(EntityDefinition $definition, Criteria $criteria, array $sortings, QueryBuilder $query, Context $context): void
    {
        foreach ($sortings as $sorting) {
            if ($sorting instanceof CustomSorting) {

                $accessor = $this->helper->getFieldAccessor($sorting->getField(), $definition, $definition->getEntityName(), $context);

                $ids = implode(',', array_reverse($sorting->getIds()));
                if (empty($ids)) {
                    continue;
                }

                $query->addOrderBy('FIELD(' . $accessor . ',' . $ids . ')', 'DESC');
            } else {
                $this->decoratedService->addSortings($definition, $criteria, [$sorting], $query, $context);
            }
        }
    }

    public function build(QueryBuilder $query, EntityDefinition $definition, Criteria $criteria, Context $context, array $paths = []): QueryBuilder
    {
        return parent::build($query, $definition, $criteria, $context, $paths);
    }

    public function addFilter(EntityDefinition $definition, ?Filter $filter, QueryBuilder $query, Context $context): void
    {
        parent::addFilter($definition, $filter, $query, $context);
    }

}

How to use the new sorting method

Finally, when building the criteria (for example in ProductListingCriteriaEvent) we can pull specific products to front by specifying there IDs. (hard coded here, in real world they come from a different source, which depends on the chosen filters)

    $customSorting = new CustomSorting('product.id');
    $customSorting->addId('0x76f9a07e153645d7bd8ad62abd131234');
    $customSorting->addId('0x76a890cb23ea433a97006e71cdb75678');
    $event->getCriteria()
        ->addSorting($customSorting);

Compatibility

This works only for the SQL engine. If ElasticSearch should also be supported, this probably would work by decorating the ElasticSearch Query Buidler as well.

Answer 2

Raw SQL is by design not supported, as the query with that criteria can also be parsed and executed by ElasticSearch if you use the ElasticSearch integration.

Instead of handling it all within the read you could use a simple custom field that you sort for. You could add a myCustomSorting custom field to the products and set the value of that field to 1 for all products that you want to show up first. Then you extend the criteria to first sort by that field.