where example

    public function getShopsWithThemes($filter = null, $order = null, $offset = null, $limit = null)
    {
        $builder = $this->createQueryBuilder('shop');

        $builder->select(['shop', 'template'])
            ->innerJoin('shop.template', 'template')
            ->where('template.version >= 3')
            ->andWhere('shop.main IS NULL')
            ->andWhere('shop.active = 1');

        if ($filter) {
            $builder->addFilter($filter);
        }
        if ($order !== null) {
            $builder->addOrderBy($order);
        }
        if ($limit !== null) {
            $builder->setFirstResult($offset)
                

/** * {@inheritdoc} */
    public function fetchBackendListing(array $ids)
    {
        $streams = $this->fetchStreamsForCustomers($ids);

        /** @var \Doctrine\DBAL\Query\QueryBuilder $query */
        $query = $this->connection->createQueryBuilder();
        $query->select('*');
        $query->from('s_customer_search_index', 'search_index');
        $query->where('search_index.id IN (:ids)');
        $query->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

        $dataRows = $query->execute()->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE);

        $sorted = [];
        foreach ($ids as $id) {
            $data = $dataRows[$id];
            $data['id'] = $id;

            $data['interests'] = json_decode($data['interests'], true);

            

    private function getQuery(ShopContextInterface $context): QueryBuilder
    {
        $query = $this->connection->createQueryBuilder();

        $query->select($this->fieldHelper->getMediaFields());

        $query->from('s_media', 'media')
            ->innerJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')
            ->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')
            ->where('media.id IN (:ids)');

        $this->fieldHelper->addMediaTranslation($query, $context);

        return $query;
    }
}

$this->connection = $connection;
    }

    /** * {@inheritdoc} */
    public function getLastBacklogId()
    {
        $value = $this->connection->createQueryBuilder()
            ->select('value')
            ->from('s_core_config_elements', 'elements')
            ->where('elements.name = :name')
            ->setParameter(':name', 'lastBacklogId')
            ->setMaxResults(1)
            ->execute()
            ->fetch(PDO::FETCH_COLUMN);

        return (int) unserialize($value, ['allowed_classes' => false]);
    }

    /** * {@inheritdoc} */
    

$session = $this->get('session');

        // Count the views of this blog item         $visitedBlogItems = $session->get('visitedBlogItems', []);
        if (\in_array($blogArticleId, $visitedBlogItems, true)) {
            return;
        }
        // Use plain sql, because the http cache should no be cleared         $query = $connection->createQueryBuilder()
            ->update('s_blog', 'b')
            ->set('b.views', 'IFNULL(b.views, 0) + 1')
            ->where('b.id = :id')
            ->setParameter('id', $blogArticleId);
        $query->execute();

        $visitedBlogItems[] = $blogArticleId;
        $session->offsetSet('visitedBlogItems', $visitedBlogItems);
    }
}

return $data;
    }

    /** * @param int[] $ids */
    private function getCategories(array $ids): array
    {
        $query = $this->get(Connection::class)->createQueryBuilder();
        $query->select(['id', 'description'])
            ->from('s_categories', 'category')
            ->where('category.id IN (:ids)')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

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

    public function getVariantFacet()
    {
        if ($this->variantFacet !== false) {
            return $this->variantFacet;
        }

        $json = $this->connection->createQueryBuilder()
            ->addSelect('facet')
            ->from('s_search_custom_facet')
            ->where('unique_key = :key')
            ->andWhere('active = 1')
            ->setParameter('key', 'VariantFacet')
            ->execute()
            ->fetchColumn();

        if (empty($json)) {
            return $this->variantFacet = null;
        }

        /** @var array<class-string<VariantFacet>, array{label: string, depth: string}> $arr */
        $arr = json_decode($json, true);

        

    protected function _where(Zend_Db_Table_Select $select, $where)
    {
        $where = (array) $where;

        foreach ($where as $key => $val) {
            // is $key an int?             if (is_int($key)) {
                // $val is the full condition                 $select->where($val);
            } else {
                // $key is the condition with placeholder,                 // and $val is quoted into the condition                 $select->where($key, $val);
            }
        }

        return $select;
    }

    /** * Generate ORDER clause from user-supplied string or array * * @param string|array $order OPTIONAL An SQL ORDER clause. * @return Zend_Db_Table_Select */

$ids = [];
        foreach ($products as $product) {
            $ids[] = $product->getId();
        }
        $ids = array_unique($ids);

        $query = $this->connection->createQueryBuilder();

        $query->addSelect($this->fieldHelper->getVoteFields());

        $query->from('s_articles_vote', 'vote')
            ->where('vote.articleID IN (:ids)')
            ->andWhere('vote.active = 1')
            ->orderBy('vote.articleID', 'DESC')
            ->addOrderBy('vote.datum', 'DESC')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

        if ($this->config->get('displayOnlySubShopVotes')) {
            $query->andWhere('(vote.shop_id = :shopId OR vote.shop_id IS NULL)');
            $query->setParameter(':shopId', $context->getShop()->getId());
        }

        $data = $query->execute()->fetchAll(PDO::FETCH_ASSOC);

        

    protected function getDetailQuery($id)
    {
        $builder = $this->getManager()->createQueryBuilder();
        $builder->select([$this->alias])
            ->from($this->model, $this->alias)
            ->where($this->alias . '.id = :id')
            ->setParameter('id', $id);

        return $builder;
    }

    /** * Creates the query builder object for the {@link #searchAssociation} function. * It creates a simple query builder object which contains the selection to the associated * model and if the $search parameter contains a search value, the function creates an orWhere * condition for each model field with a like operation. * * @param string $association * @param class-string<ModelEntity> $model * @param string $search * * @return QueryBuilder */

        $query = $this->connection->createQueryBuilder();

        $query->select(['valid_from', 'valid_to'])
            ->from('s_emotion', 'emotion')
            ->innerJoin(
                'emotion',
                's_emotion_categories',
                'mapping',
                'mapping.emotion_id = emotion.id AND mapping.category_id = :resourceId'
            )
            ->where('emotion.active = 1')
            ->andWhere('(emotion.valid_from IS NOT NULL OR emotion.valid_to IS NOT NULL)');

        $query->setParameter(':resourceId', $resourceId);

        return $query;
    }

    /** * {@inheritdoc} */
    public function supportsRoute($route)
    {

    private function assignCategoryIds(array $products): array
    {
        $ids = array_column($products, 'articleId');

        $query = $this->entityManager->getConnection()->createQueryBuilder();
        $query->select(['articleID', 'GROUP_CONCAT(categoryID)']);
        $query->from('s_articles_categories_ro');
        $query->where('articleID IN (:ids)');
        $query->groupBy('articleID');
        $query->setParameter('ids', $ids, Connection::PARAM_INT_ARRAY);

        $categories = $query->execute()->fetchAll(PDO::FETCH_KEY_PAIR);

        foreach ($products as &$product) {
            $mapping = [];
            $id = $product['articleId'];
            if (\array_key_exists($id, $categories)) {
                $mapping = array_values(array_filter(explode(',', $categories[$id])));
            }
            

private function getTemplate(Theme $theme): ?Template
    {
        $builder = $this->entityManager->createQueryBuilder();
        $builder->select([
            'template',
            'elements',
            'layouts',
        ])
            ->from(Template::class, 'template')
            ->leftJoin('template.elements', 'elements')
            ->leftJoin('template.layouts', 'layouts')
            ->where('template.template = :name')
            ->setParameter('name', $theme->getTemplate());

        return $builder->getQuery()->getOneOrNullResult(
            AbstractQuery::HYDRATE_OBJECT
        );
    }

    /** * Returns all config containers of the passed template. * * @return ArrayCollection<int, Layout> */

        $query = $this->connection->createQueryBuilder();
        $query->select([
            'LOWER(hex(document.document_type_id)) as doc_type',
            'LOWER(hex(document.id)) as doc_id',
            'document.created_at as newest_date',
            'document.sent as sent',
        ]);
        $query->from('document', 'document');
        $query->innerJoin('document', 'document_type', 'document_type', 'document.document_type_id = document_type.id');
        $query->where('document.order_id = :orderId');

        if ($entity === 'order') {
            $query->setParameter('orderId', Uuid::fromHexToBytes($referencedId));
        } else {
            $fetchOrder = $this->connection->createQueryBuilder();
            $fetchOrder->select('order_id')->from($entity)->where('id = :id');

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

            $orderId = $fetchOrder->executeQuery()->fetchOne();

            

        }
    }

    /** * Removes a single history * * @param object $history */
    protected function removeHistory($history)
    {
        $this->db->table($this->table)->where('id', $history->id)->delete();

        if (is_cli()) {
            $this->cliMessages[] = sprintf(
                "\t%s(%s) %s_%s",
                CLI::color(lang('Migrations.removed'), 'yellow'),
                $history->namespace,
                $history->version,
                $history->class
            );
        }
    }