A while ago I stared at the slow query log of a customer’s shop. Three seconds for this one:

SELECT `product`.`id` FROM `product`<br>LEFT JOIN `product` `product.parent` ON `product`.`parent_id` = `product.parent`.`id`<br>WHERE (`product`.`version_id` = ?) AND ((COALESCE(...) = ?)) LIMIT 24;

Great. But who sent it? The listing? A plugin? The sitemap generator? Some CMS element? The DAL generates SQL, and generated SQL looks the same everywhere. So the usual detective work begins: copy the query, grep the codebase, guess which filter combination produces exactly this WHERE clause, attach Xdebug, cry a little.

Turns out Shopware has a built-in solution for this. It’s one line. It’s used all over the core. And it’s documented exactly nowhere — which is why it kicks off this little series about hidden gems I found while digging through the Shopware 6.7 source.

The gem

$criteria = new Criteria();
$criteria->addFilter(new EqualsFilter('active', true));
$criteria->setTitle('acme-feed-export'); // 👈 that's it

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

From now on, every SQL query generated from this criteria starts with a comment:

# acme-feed-export::read
SELECT `product`.`id`, `product`.`product_number`, ...

The comment is part of the SQL statement that gets sent to MySQL. So it shows up everywhere you look at queries: slow query log, general query log, SHOW FULL PROCESSLIST, the Symfony profiler, pt-query-digest. Your anonymous query now introduces itself.

What happens under the hood

The mechanics are simple, but more thorough than I expected. Three places in the core (line numbers from 6.7.0.0):

Shopware’s DAL QueryBuilder checks for a title when building the SQL and prepends it (Framework/DataAbstractionLayer/Dbal/QueryBuilder.php:100):

if ($this->getTitle()) {
    $sql = '# ' . $this->title . \PHP_EOL . $sql;
}

Nothing new here, you could do that yourself. The nice part is what the DAL does with the title. Depending on the operation, it appends a suffix:

Operation	Comment in SQL
Reading entities (EntityReader)	# acme-feed-export::read
ID search (EntitySearcher)	# acme-feed-export::search-ids
Aggregations (EntityAggregator)	# acme-feed-export::aggregation::<name>

And the best part: associations inherit the title automatically (EntityReader.php:333-336).

$criteria->setTitle('acme-feed-export');
$criteria->addAssociation('cover.media');

produces:

# acme-feed-export::read
SELECT ...

# acme-feed-export::association::cover::read
SELECT ...

You label one criteria and get the whole query tree labeled — including the association queries, which are normally the ones nobody can trace back to anything.

The core does it everywhere

There are about 60 call sites in the core. A few you will recognize in your own query log:

// ProductDetailRoute.php
$criteria->setTitle('product-detail-route');
$criteria->setTitle('product-detail-route::find-best-variant');

// ProductListingRoute.php
$criteria->setTitle('product-listing-route::loading');

// CategoryBreadcrumbBuilder.php
$criteria->setTitle('breadcrumb::category::data');

Open the Symfony profiler on a product detail page in your dev environment and look at the database panel: all the core queries politely introduce themselves. Only yours don’t. Yet.

How we use it

We settled on <plugin>::<use-case> as a naming convention, e.g. acme-erp::stock-sync. That way one grep finds every query a plugin fires:

grep -A2 "^# acme-erp::" /var/log/mysql/slow.log

Cost? Practically zero. Without a title nothing happens, with a title one string gets concatenated. The core sets titles in production, so there is no reason you shouldn’t.

If you work with Shopware’s Dbal\QueryBuilder directly (below the DAL), it has the same setTitle() method.

Limitations

The comment is added by Shopware’s own QueryBuilder, so you get it wherever the DAL is involved — reader, searcher, aggregator, plus the association cascade. Hand-written queries on the raw Doctrine connection don’t get it; there you’d prepend the comment yourself. And the server-side slow query log keeps the comments reliably, but some client tools strip comments when displaying or dumping queries — don’t be surprised.

TL;DR

Put $criteria->setTitle('my-plugin::my-use-case') on your criteria. Every generated query — including association queries — shows up in the slow query log, processlist and profiler with a name tag. One line, zero cost, completely undocumented. Obviously.

Next up in this series: bulk imports done right — the undocumented indexing-skip / indexing-behavior headers and two context states that make large imports orders of magnitude faster.

If you have questions or found more hidden gems, drop me an email!

---

Found in Shopware 6.7.0.0: Criteria::setTitle() in Framework/DataAbstractionLayer/Search/Criteria.php:584, comment injection in Dbal/QueryBuilder.php:100, title cascade in Dbal/EntityReader.php:313-336, Dbal/EntitySearcher.php:90 and Dbal/EntityAggregator.php:171.

Other articles from this category