getDQL example

->where('c2.parentId = c.id')
            ->andWhere('c2.blog = 1');

        $builder = $this->createQueryBuilder('c');
        $builder->select([
            'c.id as id',
            'c.name as name',
            'c.position as position',
            'c.blog as blog',
        ]);
        $builder->having('childrenCount > 0 OR blog = 1');
        $builder->addSelect('(' . $subQuery->getDQL() . ') as childrenCount');

        return $builder;
    }

    /** * Helper function to select all path elements for the passed category. * * @param Category|null $category * @param string|array<string> $field * @param string|null $separator * * @return array|null */

        $builder = Shopware()->Models()->createQueryBuilder();
        $builder->from(Order::class, $alias)
                ->select(['SUM(' . $alias . '.invoiceAmountNet - ' . $alias . '.invoiceShippingNet)'])
                ->where('DATE_FORMAT(CURRENT_DATE(),\'%Y\') = DATE_FORMAT(' . $alias . '.orderTime,\'%Y\')')
                ->andWhere($alias . '.status NOT IN(\'4\', \'-1\')')
                ->andWhere($alias . '.partnerId = partner.idCode');
        if ($monthlyAmount) {
            $builder->andWhere('DATE_FORMAT(CURRENT_DATE(),\'%m\') = DATE_FORMAT(' . $alias . '.orderTime,\'%m\')');
        }

        return $builder->getDQL();
    }
}

$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')
                ->leftJoin('customer.orders', 'doneOrders', Join::WITH, 'doneOrders.status <> -1 AND doneOrders.status <> 4')
                ->where('customer.id = :customerId')