How to Use the Criteria API for Complex Queries in Shopware 6.7

Shopware 6.7 introduces significant enhancements to its data fetching capabilities through the Criteria API, providing developers with powerful tools to build complex and efficient database queries. The Criteria API serves as the backbone for all data retrieval operations within Shopware, offering a robust and flexible way to fetch entities with advanced filtering, sorting, and pagination options.

Understanding the Criteria API Foundation#

The Criteria API in Shopware 6.7 is built upon the Doctrine DBAL (Database Abstraction Layer) but provides a more developer-friendly interface specifically designed for Shopware's entity structure. At its core, the Criteria class acts as a query builder that translates PHP objects into efficient SQL queries.

use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;

$criteria = new Criteria();
$criteria->addFilter(new EqualsFilter('active', true));

This fundamental approach allows developers to construct complex queries without writing raw SQL, while still maintaining the performance benefits of optimized database operations.

Advanced Filtering Techniques#

Complex Filter Combinations#

Shopware 6.7's Criteria API supports sophisticated filter combinations through logical operators. The MultiFilter class enables you to create OR and AND conditions that were previously challenging to implement:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\MultiFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\EqualsFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\ContainsFilter;

$criteria = new Criteria();
$criteria->addFilter(
    new MultiFilter(MultiFilter::CONNECTION_OR, [
        new EqualsFilter('manufacturerId', 'uuid-123'),
        new ContainsFilter('name', 'product')
    ])
);

Date and Range Filtering#

Date-based filtering has been significantly enhanced in Shopware 6.7, supporting various date comparison operators:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\RangeFilter;

$criteria = new Criteria();
$criteria->addFilter(
    new RangeFilter('createdAt', [
        RangeFilter::GTE => '2023-01-01',
        RangeFilter::LTE => '2023-12-31'
    ])
);

Sorting and Pagination#

Advanced Sorting Options#

The sorting capabilities in Shopware 6.7 have been expanded to support multi-level sorting with custom orderings:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Sorting\FieldSorting;

$criteria = new Criteria();
$criteria->addSorting(
    new FieldSorting('name', FieldSorting::ASCENDING)
);
$criteria->addSorting(
    new FieldSorting('price', FieldSorting::DESCENDING)
);

Custom Pagination Strategies#

Shopware 6.7 introduces more flexible pagination options, including offset-based and cursor-based pagination for handling large datasets efficiently:

$criteria = new Criteria();
$criteria->setLimit(50);
$criteria->setOffset(100);
$criteria->setTotalCountMode(Criteria::TOTAL_COUNT_MODE_EXACT);

Association Loading and Nested Queries#

One of the most powerful features of Shopware 6.7's Criteria API is its ability to handle complex association loading with nested queries:

use Shopware\Core\Framework\DataAbstractionLayer\Search\AssociationCriteria;

$criteria = new Criteria();
$criteria->addAssociation('categories', function (AssociationCriteria $association) {
    $association->addFilter(new EqualsFilter('active', true));
    $association->addSorting(new FieldSorting('name'));
});

This nested approach allows you to filter associations at the database level, significantly improving performance compared to loading all associations and filtering in PHP.

Performance Optimization Techniques#

Selective Field Loading#

Shopware 6.7 enables developers to specify exactly which fields should be loaded, reducing memory usage and improving query performance:

$criteria = new Criteria();
$criteria->setLimit(100);
$criteria->addAssociation('media');
$criteria->addSelect('id', 'name', 'active');

Query Caching Strategies#

The Criteria API integrates seamlessly with Shopware's caching mechanisms, allowing you to cache complex queries that are frequently executed:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;

$criteria = new Criteria();
$criteria->setCacheKey('product-list-cache-key');
$criteria->setCacheLifetime(3600);

Advanced Query Patterns#

Subqueries and Related Entity Filtering#

Shopware 6.7 supports advanced subquery patterns that allow filtering based on related entity conditions:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\ExistsFilter;

$criteria = new Criteria();
$criteria->addFilter(
    new ExistsFilter('orders', function (Criteria $subCriteria) {
        $subCriteria->addFilter(new EqualsFilter('status', 'completed'));
        $subCriteria->setLimit(1);
    })
);

Aggregation and Grouping#

The API now supports aggregation operations directly within criteria, enabling complex analytical queries:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Aggregation\SumAggregation;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Aggregation\AvgAggregation;

$criteria = new Criteria();
$criteria->addAggregation(
    new SumAggregation('total', 'price')
);
$criteria->addAggregation(
    new AvgAggregation('average', 'price')
);

Integration with Custom Entities#

When working with custom entities, the Criteria API provides full compatibility and enhanced functionality:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\RangeFilter;

$criteria = new Criteria();
$criteria->addFilter(
    new RangeFilter('customField', [
        RangeFilter::GTE => 100,
        RangeFilter::LTE => 1000
    ])
);
$criteria->addSorting(new FieldSorting('customField'));

Error Handling and Debugging#

Shopware 6.7 includes enhanced debugging capabilities for Criteria queries, making it easier to identify performance bottlenecks:

use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;

$criteria = new Criteria();
$criteria->setDebug(true); // Enable debug mode

// The query will be logged with detailed execution information

Best Practices and Performance Tips#

Efficient Query Construction#

When building complex queries, it's crucial to consider the database index structure. Shopware 6.7's Criteria API automatically optimizes queries based on available indexes:

// Good: Filter on indexed fields first
$criteria = new Criteria();
$criteria->addFilter(new EqualsFilter('active', true));
$criteria->addFilter(new EqualsFilter('category', 'electronics'));

// Bad: Filtering on non-indexed fields can cause performance issues
$criteria->addFilter(new ContainsFilter('description', 'keyword'));

Memory Management#

For large datasets, always implement appropriate limits and use pagination:

$criteria = new Criteria();
$criteria->setLimit(50);
$criteria->setOffset(0);
$criteria->setTotalCountMode(Criteria::TOTAL_COUNT_MODE_NONE); // For better performance when total count is not needed

Conclusion#

Shopware 6.7's Criteria API represents a significant advancement in data fetching capabilities, providing developers with the tools necessary to build efficient, scalable applications. The enhanced filtering, sorting, and association handling features make it possible to construct complex queries that were previously difficult or impossible to implement efficiently.

By leveraging these advanced features properly, developers can create applications that not only perform well but also maintain clean, readable code. The API's integration with Shopware's caching and performance optimization mechanisms ensures that even the most complex queries remain efficient and responsive.

The key to mastering the Criteria API lies in understanding when to use specific filtering techniques, how to optimize queries for performance, and how to leverage the built-in debugging capabilities for troubleshooting. As Shopware continues to evolve, the Criteria API will undoubtedly become even more powerful, making it an essential skill for any Shopware developer working with version 6.7 and beyond.

Whether you're building simple product listings or complex analytical dashboards, the Criteria API provides the foundation for efficient data retrieval that scales with your application's needs while maintaining the performance standards that Shopware users expect.