Querying Products with Shopware's Data Abstraction Layer: A Practical Guide

The problem: avoiding raw SQL in Shopware plugins

When you need to read product data from a Shopware store, writing raw SQL queries ties your code to the underlying schema and bypasses Shopware’s extension system. Changes to the database structure or to entity definitions can break your plugin silently, and you lose automatic handling of context such as currency, language, or sales‑channel scope.

Thesis: use the Data Abstraction Layer (DAL) and its Criteria API for safe, maintainable entity queries

Shopware’s DAL exposes every entity through a repository service. By building a Criteria object you can filter, sort, paginate, and eager‑load associations without writing SQL. The DAL translates the Criteria into SQL that respects the current Context, keeping your code compatible with future minor versions.

Understanding the DAL and Criteria API

The DAL is accessed via a repository obtained from the Symfony DI container, for example product.repository. A Criteria object supports:

• Filters: equalsFilter, rangeFilter, containsFilter, multiFilter with AND/OR logic
• Sorting: addSorting
• Pagination: setLimit, setOffset
• Associations: addAssociation to eager‑load related data such as product prices or media

The final call repository->search($criteria, $context) returns an EntitySearchResult containing the entities and the total count.

Worked example: fetch the ten cheapest active products with their cover media

Assume you are working in a service or plugin targeting Shopware 6.5 or later. The example below shows how to build the Criteria, execute the query, and iterate over the results.

use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Filter\EqualsFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Sorting\FieldSorting;

class ProductService
{
    private $productRepository;

    public function __construct(\Shopware\Core\Content\Product\ProductEntityRepository $productRepository)
    {
        $this->productRepository = $productRepository;
    }

    public function getCheapestActiveProducts(Context $context): array
    {
        $criteria = new Criteria();
        // only active products
        $criteria->addFilter(new EqualsFilter('active', true));
        // sort by price ascending
        $criteria->addSorting(new FieldSorting('price', FieldSorting::ASCENDING));
        // eager‑load the cover media association
        $criteria->addAssociation('cover');
        // limit to ten results
        $criteria->setLimit(10);

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

        $products = [];
        foreach ($result->getEntities() as $entity) {
            $products[] = [
                'id' => $entity->getId(),
                'name' => $entity->getName(),
                'price' => $entity->getPrice(),
                'cover' => $entity->getCover() ? $entity->getCover()->getUrl() : null,
            ];
        }

        return $products;
    }
}

Key points in the example:

• The Context object carries the current sales‑channel, language, and currency, ensuring the price returned is correctly converted.
• The association cover is loaded in the same query, avoiding additional lazy‑load calls.
• If you need to debug the generated SQL, enable the Symfony profiler in dev mode and inspect the Doctrine DBAL log.

Trade‑offs and limitations

The DAL adds a thin abstraction layer; for simple look‑ups the overhead is negligible. However, when you add many associations or complex filters, the generated SQL can become large and may perform worse than a hand‑crafted query tailored to a specific report. For bulk exports or analytical queries, consider using the Doctrine DBAL connection directly, but remember that you will then need to manage context, extensions, and version compatibility yourself.

Another common pitfall is forgetting to pass the Context. The same Criteria can return different results depending on the active language, currency, or sales‑channel, which leads to subtle bugs if the context is omitted or incorrectly scoped.

Verification steps

1. Create a minimal plugin or use bin/console with a custom command that calls the method above.
2. Run the command against a local Shopware 6.5/6.6 development instance.
3. Check the returned array for the expected ten products, verify that the active flag is true and that prices are sorted ascending.
4. Enable the Symfony profiler (APP_ENV=dev) and review the Doctrine DBAL panel to see the SQL generated by the DAL.
5. Optionally, write a raw SQL query that reproduces the same filters, sorting, limit, and join to the media table, and compare the result sets.

These steps confirm that the DAL behaves as expected without claiming any benchmark numbers.

Actionable closing

Start by replacing any direct SQL reads in your plugins with repository‑based Criteria queries. Pin your code to a specific Shopware minor version (e.g., 6.5.x) and keep an eye on the changelog for deprecations. When you encounter performance concerns, profile the generated SQL first; only fall back to raw SQL after you have measured that the DAL query is the bottleneck.