Shopware 6 DAL Criteria – Build Paginated Product Queries

Use Criteria, Not Raw SQL, for Product Reads

When you need a product list that supports filtering, sorting, association loading, and pagination, the Shopware 6 Data Abstraction Layer (DAL) gives you a single, well‑defined API: the Criteria object combined with an entity repository. Replacing ad‑hoc SQL with Criteria guarantees that the framework applies context rules (currency, language, inheritance) and protects you from common pitfalls such as unbounded queries or duplicate rows caused by joins.

How the DAL Works

• The product.repository service is injected into your class.
• A new Criteria instance is created and enriched with filters, sorting, associations, and pagination data.
• The repository’s search() or searchIds() method is called with the current Context.
• The DAL translates the Criteria into one or more SQL statements, executes them, and hydrates the result set.

Worked Example – Paginated Product Listing

// Inject the repository in the constructor
public function __construct(
    private readonly ProductRepository $productRepository,
    private readonly Context $context
) {}

public function getListing(int $categoryId, int $page = 1): ProductSearchResult
{
    $criteria = new Criteria();
    // Only active products
    $criteria->addFilter(new EqualsFilter('active', true));
    // Products that belong to a specific category
    $criteria->addFilter(new EqualsFilter('categories.id', $categoryId));
    // Load the manufacturer data for each product
    $criteria->addAssociation('manufacturer');
    // Sort by product name ascending
    $criteria->addSorting(new FieldSorting('name', FieldSorting::ASCENDING));
    // Pagination: 24 items per page
    $criteria->setLimit(24);
    $criteria->setPage($page);
    // Request an exact total count for the UI
    $criteria->setTotalCountMode(Criteria::TOTAL_COUNT_MODE_EXACT);

    return $this->productRepository->search($criteria, $this->context);
}

Key points from the code:

• addFilter on the root Criteria limits the rows that will be returned as products.
• Filters inside an association (e.g. $criteria->getAssociation('manufacturer')->addFilter(...)) only affect the data loaded for that association, not the parent product set.
• The DAL automatically calculates the SQL OFFSET from setLimit and setPage (pages are 1‑based).
• Setting TOTAL_COUNT_MODE_EXACT triggers an additional COUNT(*) query, which is necessary if the UI needs to show the total number of pages.

Choosing Between search() and searchIds()

If you only need identifiers—for example, to process a batch of products later—use searchIds(). It returns an IdsSearchResult without hydrating entity objects, making it far cheaper for large result sets.

Method	When to Use	Result Type
search()	Full entity data, associations, and pagination needed.	ProductSearchResult
searchIds()	Only IDs required; later hydration needed.	IdsSearchResult

Common Mistakes and Limits

• Unbounded Criteria – Forgetting setLimit in a request path can load every matching product into memory and crash the application. Always set a reasonable limit or use searchIds() for bulk jobs.
• Root vs. Association Filters – Adding a filter to the root Criteria that references an association field (e.g. manufacturer.name) actually applies to the product table, not the joined manufacturer table. Use $criteria->getAssociation('manufacturer')->addFilter(...) to narrow the manufacturer rows.
• Maximum Limit Enforcement – The Store API and storefront controllers wrap the request parameters in a RequestCriteria object. The API config imposes a maxLimit (often 500). A client cannot ask for more rows than this limit; attempting to do so will silently cap the result.
• Inheritance and Variant Semantics – Products with variants or translations may produce duplicate rows if the query joins multiple tables. Verify row counts when adding complex association filters.
• Aggregations on Large Datasets – While the DAL supports CountAggregation, SumAggregation, etc., for reporting‑style aggregates a dedicated DBAL query or a read‑model is usually clearer and faster.
• Version Drift – Criteria methods and default limits can change between minor releases. Check composer.lock or run bin/console shopware:version to confirm the API version you are targeting.

Verification Checklist

1. Enable SQL logging (config/packages/doctrine.yaml or the Symfony profiler) and inspect the generated statements: confirm the correct joins, LIMIT/OFFSET, and whether an extra COUNT(*) query is issued.
2. Write an integration test against a fixture set: assert that $result->getTotal() equals the expected number of products and that page 1 and page 2 return disjoint slices of size 24.
3. Run bin/console shopware:version to confirm the Shopware version and cross‑reference the official developer docs for that version’s DAL API.
4. Check that the repository service is registered: bin/console debug:container product.repository should list the service.
5. Compare the DAL result with an equivalent hand‑written SQL query against the same database snapshot to validate filter and association semantics.

Practical Takeaway

For any product listing that requires filters, sorting, associations, and pagination, always build a Criteria object and call search() or searchIds() on the injected repository. This approach ensures context‑aware, safe, and maintainable queries while keeping your code portable across Shopware 6 releases.