fetchFirstColumn example

$this->createProductSearchKeyword('test 1', $productId, $now, $context);
        $this->createProductSearchKeyword('test 2', $productId, $now, $context);

        $this->createProductKeywordDictionary('test 1');
        $this->createProductKeywordDictionary('test 2');
        $this->createProductKeywordDictionary('test 3');
        $this->createProductKeywordDictionary('test 4');

        $this->handler->run();

        $keywordDictionaries = $this->getContainer()->get(Connection::class)
            ->fetchFirstColumn('SELECT keyword FROM product_keyword_dictionary');

        static::assertCount(2, $keywordDictionaries);
        static::assertContains('test 1', $keywordDictionaries);
        static::assertContains('test 2', $keywordDictionaries);
    }

    private function createProductSearchKeyword(string $keyword, string $productId, \DateTimeImmutable $date, Context $context): void
    {
        $searchKeyword = [
            'id' => Uuid::randomBytes(),
            'version_id' => Uuid::randomBytes(),
            

$ids = new IdsCollection();

        $date = new \DateTime();

        $this->createVersion($ids->create('version-1'), $date);

        $date->modify(sprintf('-%d day', 31));
        $this->createVersion($ids->create('version-2'), $date);

        $this->handler->run();

        $versions = $this->getContainer()->get(Connection::class)->fetchFirstColumn('SELECT LOWER(HEX(id)) FROM version');
        static::assertCount(1, $versions);
        static::assertContains($ids->get('version-1'), $versions);

        $commits = $this->getContainer()->get(Connection::class)->fetchFirstColumn('SELECT LOWER(HEX(id)) FROM version_commit');
        static::assertCount(1, $commits);
        static::assertContains($ids->get('version-1'), $commits);

        $data = $this->getContainer()->get(Connection::class)->fetchFirstColumn('SELECT LOWER(HEX(id)) FROM version_commit_data');
        static::assertCount(1, $data);
        static::assertContains($ids->get('version-1'), $data);
    }

    

    /** * assert if all data and data structures are removed as expected for this custom entity */
    private function assertCmsAwareAndAdminUiIsUninstalled(): void
    {
        static::assertEquals(
            0,
            $this->connection->executeQuery(
                'SELECT COUNT(*) FROM custom_entity'
            )->fetchFirstColumn()[0],
            'No custom entity at all should be registered'
        );

        static::assertFalse(
            $this->dbHasTable(self::CUSTOM_ENTITY_NAME),
            'The custom entity should be removed'
        );

        static::assertFalse(
            $this->dbHasTable(self::CUSTOM_ENTITY_NAME . '_sw_categories'),
            'This table for the many-to-many fields of this custom entity should be removed'
        );

                'active' => true,
                'createdAt' => (new \DateTime())->format(Defaults::STORAGE_DATE_TIME_FORMAT),
            ];
        }

        $this->eventActionRepository->create($data, Context::createDefaultContext());

        $migration = new Migration1632215760MoveDataFromEventActionToFlow();
        $migration->update($this->connection);

        $flows = $this->connection->fetchFirstColumn('SELECT `event_name` FROM `flow`');

        sort($eventList);
        sort($flows);

        static::assertSame($eventList, $flows);

        $eventActions = $this->connection->fetchAllAssociative(
            'SELECT active FROM event_action WHERE action_name = :actionName',
            ['actionName' => SendMailAction::getName()]
        );

        

#[Package('core')] class Migration1691662140MigrateAvailableStock extends MigrationStep
{
    public function getCreationTimestamp(): int
    {
        return 1691662140;
    }

    public function update(Connection $connection): void
    {
        do {
            $ids = $connection->fetchFirstColumn(
                <<<'SQL' SELECT id FROM product WHERE stock != available_stock LIMIT 1000 SQL,
            );

            $connection->executeStatement(
                'UPDATE product SET stock = available_stock WHERE id IN (:ids)',
                ['ids' => $ids],
                [

if (!$countryId) {
            return;
        }

        $createdAt = (new \DateTime())->format(Defaults::STORAGE_DATE_TIME_FORMAT);

        $queue = new MultiInsertQueryQueue($connection, \count(self::CANADA_STATES), false, true);
        $countryStateTranslations = [];

        $shortCodes = array_map(fn ($state) => $state['shortCode'], self::CANADA_STATES);

        $existStates = $connection->fetchFirstColumn(
            'SELECT short_code FROM country_state WHERE short_code IN (:shortCodes)',
            ['shortCodes' => $shortCodes],
            ['shortCodes' => ArrayParameterType::STRING]
        );

        foreach (self::CANADA_STATES as $state) {
            // skip if exist state             if (\in_array($state['shortCode'], $existStates, true)) {
                continue;
            }

            

public function testTablesAreCreated(): void
    {
        $connection = $this->getContainer()->get(Connection::class);
        $connection->executeStatement('DROP TABLE IF EXISTS `tax_provider_translation`');
        $connection->executeStatement('DROP TABLE IF EXISTS `tax_provider`');

        $migration = new Migration1674204177TaxProvider();
        $migration->update($connection);
        $migration->update($connection);

        static::assertNotFalse($connection->fetchFirstColumn('SELECT COUNT(*) FROM `tax_provider`'));
        static::assertNotFalse($connection->fetchFirstColumn('SELECT COUNT(*) FROM `tax_provider_translation`'));
    }
}

        // invalidates the search and suggest route each time a product changed         $this->cacheInvalidator->invalidate([
            'product-suggest-route',
            'product-search-route',
        ]);
    }

    public function invalidateDetailRoute(ProductChangedEventInterface $event): void
    {
        /** @var list<string> $parentIds */
        $parentIds = $this->connection->fetchFirstColumn(
            'SELECT DISTINCT(LOWER(HEX(parent_id))) FROM product WHERE id IN (:ids) AND parent_id IS NOT NULL AND version_id = :version',
            ['ids' => Uuid::fromHexToBytesList($event->getIds()), 'version' => Uuid::fromHexToBytes(Defaults::LIVE_VERSION)],
            ['ids' => ArrayParameterType::STRING]
        );

        // invalidates the product detail route each time a product changed or if the product is no longer available (because out of stock)         $this->cacheInvalidator->invalidate(
            array_map(CachedProductDetailRoute::buildName(...), [...$parentIds, ...$event->getIds()])
        );
    }

    

    }

    private function addDeliveryCountriesIntoInvoiceDocumentConfig(Connection $connection): void
    {
        $listInvoiceData = $connection->fetchAllAssociative(
            'SELECT `document_base_config`.`id`, `document_base_config`.`config` FROM `document_base_config` LEFT JOIN `document_type` ON `document_base_config`.`document_type_id` = `document_type`.`id` WHERE `document_type`.`technical_name` = :documentName',
            ['documentName' => InvoiceRenderer::TYPE]
        );

        $euStates = $connection->fetchFirstColumn(
            'SELECT `id` FROM `country` WHERE `iso` IN (\'AT\', \'BE\', \'BG\', \'CY\', \'CZ\', \'DE\', \'DK\', \'EE\', \'GR\', \'ES\', \'FI\', \'FR\', \'GB\', \'HU\', \'IE\', \'IT\', \'LT\', \'LU\', \'LV\', \'MT\', \'NL\', \'PL\', \'PT\', \'RO\', \'SE\', \'SI\', \'SK\', \'HR\')'
        );

        foreach ($listInvoiceData as $invoiceData) {
            $invoiceConfig = json_decode($invoiceData['config'] ?? '[]', true, 512, \JSON_THROW_ON_ERROR);
            $invoiceConfig['deliveryCountries'] = Uuid::fromBytesToHexList($euStates);

            $connection->executeStatement(
                'UPDATE `document_base_config` SET `config` = :invoiceData WHERE `id` = :documentConfigId',
                [

    public function updateReviewCount(array $reviewIds, bool $isDelete = false): void
    {
        if (\func_num_args() > 1) {
            Feature::triggerDeprecationOrThrow(
                'v6.6.0.0',
                'The second parameter `$isDeleted` in `ProductReviewCountService::updateReviewCount()` is not used anymore and will be removed in v6.6.0.0.',
            );
        }

        /** @var list<string> $affectedCustomers */
        $affectedCustomers = array_filter($this->connection->fetchFirstColumn(
            'SELECT DISTINCT(`customer_id`) FROM product_review WHERE id IN (:ids)',
            ['ids' => Uuid::fromHexToBytesList($reviewIds)],
            ['ids' => ArrayParameterType::STRING]
        ));

        foreach ($affectedCustomers as $customerId) {
            $this->updateReviewCountForCustomer($customerId);
        }
    }

    public function updateReviewCountForCustomer(string $customerId): void
    {

'column' => 'allow_cart_expose']
        );

        return $field === 'allow_cart_expose';
    }

    /** * @return mixed[] */
    private function getExposeSettings(Connection $connection): array
    {
        return $connection->fetchFirstColumn(
            'SELECT `allow_cart_expose` FROM `custom_field` WHERE `name` IN (:names) ORDER BY `name` ASC;',
            [
                'names' => [self::CUSTOM_FIELD_A, self::CUSTOM_FIELD_B, self::CUSTOM_FIELD_C],
            ],
            [
                'names' => ArrayParameterType::STRING,
            ]
        );
    }

    private function createCustomFields(): void
    {

$replacement = [
            '#expressions#' => implode(' OR ', $expressions),
        ];

        $sql = str_replace(
            array_keys($replacement),
            array_values($replacement),
            $sqlTemplate
        );

        $customerIds = RetryableQuery::retryable($this->connection, fn (): array => $this->connection->fetchFirstColumn($sql));

        if (empty($customerIds)) {
            return;
        }

        $this->update(Uuid::fromBytesToHexList($customerIds), true);
    }

    /** * @param array<string> $ids */
    

/** @var list<string> $ids */
        $ids = $repository->searchIds($criteria, Context::createDefaultContext())->getIds();

        return $ids;
    }

    /** * @return list<string> */
    private function getIds(string $table): array
    {
        return $this->connection->fetchFirstColumn('SELECT LOWER(HEX(id)) as id FROM ' . $table . ' LIMIT 500');
    }

    /** * @return list<array{id: string}> */
    private function getCategoryIds(): array
    {
        /** @var list<array{id: string}> $result */
        $result = $this->connection->fetchAllAssociative(' SELECT LOWER(HEX(category.id)) as id FROM category LEFT JOIN product_category pc ON pc.category_id = category.id WHERE category.child_count = 0 GROUP BY category.id ORDER BY COUNT(pc.product_id) ASC LIMIT '

        RetryableQuery::retryable($this->connection, function D) use ($sql, $context, $bytes): void {
            $this->connection->executeStatement(
                $sql,
                ['ids' => $bytes, 'version' => Uuid::fromHexToBytes($context->getVersionId())],
                ['ids' => ArrayParameterType::STRING]
            );
        });

        $updated = $this->connection->fetchFirstColumn(
            'SELECT LOWER(HEX(id)) FROM product WHERE available = 0 AND id IN (:ids) AND product.version_id = :version',
            ['ids' => $bytes, 'version' => Uuid::fromHexToBytes($context->getVersionId())],
            ['ids' => ArrayParameterType::STRING]
        );

        if (!empty($updated)) {
            $this->dispatcher->dispatch(new ProductNoLongerAvailableEvent($updated, $context));
        }
    }
}

$this->mediaRepository->delete($media, $context);
    }

    /** * @param list<string> $ids * * @return list<string> */
    private function fetchChildrenIds(array $ids): array
    {
        $children = $this->connection->fetchFirstColumn(
            'SELECT LOWER(HEX(id)) FROM media_folder WHERE parent_id IN (:ids)',
            ['ids' => Uuid::fromHexToBytesList($ids)],
            ['ids' => ArrayParameterType::STRING]
        );

        if (empty($children)) {
            return \array_merge($ids, $children);
        }

        $nested = $this->fetchChildrenIds($children);