Symfony 3.4+Sonata Admin页面重复查询溯源问题求助

Absolutely! You can track down those annoying repeated queries by hooking into Doctrine's event system or adding targeted debug calls—no need to rely solely on the Symfony Profiler's default output. Here are a few practical approaches:

1. Use a Doctrine Event Subscriber (Most Reliable) #

Doctrine lets you listen to query events, which is perfect for catching repetitive SQL and dumping the call stack to see where it’s originating. Here’s how to set it up:

First, create an event subscriber class:

// src/AppBundle/EventListener/QueryDebugListener.php
namespace AppBundle\EventListener;

use Doctrine\Common\EventSubscriber;
use Doctrine\ORM\Events;
use Doctrine\ORM\Event\QueryEventArgs;
use Symfony\Component\VarDumper\VarDumper;

class QueryDebugListener implements EventSubscriber
{
    public function getSubscribedEvents(): array
    {
        return [Events::preExecute];
    }

    public function preExecute(QueryEventArgs $args): void
    {
        $sql = $args->getQuery()->getSQL();
        
        // Replace this with a unique snippet from your repetitive query
        // (e.g., the table name or a specific field that identifies the duplicate queries)
        if (strpos($sql, 'your_target_table') !== false) {
            // Dump the SQL and the full call stack to trace the origin
            VarDumper::dump([
                'SQL' => $sql,
                'Call Stack' => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 15)
            ]);
            // Or use dd() to halt execution immediately
            // dd($sql, debug_backtrace());
        }
    }
}

Then register the subscriber in your services.yml:

# app/config/services.yml
services:
    app.query_debug_listener:
        class: AppBundle\EventListener\QueryDebugListener
        tags:
            - { name: doctrine.event_subscriber, connection: default }

When you load the Sonata Admin page, this will dump the call stack every time the target query runs—you’ll see exactly which method (in Sonata Admin or your own code) is triggering it.

2. Target Sonata Admin Directly (Quick Debug) #

Since the issue is isolated to a specific Sonata Admin page, you can add temporary debug calls right in your Admin class to narrow things down:

// src/AppBundle/Admin/YourEntityAdmin.php
namespace AppBundle\Admin;

use Sonata\AdminBundle\Admin\AbstractAdmin;
use Symfony\Component\VarDumper\VarDumper;

class YourEntityAdmin extends AbstractAdmin
{
    // Override createQuery to check the DQL being generated
    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);
        
        // Only run in dev environment to avoid breaking production
        if ($this->getConfigurationPool()->getContainer()->get('kernel')->getEnvironment() === 'dev') {
            VarDumper::dump($query->getDQL());
            // Optional: Dump call stack here too
            // VarDumper::dump(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 10));
        }
        
        return $query;
    }
}

You can also check related methods like configureListFields or configureDatagridFilters if the query is being modified there.

3. Customize the Symfony Profiler (Advanced) #

If you want to embed the call stack directly into the Profiler’s Doctrine tab, you can override the default Doctrine data collector:

1. Create a custom collector that adds backtrace data to queries:

// src/AppBundle/DataCollector/CustomDoctrineDataCollector.php
namespace AppBundle\DataCollector;

use Symfony\Bridge\Doctrine\DataCollector\DoctrineDataCollector as BaseCollector;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;

class CustomDoctrineDataCollector extends BaseCollector
{
    public function collect(Request $request, Response $response, \Throwable $exception = null): void
    {
        parent::collect($request, $response, $exception);
        
        // Add backtrace to matching queries
        foreach ($this->data['queries'] as &$query) {
            if (strpos($query['sql'], 'your_target_table') !== false) {
                $query['backtrace'] = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 10);
            }
        }
    }
}

2. Replace the default collector in services.yml:

# app/config/services.yml
services:
    data_collector.doctrine:
        class: AppBundle\DataCollector\CustomDoctrineDataCollector
        arguments: ['@doctrine', '@doctrine.dbal.logger.profiling']
        tags:
            - { name: data_collector, template: '@App/Collector/doctrine.html.twig', id: 'doctrine' }

3. Create a custom Twig template to display the backtrace in the Profiler.

Important Notes #

• Always wrap debug code in an environment check (e.g., if ($kernel->getEnvironment() === 'dev')) to avoid deploying it to production.
• Once you find the source of the repeated queries, consider fixing it with Doctrine’s fetch-join or Sonata Admin’s addAssociation methods to avoid N+1 query issues.

内容的提问来源于stack exchange，提问作者StockBreak