prepareSubQuery example

        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']);
    }
}

    protected function getBaseQuery()
    {
        return $this->connection->createQueryBuilder()
            ->from('s_blog', 'blog')
            ->join(
                'blog',
                's_categories',
                'cat',
                sprintf('blog.category_id = cat.id AND blog.category_id IN (%s)', $this->prepareSubQuery()->getSQL())
            )
            ->where('blog.active = 1');
    }

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

/** * {@inheritdoc} */
    public function getUrls(Context $context, $limit = null, $offset = null)
    {
        $qb = $this->getBaseQuery()
            ->addSelect(['details.articleID', 'details.ordernumber', 'details.kind', "GROUP_CONCAT( 'group[', opt.group_id, ']=', opt_rel.option_id ORDER BY opt.group_id ASC SEPARATOR '&') AS 'link'"])
            ->where(sprintf('article.id IN (%s)', $this->prepareSubQuery()->getSQL()))
            ->groupBy('details.ordernumber')
            ->orderBy('details.ordernumber', 'ASC')
            ->setParameter(':shop', $context->getShopId());

        if ($limit !== null && $offset !== null) {
            $qb->setFirstResult($offset)
                ->setMaxResults($limit);
        }

        $resultArray = $qb->execute()->fetchAll();