我們的目標是將產品串列中的某些促銷產品拉到前面。

重要提示：促銷產品因類別/過濾器而異，因此僅插入自定義欄位或使用已內置的“促銷產品”標志是行不通的。我們已經可以訪問要拉到前面的產品 ID，我們只需要相應地對串列進行排序。

我們訂閱ProductListingCriteriaEvent::class并嘗試了一些東西 - 基于https://stackoverflow.com/a/6810827/288568 - 像這樣：

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

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

其中 0x123456... 將是要拉到前面的產品的 UUID。

這當然行不通，因為 Shopware 需要一個欄位。

出于這個原因，是否可以創建類似“虛擬”欄位的內容，或者是否有其他方法可以插入這樣的原始 SQL 部分？

uj5u.com熱心網友回復：

通過裝飾 QueryBuilder 添加新的自定義排序工具

我們可以實作一個新的 Sorting 類，它將特定的 id 拉到前面，然后裝飾CriteriaQueryBuilder以添加這個新的排序型別。

實施細節（在 Shopware 6.4.6.0 上測驗）

首先我們定義一個類來保存新排序方法的資訊：

`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;
    }
}

接下來，我們為定義一個裝飾器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>

接下來，我們實作裝飾器，它保存使用FIELD()方法生成 SQL 的新邏輯。

`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);
    }

}

如何使用新的排序方法

最后，在構建標準時（例如在中ProductListingCriteriaEvent），我們可以通過指定 ID 將特定產品拉到前面。（這里硬編碼，在現實世界中它們來自不同的來源，這取決于所選的過濾器）

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