expr example

protected static function exprIf($condition, $expression1, $expression2)
    {
        return " if (($condition),($expression1),($expression2)) ";
    }

    private function addSorting(ProductNameSorting $sorting, QueryBuilder $query, ShopContextInterface $context): void
    {
        $query->leftJoin(
            self::PRODUCT,
            's_articles_translations',
            self::TRANSLATION,
            $query->expr()->andX(
                $query->expr()->eq(self::TRANSLATION . '.articleID', self::PRODUCT . '.id'),
                $query->expr()->eq(self::TRANSLATION . '.languageID', $context->getShop()->getId()),
                $query->expr()->isNotNull(self::TRANSLATION_NAME),
                $query->expr()->neq(self::TRANSLATION_NAME, $query->expr()->literal(''))
            )
        );

        $query->addOrderBy(
            self::exprIf(
                $query->expr()->isNull(self::TRANSLATION_NAME),
                self::PRODUCT . '.name',
                

    /** * {@inheritdoc} */
    public function completeOptionValues($optionName, CompletionContext $context)
    {
        if ($optionName === 'streamId') {
            $customerStreamRepository = $this->getContainer()->get(ModelManager::class)->getRepository(CustomerStream::class);
            $queryBuilder = $customerStreamRepository->createQueryBuilder('stream');

            if (is_numeric($context->getCurrentWord())) {
                $queryBuilder->andWhere($queryBuilder->expr()->like('stream.id', ':id'))
                    ->setParameter('id', addcslashes($context->getCurrentWord(), '%_') . '%');
            }

            $result = $queryBuilder->select(['stream.id'])
                ->getQuery()
                ->getArrayResult();

            return array_column($result, 'id');
        }

        return [];
    }

    public function clearRewriteCache()
    {
        $cache = (int) $this->config->offsetGet('routerCache');
        $cache = $cache < 360 ? 86400 : $cache;

        $builder = $this->db->createQueryBuilder();
        $builder
            ->from('s_core_config_values', 'v')
            ->join('v', 's_core_config_elements', 'e', 'v.element_id = e.id')
            ->select(['v.shop_id', 'v.value', 'e.id as element_id'])
            ->where($builder->expr()->like('e.name', $builder->expr()->literal('routerlastupdate')));

        $values = $builder->execute()->fetchAll(PDO::FETCH_ASSOC);
        $stmt = $this->db->prepare('UPDATE s_core_config_values SET value=? WHERE shop_id=? AND element_id=?');

        foreach ($values as $rawValue) {
            $value = unserialize($rawValue['value'], ['allowed_classes' => false]);
            $value = min(strtotime($value), time() - $cache);
            $value = date('Y-m-d H:i:s', $value);
            $value = serialize($value);
            $stmt->execute([$value, $rawValue['shop_id'], $rawValue['element_id']]);
        }
    }

    protected function configureQuery(QueryBuilder $builder, ?int $shopId, string $pluginName): QueryBuilder
    {
        return $builder
            ->innerJoin(
                'coreConfigValues',
                's_core_shops',
                'coreShops',
                'coreConfigValues.shop_id = coreShops.main_id'
            )
            ->andWhere($builder->expr()->eq('corePlugins.name', $builder->createNamedParameter($pluginName)))
            ->andWhere($builder->expr()->eq('coreShops.id', $builder->createNamedParameter($shopId)))
        ;
    }

    protected function isLayerResponsible(?int $shopId): bool
    {
        $queryBuilder = $this->connection->createQueryBuilder();

        return $queryBuilder->select('1')
                ->from('s_core_shops')
                ->andWhere($queryBuilder->expr()->eq('id', ':id'))
                

    public function completeOptionValues($optionName, CompletionContext $context)
    {
        if ($optionName === 'albumid') {
            $albumRepository = $this->getContainer()->get(ModelManager::class)->getRepository(Album::class);

            $queryBuilder = $albumRepository->createQueryBuilder('alb')
                ->innerJoin('alb.settings', 'settings', Join::WITH, 'settings.createThumbnails = 1');

            if (is_numeric($context->getCurrentWord())) {
                $queryBuilder->andWhere($queryBuilder->expr()->like('alb.id', ':id'))
                    ->setParameter('id', addcslashes($context->getCurrentWord(), '%_') . '%');
            }

            $result = $queryBuilder->select(['alb.id'])
                ->getQuery()
                ->getArrayResult();

            return array_column($result, 'id');
        }

        return [];
    }

return [];
    }

    /** * {@inheritdoc} */
    public function completeArgumentValues($argumentName, CompletionContext $context)
    {
        if ($argumentName === 'plugin') {
            $repository = $this->getContainer()->get(ModelManager::class)->getRepository(Plugin::class);
            $queryBuilder = $repository->createQueryBuilder('plugin');
            $result = $queryBuilder->andWhere($queryBuilder->expr()->eq('plugin.capabilityEnable', 'true'))
                ->select(['plugin.name'])
                ->getQuery()
                ->getArrayResult();

            return array_column($result, 'name');
        }

        if ($argumentName === 'key') {
            $pluginName = $context->getWordAtIndex($context->getWordIndex() - 1);
            $pluginManager = $this->container->get(InstallerService::class);
            try {
                

                    }

                    if ($where === null) {
                        $expression = 'IS NULL';
                    }

                    $cond = '`' . $item['property'] . '` ' . $expression;

                    if ($where) {
                        if ($expression === 'IN') {
                            $cond = $query->expr()->in('`' . $item['property'] . '`', $where);
                        } else {
                            $cond .= ' ' . $query->createNamedParameter($where);
                        }
                    }

                    if (isset($item['operator'])) {
                        $query->orWhere($cond);
                    } else {
                        $query->andWhere($cond);
                    }
                }
            }

'description' => 'articles.description',
            'active' => 'articles.active',
            'changeTime' => 'articles.changed',
            'number' => 'detail.number',
            'detailId' => 'detail.id as detailId',
            'inStock' => 'detail.inStock',
            'supplierName' => 'supplier.name as supplierName',
            'supplierId' => 'supplier.id as supplierId',
        ];
        $builder->select($fields);

        $builder->addSelect($builder->expr()->count('details.id') . ' as countDetails');

        $builder->innerJoin('articles.mainDetail', 'detail');
        $builder->innerJoin('articles.supplier', 'supplier');

        $builder->leftJoin('articles.details', 'details');
        $builder->groupBy('articles.id');

        // Don't search for normal articles         $displayProducts = (bool) $this->Request()->getParam('articles', true);
        if (!$displayProducts) {
            $builder->andWhere('articles.configuratorSetId IS NOT NULL');
        }

/** * {@inheritdoc} */
    public function completeArgumentValues($argumentName, CompletionContext $context)
    {
        if ($argumentName === 'shopId') {
            $shopRepository = $this->getContainer()->get(ModelManager::class)->getRepository(ShopModel::class);
            $queryBuilder = $shopRepository->createQueryBuilder('shop');

            if (is_numeric($context->getCurrentWord())) {
                $queryBuilder->andWhere($queryBuilder->expr()->like('shop.id', ':id'))
                    ->setParameter('id', addcslashes($context->getCurrentWord(), '%_') . '%');
            }

            $result = $queryBuilder->select(['shop.id'])
                ->addOrderBy($queryBuilder->expr()->asc('shop.id'))
                ->getQuery()
                ->getArrayResult();

            return array_column($result, 'id');
        }

        

/** @var ManyToManyAssociationField[] $manyToManyFields */
        $manyToManyFields = $this->tagDefinition->getFields()->filter(fn (Field $field) => $field instanceof ManyToManyAssociationField);

        foreach ($manyToManyFields as $manyToManyField) {
            $mappingTable = EntityDefinitionQueryHelper::escape($manyToManyField->getMappingDefinition()->getEntityName());
            $mappingLocalColumn = EntityDefinitionQueryHelper::escape($manyToManyField->getMappingLocalColumn());

            $subQuery = (new QueryBuilder($this->connection))
                ->select([$mappingLocalColumn])
                ->from($mappingTable);

            $query->andWhere($query->expr()->notIn('`tag`.`id`', sprintf('(%s)', $subQuery->getSQL())));
        }
    }

    private function addDuplicateFilter(QueryBuilder $query): void
    {
        $subQuery = (new QueryBuilder($this->connection))
            ->select(['name'])
            ->from('tag')
            ->groupBy('name')
            ->having('COUNT(`name`) > 1');

        

$result
        );

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

        // Delete         $builder = $entityManager->createQueryBuilder();
        $query = $entityManager->createQueryBuilder()
                ->delete('\Shopware\Models\MultiEdit\QueueArticle', 'q')
                ->where($builder->expr()->in('q.detailId', $result))
                ->getQuery();
        $query->execute();

        return $result;
    }

    /** * Create queue for a given filter array. If an queueId is passed, the existing queue will be used * * @param array $filterArray * @param array $operations * @param int $offset * @param int $limit * @param int $queueId * * @throws RuntimeException * * @return array{totalCount: int, offset: int, queueId: int, done: bool} */

$builder = $this->getEntityManager()->createQueryBuilder();
        $builder->select([
            'customer',
            'IDENTITY(customer.defaultBillingAddress) as default_billing_address_id',
            'IDENTITY(customer.defaultShippingAddress) as default_shipping_address_id',
            'billing',
            'shipping',
            'paymentData',
            'locale.language',
            'shop.name as shopName',
            $builder->expr()->count('doneOrders.id') . ' as orderCount',
            'SUM(doneOrders.invoiceAmount) as amount',
            '(' . $subQueryBuilder->getDQL() . ') as canceledOrderAmount',
        ]);
        // Join s_orders second time to display the count of canceled orders and the count and total amount of done orders         $builder->from($this->getEntityName(), 'customer')
                ->leftJoin('customer.defaultBillingAddress', 'billing')
                ->leftJoin('customer.defaultShippingAddress', 'shipping')
                ->leftJoin('customer.shop', 'shop')
                ->leftJoin('customer.languageSubShop', 'subShop')
                ->leftJoin('subShop.locale', 'locale')
                ->leftJoin('customer.paymentData', 'paymentData', Join::WITH, 'paymentData.paymentMean = customer.paymentId')
                

return null;
    }

    private function hasBoundAccount(string $email): bool
    {
        $query = $this->connection->createQueryBuilder();

        /** @var array{email: string, guest: int, bound_sales_channel_id: string|null}[] $results */
        $results = $query
            ->select('LOWER(HEX(bound_sales_channel_id)) as bound_sales_channel_id')
            ->from('customer')
            ->where($query->expr()->eq('email', $query->createPositionalParameter($email)))
            ->executeQuery()
            ->fetchAllAssociative();

        foreach ($results as $result) {
            if ($result['bound_sales_channel_id']) {
                return true;
            }
        }

        return false;
    }

    

    private function getExclusionIds(string $id): array
    {
        if (!Uuid::isValid($id)) {
            return [];
        }

        $query = $this->connection->createQueryBuilder();
        $query->select('ifnull(exclusion_ids,JSON_ARRAY()) as exclusion_ids');
        $query->from(PromotionDefinition::ENTITY_NAME);
        $query->andWhere($query->expr()->eq('id', ':id'));

        $query->setParameter('id', Uuid::fromHexToBytes($id));

        return $query->executeQuery()->fetchAllAssociative();
    }

    /** * helper function to convert hex array values to a binary array * * @param array<string> $hexIds */
    

// Get prefix from first entry         list($prefix, $column) = explode('.', $operations[0]['column']);

        $entity = $this->getDqlHelper()->getEntityForPrefix($prefix);

        $builder = $this->getDqlHelper()->getEntityManager()->createQueryBuilder()
            ->update($entity, $prefix);

        $columnInfo = $this->getDqlHelper()->getColumnsForProductListing();

        $ids = $this->getDqlHelper()->getIdForForeignEntity($prefix, $detailIds);
        $builder->where($builder->expr()->in($prefix . '.id', $ids));

        foreach ($operations as $operation) {
            list($prefix, $column) = explode('.', $operation['column']);

            $type = $columnInfo[ucfirst($prefix) . ucfirst($column)]['type'];
            if ($operation['value'] && \in_array($type, ['decimal', 'integer', 'float'], true)) {
                $operation['value'] = str_replace(',', '.', $operation['value']);
            }

            // In set mode: If column is nullable and value is "" - set it to null             if ($operation['operator'] === 'set' && $columnInfo[ucfirst($prefix) . ucfirst($column)]['nullable'] && $operation['value'] == '') {