Fix N+1 Queries in a Symfony App

The agent starts from the alert and the route it names, then reads how this app loads data. In a Symfony app like symfony/demo, composer.json requires PHP 8.4, Symfony 8, doctrine/orm ^3.5, doctrine/doctrine-bundle ^3.0, PHPUnit 11.5 with dama/doctrine-test-bundle, doctrine/doctrine-fixtures-bundle and phpstan/phpstan-doctrine, and .env points DATABASE_URL at data/database.sqlite with a database_test.sqlite beside it. config/packages/doctrine.yaml enables profiling_collect_backtrace in debug, which means the web profiler's database panel already shows every query with the line that caused it. The agent reads the repository first: PostRepository::findLatest already does this right, with addSelect('a', 't'), an innerJoin('p.author', 'a') and a leftJoin('p.tags', 't'), so the blog index renders author names and tags from one query, and the agent says so rather than rewriting it.

The problem is on the post page. BlogController::postShow receives the Post through the {slug:post} route mapping, which loads the post alone, and templates/blog/post_show.html.twig then loops post.comments, a lazy OneToMany on Post ordered by publishedAt, and prints comment.author.fullName for each, where Comment::author is a ManyToOne with the default lazy fetch. That is one query for the comments and one more per distinct commenter. The agent proves it before changing anything: it loads the fixtures from src/DataFixtures/AppFixtures.php, requests a post page with the profiler enabled and reads the query count from the db collector, noting that the fixtures give every comment to the same user, so the identity map hides most of the cost locally while a post with thirty different commenters in production pays thirty queries.

The fix follows the pattern the repository already uses. The agent adds PostRepository::findOneBySlugWithComments, a query builder that selects the post, leftJoins p.comments and each comment's author with addSelect, and keeps the publishedAt ordering the entity declares; postShow gets a #[MapEntity] attribute that resolves the slug through that method, the same attribute commentNew already imports for its own route. Nothing changes in the Twig template or the entities, and the OrderBy on Post::comments still applies. The agent then re-reads the admin pages, Admin/BlogController::index and its findBy(['author' => $user]), and the RSS output that shares findLatest, and reports that neither has a lazy load in a loop.

The proof is a test. tests/Controller/BlogControllerTest.php already extends WebTestCase and checks the index, the RSS feed, a new comment and the search, so the agent adds a test that enables the profiler on the client, requests a post page, and asserts the db collector's query count against a fixed ceiling, failing with the count and the queries when it is exceeded. It runs vendor/bin/phpunit, which phpunit.dist.xml configures with failOnDeprecation, failOnWarning and the DAMA extension that wraps each test in a transaction, and vendor/bin/phpstan analyse, and puts both outputs in the pull request beside the before and after query counts from the profiler.

What comes back is a pull request with a new method in src/Repository/PostRepository.php, a #[MapEntity] attribute on postShow in src/Controller/BlogController.php, and a new test in tests/Controller/BlogControllerTest.php. The description shows the query list the profiler's db collector produced for a post page before the change and after it, with the count on each, and quotes the vendor/bin/phpunit and vendor/bin/phpstan analyse output from the branch.

The description also says what was checked and left alone: findLatest already joins author and tags, the admin post list reads no relation in its loop, and the RSS feed shares the index query. That way a reviewer knows the agent looked at the whole blog, not only the route in the alert.

A reviewer checks the join does not change the comment order the entity declares, that the ceiling in the new test leaves room for the queries the security layer adds on a logged-in request, and that the fixtures still load. Then they merge, and the test keeps the page from regressing when someone adds comment.author.image to the template next year.

In the Project that has your repository, add an automation with this prompt and a webhook trigger. Rush shows its URL and secret once. A Grafana contact point can send a custom Authorization: Bearer <secret> header, so a latency alert on the post route posts straight to the trigger; the alert payload follows the prompt as the event, and the agent reads the route from it. An alerting service that cannot set that header posts to a small handler on your server, which forwards the payload with the header.

Each fire starts one run in its own sandbox, duplicate deliveries with the same Idempotency-Key are dropped, and the automation's max_runs_per_hour keeps a flapping alert from starting a run a minute.

An N+1 is easy to fix and hard to prove fixed: the profiler panel shows it in development, but the fixtures give every comment to one author, so the local count looks fine and the production page still runs thirty queries. In a Rush sandbox the agent loads the fixtures, reads the real query list from the profiler collector, makes the change, reads it again, and keeps both lists in the transcript beside the diff.

The pull request carries a test that makes the fix permanent, run under the project's own phpunit.dist.xml with deprecations and warnings failing, and the PHPStan pass with the Doctrine extension that would catch a wrong DQL alias. The alert that started it is answered with a pull request and the evidence, not a note to look into it.

Why not set fetch EAGER on Comment::author?

How does the test count queries?

Will the fixtures show the problem?

Does it change the templates?

What if the alert names a route that is fine?