getSQL example

$query = $this->getTranslationQuery($definition, $translationDefinition, $context->getPath(), $context->getContext(), $translatedVersionFieldName);

        if ($rootVersionFieldName && $translatedVersionFieldName) {
            $variables['#rootVersionField#'] = $rootVersionFieldName;
            $variables['#translatedVersionField#'] = $translatedVersionFieldName;
            $versionJoin = ' AND #alias#.#translatedVersionField# = #on#.#rootVersionField#';
        }

        $context->getQuery()->leftJoin(
            EntityDefinitionQueryHelper::escape($context->getAlias()),
            '(' . $query->getSQL() . ')',
            EntityDefinitionQueryHelper::escape($alias),
            str_replace(
                array_keys($variables),
                array_values($variables),
                '#alias#.#foreignKey# = #on#.`id`' . $versionJoin
            )
        );

        foreach ($query->getParameters() as $key => $value) {
            $context->getQuery()->setParameter($key, $value);
        }

        

$mainQuery->select($this->fieldHelper->getPriceFields())
            ->addSelect($this->fieldHelper->getUnitFields())
            ->addSelect('variantCheapestPrice.ordernumber as __variant_ordernumber')
            ->addSelect('variantCheapestPrice.different_price_count as __different_price_count');

        $mainQuery->from('s_articles_prices', 'price')
            ->innerJoin('price', 's_articles_details', 'variant', 'variant.id = price.articledetailsID')
            ->innerJoin('variant', 's_articles', 'product', 'product.id = variant.articleID')
            ->leftJoin('variant', 's_core_units', 'unit', 'unit.id = variant.unitID')
            ->leftJoin('price', 's_articles_prices_attributes', 'priceAttribute', 'priceAttribute.priceID = price.id')
            ->innerJoin('price', '(' . $cheapestPriceQuery->getSQL() . ')', 'variantCheapestPrice', 'variantCheapestPrice.id = price.id');

        $this->fieldHelper->addUnitTranslation($mainQuery, $context);
        $this->fieldHelper->addProductTranslation($mainQuery, $context);
        $this->fieldHelper->addVariantTranslation($mainQuery, $context);
        $this->fieldHelper->addPriceTranslation($mainQuery, $context);

        $mainQuery->setParameter(':customerGroup', $context->getCurrentCustomerGroup()->getKey())
            ->setParameter(':fallbackCustomerGroup', $context->getFallbackCustomerGroup()->getKey())
            ->setParameter(':variants', $variantIds, Connection::PARAM_INT_ARRAY)
            ->setParameter(':priceGroupCustomerGroup', $customerGroup->getId());

        

            $subQuery->orderBy('prices.price');
        }

        /** * Creates an outer query which allows to * select multiple cheapest product prices. */
        $query = $this->connection->createQueryBuilder();
        $query->setParameter(':customerGroup', $customerGroup->getKey());

        $query->select('(' . $subQuery->getSQL() . ') as priceId')
            ->from('s_articles_prices', 'outerPrices')
            ->where('outerPrices.articleID IN (:products)')
            ->setParameter(':products', $ids, Connection::PARAM_INT_ARRAY)
            ->setParameter(':priceGroupCustomerGroup', $customerGroup->getId())
            ->groupBy('outerPrices.articleID')
            ->having('priceId IS NOT NULL');

        return $query->execute()->fetchAll(PDO::FETCH_COLUMN);
    }
}

        if ($criteria->getTotalCountMode() !== Criteria::TOTAL_COUNT_MODE_EXACT) {
            return \count($data);
        }

        $query->resetQueryPart('orderBy');
        $query->setMaxResults(null);
        $query->setFirstResult(0);

        $total = new QueryBuilder($this->connection);
        $total->select(['COUNT(*)'])
            ->from(sprintf('(%s) total', $query->getSQL()))
            ->setParameters($query->getParameters(), $query->getParameterTypes());

        return (int) $total->executeQuery()->fetchOne();
    }

    private function addGroupBy(EntityDefinition $definition, Criteria $criteria, Context $context, QueryBuilder $query, string $table): void
    {
        if ($criteria->getGroupFields()) {
            foreach ($criteria->getGroupFields() as $grouping) {
                $accessor = $this->queryHelper->getFieldAccessor($grouping->getField(), $definition, $definition->getEntityName(), $context);

                

if ($associationField instanceof ManyToManyAssociationField) {
            $mappingTable = $this->escape($associationField->getMappingDefinition()->getEntityName());
            $mappingLocalColumn = $this->escape($associationField->getMappingLocalColumn());
            $localColumn = $this->escape($associationField->getLocalField());

            $subQuery = (new QueryBuilder($this->connection))
                ->select('1')
                ->from($mappingTable)
                ->andWhere(sprintf('%s = `rule`.%s', $mappingLocalColumn, $localColumn));

            $query->addSelect(sprintf($template, $subQuery->getSQL(), $propertyName));

            return;
        }

        if ($associationField instanceof OneToManyAssociationField) {
            $referenceTable = $this->escape($associationField->getReferenceDefinition()->getEntityName());
            $referenceColumn = $this->escape($associationField->getReferenceField());
            $localColumn = $this->escape($associationField->getLocalField());

            $subQuery = (new QueryBuilder($this->connection))
                ->select('1')
                

$versionQuery = $this->createSubVersionQuery($field, $context, $this->queryHelper);

        $parameters = [
            '#source#' => $source,
            '#root#' => EntityDefinitionQueryHelper::escape($root),
            '#alias#' => EntityDefinitionQueryHelper::escape($alias),
            '#reference_column#' => $referenceColumn,
        ];

        $query->leftJoin(
            EntityDefinitionQueryHelper::escape($root),
            '(' . $versionQuery->getSQL() . ')',
            EntityDefinitionQueryHelper::escape($alias),
            str_replace(
                array_keys($parameters),
                array_values($parameters),
                '#source# = #alias#.#reference_column#',
            ),
        );

        foreach ($versionQuery->getParameters() as $key => $value) {
            $query->setParameter($key, $value, $query->getParameterType($key));
        }
    }

        foreach ($sortings as $i => $sorting) {
            $wrapper->addOrderBy(sprintf('sort_%s', $i), $sorting->getDirection());
        }

        $wrapper->from($root, $root);

        // wrap query into a sub select to restrict the association count from the outer query         $wrapper->leftJoin(
            $root,
            '(' . $query->getSQL() . ')',
            'child',
            'child.' . $foreignKey . ' = ' . $root . '.id AND id_count >= :offset AND id_count <= :limit'
        );

        // filter result to loaded root entities         $wrapper->andWhere($root . '.id IN (:rootIds)');

        $bytes = $collection->map(
            fn (Entity $entity) => Uuid::fromHexToBytes($entity->getUniqueIdentifier())
        );

        

return $query;
    }

    private function getTotal(QueryBuilder $query): int
    {
        $query->setMaxResults(null);
        $query->setFirstResult(0);

        $total = (new QueryBuilder($this->connection))
            ->select(['COUNT(*)'])
            ->from(sprintf('(%s) total', $query->getSQL()))
            ->setParameters($query->getParameters(), $query->getParameterTypes());

        return (int) $total->executeQuery()->fetchOne();
    }

    private function addEmptyFilter(QueryBuilder $query): void
    {
        /** @var ManyToManyAssociationField[] $manyToManyFields */
        $manyToManyFields = $this->tagDefinition->getFields()->filter(fn (Field $field) => $field instanceof ManyToManyAssociationField);

        foreach ($manyToManyFields as $manyToManyField) {
            

if (!empty($table['referenz_table'])) {
                $query->addSelect($alias . '.articleID as articleID');
                $query->innerJoin('si', $table['referenz_table'], $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']);
            } elseif (!empty($table['foreign_key'])) {
                $query->addSelect($alias . '.id as articleID');
                $query->innerJoin('si', 's_articles', $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']);
            } else {
                $query->addSelect('si.elementID as articleID');
            }

            $tablesSql[] = $query->getSQL();
        }

        $tablesSql = "\n" . implode("\n UNION ALL\n", $tablesSql);

        $subQuery = $this->connection->createQueryBuilder();
        $subQuery->select(['srd.articleID', 'SUM(srd.relevance) as relevance', 'COUNT(DISTINCT term) as termCount']);
        $subQuery->from('(' . $tablesSql . ')', 'srd')
            ->groupBy('srd.articleID')
            ->orderBy('relevance', 'DESC')
            ->setMaxResults(5000);

        

            // Remove remaining AND             $join = substr($join, 0, -4);

            foreach ($scoreQuery->getParameters() as $key => $value) {
                $query->setParameter($key, $value, $scoreQuery->getParameterType($key));
            }

            $query->join(
                EntityDefinitionQueryHelper::escape($table),
                '(' . $scoreQuery->getSQL() . ')',
                'score_table',
                $join
            );
        }

        foreach ($aggregation->getFields() as $fieldName) {
            $this->queryHelper->resolveAccessor($fieldName, $definition, $table, $query, $context, $aggregation);
        }

        $query->resetQueryPart('groupBy');

        

$priceTable = $this->listingPriceHelper->getPriceTable($context);

        if ($hasDifferentCustomerGroups) {
            $priceTable->andWhere('IFNULL(customerPrice.`pseudoprice`, defaultPrice.`pseudoprice`) > 0');
        } else {
            $priceTable->andWhere('defaultPrice.pseudoprice > 0');
        }

        $query->innerJoin(
            'allVariants',
            '(' . $priceTable->getSQL() . ')',
            'variantPrices',
            'variantPrices.articledetailsID = allVariants.id'
        );

        $query->setParameter(':fallbackCustomerGroup', $context->getFallbackCustomerGroup()->getKey());
        if ($hasDifferentCustomerGroups) {
            $query->setParameter(':currentCustomerGroup', $context->getCurrentCustomerGroup()->getKey());
        }
    }

    private function hasDifferentCustomerGroups(ShopContextInterface $context): bool
    {

$queryBuilder = $this->driverConnection->createQueryBuilder()
            ->insert($this->configuration['table_name'])
            ->values([
                'body' => '?',
                'headers' => '?',
                'queue_name' => '?',
                'created_at' => '?',
                'available_at' => '?',
            ]);

        $this->executeStatement($queryBuilder->getSQL(), [
            $body,
            json_encode($headers),
            $this->configuration['queue_name'],
            $now,
            $availableAt,
        ], [
            Types::STRING,
            Types::STRING,
            Types::STRING,
            Types::DATETIME_IMMUTABLE,
            Types::DATETIME_IMMUTABLE,
        ]);

        return $this->connection->createQueryBuilder()
            ->from('s_emotion', 'emo')
            ->join(
                'emo',
                's_emotion_categories',
                'emo_cat',
                'emo.id = emo_cat.emotion_id'
            )
            ->leftJoin('emo_cat', 's_categories', 'cat', 'emo_cat.category_id = cat.id')
            ->where('emo.active = 1')
            ->andWhere(sprintf('emo_cat.category_id IN (%s)', $this->prepareSubQuery()->getSQL()))
            ->andWhere('emo.valid_to > NOW() OR emo.valid_to IS NULL');
    }

    /** * @return QueryBuilder */
    private function prepareSubQuery()
    {
        return parent::getBaseQuery()
            ->addSelect(['cat.id']);
    }
}

        // Get mapped sql:         $mappedSql = str_replace(
            array_map(
                function D$param) {
                    return ':' . $param;
                },
                array_keys($qb->getParameters())
            ),
            array_values($qb->getParameters()),
            // Replace SELECT by SELECT STRAIGHT JOIN             substr_replace($qb->getSQL(), 'SELECT STRAIGHT_JOIN', 0, 6)
        );

        $collectToDelete = $this->connection->fetchAllAssociative($mappedSql);
        foreach ($collectToDelete as $delete) {
            $sql = ' DELETE FROM s_search_index WHERE keywordID=? AND fieldID=? ';
            $this->connection->executeStatement($sql, [$delete['keywordID'], $delete['fieldID']]);
        }
    }

    

public function setTitle(?string $title): void
    {
        $this->title = $title;
    }

    /** * @return string */
    public function getSQL()
    {
        $sql = parent::getSQL();

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

        return $sql;
    }
}