将含子查询的大型MySQL查询转换为Doctrine DQL并优化性能

Alright, let's walk through converting your MySQL performance-optimized query to Doctrine DQL. I know dealing with subqueries in JOINs can feel tricky at first, but DQL handles this nicely once you map it to your entities properly.

First, let's assume you have properly mapped Doctrine entities for all your tables:

• App\Entity\Campaign (maps to campaigns table)
• App\Entity\User (maps to users table)
• App\Entity\Currency (maps to currencies table)
• App\Entity\CampaignBudget (maps to campaign_budgets table)
• App\Entity\Creative (maps to creatives table)

Full DQL Query Example #

Here's how your query translates to DQL, including the subquery in the JOIN:

SELECT 
    c.id, 
    c.name, 
    CONCAT(u.id, ' ', u.email) AS usersData, 
    CONCAT(c.cpm, ' ', cur.currency_code) AS cpm, 
    CONCAT(cb.total_spend, ' ', cur.currency_code) AS total_spend, 
    cr.impressionsCount, 
    cr.bidsCount, 
    cr.winsAmount, 
    cr.winsPercentage, 
    cr.creativeName -- Fill in the rest of your truncated creative fields here
FROM 
    App\Entity\Campaign c
JOIN 
    c.user u -- Assumes Campaign has a mapped association named `user` to User entity
JOIN 
    c.currency cur -- Assumes Campaign has a mapped association named `currency` to Currency entity
JOIN 
    c.campaignBudget cb -- Assumes Campaign has a mapped association named `campaignBudget` to CampaignBudget entity
JOIN 
    (
        SELECT 
            crSub.campaignId, 
            crSub.impressionsCount, 
            crSub.bidsCount, 
            crSub.winsAmount, 
            crSub.winsPercentage, 
            crSub.creativeName
        FROM 
            App\Entity\Creative crSub
        WHERE 
            -- Add your original subquery conditions here (e.g., crSub.isActive = TRUE)
    ) cr WITH cr.campaignId = c.id

Key Differences & Notes #

• Entity vs Table Names: DQL uses your Doctrine entity class names (with full namespace) instead of raw database table names. Aliases like c for Campaign keep things clean.
• Associations Over JOIN ON: For direct entity relationships (like Campaign → User), use the mapped association property (e.g., c.user) instead of writing a raw ON clause—Doctrine handles the join condition automatically based on your entity mapping.
• Subqueries in JOINs: When joining a subquery, use the WITH clause to define how the subquery result (cr) links back to the main entity (c).
• CONCAT Support: DQL natively supports CONCAT, so you can use it exactly like you did in MySQL.

Using QueryBuilder (More Flexible) #

If you prefer using Doctrine's QueryBuilder (common in Symfony apps), here's the equivalent code:

use Doctrine\ORM\EntityManagerInterface;
use App\Entity\Campaign;
use App\Entity\Creative;

public function fetchCampaignPerformance(EntityManagerInterface $em)
{
    // Build the subquery first
    $creativeSubquery = $em->createQueryBuilder()
        ->select([
            'crSub.campaignId',
            'crSub.impressionsCount',
            'crSub.bidsCount',
            'crSub.winsAmount',
            'crSub.winsPercentage',
            'crSub.creativeName'
        ])
        ->from(Creative::class, 'crSub')
        ->where('crSub.status = :status') // Example condition from your original query
        ->setParameter('status', 'active');

    // Build the main query
    $query = $em->createQueryBuilder()
        ->select([
            'c.id',
            'c.name',
            'CONCAT(u.id, \' \', u.email) AS usersData',
            'CONCAT(c.cpm, \' \', cur.currency_code) AS cpm',
            'CONCAT(cb.total_spend, \' \', cur.currency_code) AS total_spend',
            'cr.impressionsCount',
            'cr.bidsCount',
            'cr.winsAmount',
            'cr.winsPercentage',
            'cr.creativeName'
        ])
        ->from(Campaign::class, 'c')
        ->join('c.user', 'u')
        ->join('c.currency', 'cur')
        ->join('c.campaignBudget', 'cb')
        ->join(
            $creativeSubquery->getDQL(),
            'cr',
            'WITH',
            'cr.campaignId = c.id'
        )
        ->getQuery();

    return $query->getResult();
}

Final Checks #

1. Double-check that all entity associations are correctly mapped (e.g., Campaign has a $user property with @ManyToOne annotation pointing to User).
2. If your original query uses MySQL-specific functions not supported by DQL, you can create a custom DQL function to extend Doctrine's capabilities.
3. For performance, ensure your entity associations are set up with proper fetch modes (e.g., EAGER if needed, but prefer LAZY for most cases).

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