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?
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.
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);
}
}
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);
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.