Filtering and Paginating Product Lists in Shopware 6 with Doctrine Criteria

Problem: Building dynamic product filters without raw SQL

When you need a product listing that reacts to user‑selected filters (price, attributes, custom fields) and also supports pagination, writing raw SQL quickly becomes brittle and hard to maintain. Shopware 6 already ships with a Doctrine‑based repository for products, but many developers are unsure how to leverage its Criteria object to keep the query building declarative and safe.

Thesis: Use Doctrine Criteria via ProductRepository::search()

Shopware’s ProductRepository accepts a Doctrine\Common\Collections\Criteria instance. The repository translates the criteria into a SELECT for the data and a matching SELECT COUNT(*) for pagination, all while respecting Shopware’s entity extensions and sales‑channel context.

Section 1: How Criteria works in Shopware

Doctrine Criteria lets you add:

• Where conditions with Criteria::expr()
• Sorting via Criteria::orderBy()
• Pagination with Criteria::setFirstResult() (offset) and Criteria::setMaxResults() (limit)

In Shopware 6.5+ the ORM automatically maps extension fields (custom fields) to the underlying table, so you can reference them by their property name without extra mapping. Prior to 6.5 you had to add a @ORM\Column or a custom DQL extension for each field.

Section 2: Building a Criteria object for filters and pagination

The following example shows a controller action that reads query parameters, builds a Criteria, and returns a paginated product list. Place the code in a custom plugin’s controller or a Symfony controller that has access to the service container.

// src/Controller/ProductListingController.php
namespace MyPlugin\Controller;

use Shopware\Core\Framework\Context;
use Shopware\Core\Content\Product\ProductEntity;
use Shopware\Core\Content\Product\ProductRepositoryInterface;
use Doctrine\Common\Collections\Criteria;
use Doctrine\Common\Collections\Expr\Comparison;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;

class ProductListingController extends AbstractController
{
    private ProductRepositoryInterface $productRepository;

    public function __construct(ProductRepositoryInterface $productRepository)
    {
        $this->productRepository = $productRepository;
    }

    #[Route('/api/product-list', name: 'api.product_list', methods: ['GET'])]
    public function list(Context $context): JsonResponse
    {
        // ----- read request parameters -----
        $page = max(1, (int)($_GET['page'] ?? 1));
        $limit = min(100, max(1, (int)($_GET['limit'] ?? 20)));
        $minPrice = isset($_GET['min_price']) ? (float)$_GET['min_price'] : null;
        $maxPrice = isset($_GET['max_price']) ? (float)$_GET['max_price'] : null;
        $customValue = $_GET['custom_field'] ?? null; // e.g. a custom field "rating"

        // ----- build Criteria -----
        $criteria = new Criteria();
        $criteria->setFirstResult(($page - 1) * $limit); // offset
        $criteria->setMaxResults($limit);                // limit

        // price range filter
        if ($minPrice !== null) {
            $criteria->andWhere(new Comparison('price', Comparison::GT, $minPrice));
        }
        if ($maxPrice !== null) {
            $criteria->andWhere(new Comparison('price', Comparison::LT, $maxPrice));
        }

        // custom field filter (only works if the field is mapped)
        if ($customValue !== null) {
            // assume the custom field is stored as a string extension "rating"
            $criteria->andWhere(new Comparison('extension.rating', Comparison::EQ, $customValue));
        }

        // optional sorting
        $criteria->addOrderBy('price', Criteria::ASC);

        // ----- execute search -----
        $result = $this->productRepository->search($criteria, $context);

        // ----- prepare response -----
        $products = [];
        foreach ($result->getEntities() as $product) {
            /** @var ProductEntity $product */
            $products[] = [
                'id' => $product->getId(),
                'name' => $product->getName(),
                'price' => $product->getPrice()->getGross(),
            ];
        }

        return new JsonResponse([
            'total' => $result->getTotal(),
            'page' => $page,
            'limit' => $limit,
            'products' => $products,
        ]);
    }
}

Where to run: Place the file in your plugin’s src/Controller directory, then run bin/console plugin:refresh and clear the cache (bin/console cache:clear). The controller needs no special permissions beyond the usual Shopware access to the DI container.

Section 3: Trade‑offs and limitations

• N+1 risk: Filtering on unindexed custom fields can cause the ORM to load related extensions row‑by‑row. Verify by opening Shopware’s debug toolbar (enable APP_ENV=dev) and inspect the generated SQL. If you see many similar queries, add a database index on the custom field’s column.
• Version sensitivity: In Shopware 6.4 and earlier, extension fields must be explicitly mapped in src/Resources/config/orm/products.xml or via a custom DQL extension. Starting with 6.5, the ORM auto‑detects extension properties, reducing boilerplate.
• Mixing raw queries: If you later add a raw Doctrine\DBAL\Connection query alongside the Criteria‑based search, you may unintentionally duplicate WHERE clauses or break the COUNT query. Keep all filtering inside the Criteria object when possible.

Actionable closing: Verify and improve

1. Enable the debug toolbar (APP_ENV=dev) and open the product list endpoint. Look for two queries: a SELECT COUNT(*) and a SELECT ... LIMIT … OFFSET …. Ensure the WHERE clauses contain the price and custom‑field conditions you set.
2. Write a simple unit test that asserts the repository returns the expected total count and page size:

// tests/Unit/ProductListingTest.php
public function testCriteriaPagination():
    void
    {
        $criteria = new Criteria();
        $criteria->setFirstResult(0);
        $criteria->setMaxResults(10);
        $criteria->andWhere(new Comparison('price', Comparison::GT, 20));
        
        $result = $this->productRepository->search($criteria, $context);
        
        $this->assertEquals(10, $result->getEntities()->count());
        $this->assertGreaterThanOrEqual(10, $result->getTotal());
    }

If the test fails, check that the custom field is indexed (SHOW INDEX FROM product_product;) and that the extension is properly registered in extensions.xml.

By sticking to Doctrine Criteria you keep your filter logic readable, avoid raw SQL, and gain automatic pagination counts—just remember to watch for unindexed fields and version‑specific mapping requirements.