How to add subquery or hint in php symfony query builder without breaking query structure

Question

When the data is increased, a query that runs locally in 0.2 seconds takes 20 seconds on an AWS MySQL instance. The solution was to add a subquery or hint.

Sql origin.

SELECT DISTINCT m0_.id AS id_0, m0_.alias AS alias_1 
FROM marketed_name m0_ 
INNER JOIN product p1_ ON m0_.id = p1_.marketed_name_id AND (p1_.active = 1)
INNER JOIN attribute_value a2_
INNER JOIN product_value p4_ ON a2_.id = p4_.value_id 
INNER JOIN product p3_ ON p3_.id = p4_.product_id AND (p3_.id = p1_.id) 
INNER JOIN product p5_ 
INNER JOIN marketed_name m6_ ON p5_.marketed_name_id = m6_.id AND (m6_.alias = 'betametazon') 
INNER JOIN attribute_value a7_ 
INNER JOIN product_value p9_ ON a7_.id = p9_.value_id 
INNER JOIN product p8_ ON p8_.id = p9_.product_id AND (p8_.id = p5_.id) 
INNER JOIN attribute a10_ ON a7_.attribute_id = a10_.id AND (a10_.alias = 'atc') 
LEFT JOIN price p11_ ON p1_.id = p11_.product_id AND (p11_.current > 0) 
LEFT JOIN (product_image p12_ 
LEFT JOIN image i13_ ON p12_.id = i13_.id) ON p1_.id = p12_.product_id AND (p12_.main = 1) 
WHERE p1_.id <> p5_.id 
AND m0_.id <> p5_.marketed_name_id
AND a2_.attribute_id = a7_.attribute_id 
AND a2_.string_value = a7_.string_value 
GROUP BY m0_.id 
ORDER BY m0_.alias ASC LIMIT 30

Add hint

INNER JOIN product p1_ **FORCE INDEX FOR ORDER BY(IDX_34242323423423423)** ON m0_.id = p1_.marketed_name_id

Or subquery

SELECT DISTINCT m0_.id AS id_0, m0_.alias AS alias_1 
FROM (
    SELECT m0_.id, m0_.alias
    FROM marketed_name m0_
    INNER JOIN product p1_ ON m0_.id = p1_.marketed_name_id
    ORDER BY p1_.vendor_id ASC
) AS m0_
INNER JOIN product p1_ ON m0_.id = p1_.marketed_name_id
INNER JOIN attribute_value a2_
INNER JOIN product_value p4_ ON a2_.id = p4_.value_id 
INNER JOIN product p3_ ON p3_.id = p4_.product_id AND (p3_.id = p1_.id) 
INNER JOIN product p5_ 
INNER JOIN marketed_name m6_ ON p5_.marketed_name_id = m6_.id AND (m6_.alias = 'azitromicin')
INNER JOIN attribute_value a7_ 
INNER JOIN product_value p9_ ON a7_.id = p9_.value_id
INNER JOIN product p8_ ON p8_.id = p9_.product_id AND (p8_.id = p5_.id) 
INNER JOIN attribute a10_ ON a7_.attribute_id = a10_.id AND (a10_.alias = 'atc') 
LEFT JOIN price p11_ ON p1_.id = p11_.product_id AND (p11_.current > 0)
LEFT JOIN (product_image p12_ LEFT JOIN image i13_ ON p12_.id = i13_.id) ON p1_.id = p12_.product_id AND (p12_.main = 1) 
WHERE p1_.id <> p5_.id AND m0_.id <> p5_.marketed_name_id AND p1_.active = 1 AND a2_.attribute_id = a7_.attribute_id AND a2_.string_value = a7_.string_value GROUP BY m0_.id ORDER BY m0_.alias ASC LIMIT 30

I have a function called buildIndexAnalogs() that uses a QueryBuilder object to create a query that returns a list of analogs for a given trade name.

  public function buildIndexAnalogs(QueryBuilder $qb, string $marketedName): void
    {
        $qb
            ->addSelect('COUNT(DISTINCT p.id) productsCount')
            ->addSelect('MIN(pp.current) minPrice')
            ->addSelect('MAX(pp.current) maxPrice')
            ->addSelect('MAX(pp.updatedAt) maxDate')
            ->addSelect('ANY_VALUE(i.src) src')
            ->addSelect('ANY_VALUE(i.largeSrc) largeSrc')
            ->addSelect('ANY_VALUE(i.mediumSrc) mediumSrc')
            ->addSelect('ANY_VALUE(i.smallSrc) smallSrc')
            ->addSelect('ANY_VALUE(i.width) width')
            ->addSelect('ANY_VALUE(i.height) height')
            ->addSelect('ANY_VALUE(i.largeWidth) largeWidth')
            ->addSelect('ANY_VALUE(i.largeHeight) largeHeight')
            ->addSelect('ANY_VALUE(i.mediumWidth) mediumWidth')
            ->addSelect('ANY_VALUE(i.mediumHeight) mediumHeight')
            ->addSelect('ANY_VALUE(i.smallWidth) smallWidth')
            ->addSelect('ANY_VALUE(i.smallHeight) smallHeight')
            ->addSelect('ANY_VALUE(i.title) title')
            ->addSelect('ANY_VALUE(i.alt) alt')
            ->addSelect('ANY_VALUE(p.url) productUrl')
            ->join('o.products', 'p')
            ->join(Value::class, 'v')
            ->join('v.products', 'vp', Join::WITH, $qb->expr()->eq('vp.id', 'p.id'))
            ->join(Product::class, 'p0')
            ->join('p0.marketedName', 'mn0', Join::WITH, $qb->expr()->eq('mn0.alias', ':mnAlias'))
            ->join(Value::class, 'v0')
            ->join('v0.products', 'v0p', Join::WITH, $qb->expr()->eq('v0p.id', 'p0.id'))
            ->join('v0.attribute', 'v0a', Join::WITH, $qb->expr()->eq('v0a.alias', ':attribute'))
            ->leftJoin('p.price', 'pp', Join::WITH, $qb->expr()->gt('pp.current', 0))
            ->leftJoin('p.images', 'i', Join::WITH, $qb->expr()->eq('i.main', true))
            ->andWhere($qb->expr()->neq('p.id', 'p0.id'))
            ->andWhere($qb->expr()->neq('o.id', 'p0.marketedName'))
            ->andWhere($qb->expr()->eq('p.active', true))
            ->andWhere($qb->expr()->eq('v.attribute', 'v0.attribute'))
            ->andWhere($qb->expr()->eq('v.stringValue', 'v0.stringValue'))
            ->setParameter('attribute', 'atc')
            ->setParameter('mnAlias', $marketedName)
            ->groupBy('o.id');
    }

In the function parameters, I will already receive:

QueryBuilder $qb -

 SELECT m0_.id AS id_0, m0_.alias AS alias_1, m0_.url AS url_2, m0_.author AS author_3, m0_.censor AS censor_4, m0_.type AS type_5, m0_.brand AS brand_6, m0_.created_at AS created_at_7, m0_.updated_at AS updated_at_8 FROM marketed_name m0_

I tried to add to -from($subquery) but it doesn't work, an error. And ->setHint is only available in Query, not QueryBuilder

How do I add a $subquery or hint to this function and not break it, as after this function there is still processing as a parameter of QueryBuilder? The code was not written by me, but I need to solve it. Symfony 4.4

jpvdw · Accepted Answer · 2023-10-04 19:46:05Z

If you want to create a subquery with doctrine, you can apply the following. I've pulled this example from my archive, and you should, of course, adapt it to your code.

$lastSeenDql = $entityManager->createQueryBuilder()
    ->from(VehicleStandingDay::class, 'st')
    ->select('MAX(st.id) id')
    ->where('st.vehicle = vehicle');

$qb = $entityManager->createQueryBuilder();
$query = $qb
    ->select('pa')
    ->addSelect('vehicle_standing_day.days as days')
    ->from(PortalAdvertorial::class, 'pa')
    ->where('pa.company = :company')
    ->setParameter('company', $company)
    ->leftJoin('pa.vehicle', 'vehicle')
    ->join('vehicle.standingDays', 'vehicle_standing_day', Join::WITH, $qb->expr()->eq('vehicle_standing_day.id', '(' . $lastSeenDql->getDQL() . ')'))
    ->groupBy('vehicle.licenseplate')
    ->getQuery()
    ->getResult()

Collectives™ on Stack Overflow

How to add subquery or hint in php symfony query builder without breaking query structure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related