leftJoin example

/** * @param int|null $userId * @param string $paymentName * * @return QueryBuilder */
    public function getCurrentPaymentDataQueryBuilder($userId, $paymentName)
    {
        $builder = $this->getEntityManager()->createQueryBuilder();
        $builder->select(['paymentdata']);
        $builder->from(PaymentData::class, 'paymentdata')
            ->leftJoin('paymentdata.paymentMean', 'paymentmean')
            ->leftJoin('paymentdata.customer', 'customer')
            ->where('customer.id = :userId')
            ->andWhere('paymentmean.name = :paymentName')
            ->andWhere('paymentmean.active = 1')
            ->setParameter('userId', $userId)
            ->setParameter('paymentName', $paymentName);

        return $builder;
    }
}

/** * {@inheritdoc} */
    public function getList(array $ids, ShopContextInterface $context)
    {
        $query = $this->connection->createQueryBuilder();

        $query->addSelect('page.id as arrayKey');
        $query->addSelect($this->fieldHelper->getShopPageFields());

        $query->from('s_cms_static', 'page')
            ->leftJoin('page', 's_cms_static_attributes', 'pageAttribute', 'pageAttribute.cmsStaticID = page.id')
            ->where('page.id IN (:ids)')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

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

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

        return array_map([$this->shopPageHydrator, 'hydrate'], $data);
    }
}

$this->fieldHelper->addConfiguratorGroupTranslation($query, $context);
        $this->fieldHelper->addConfiguratorOptionTranslation($query, $context);

        $query->from('s_articles', 'product');
        $query->innerJoin('product', 's_articles_details', 'variant', 'variant.articleID = product.id');
        $query->innerJoin('product', 's_article_configurator_sets', 'configuratorSet', 'configuratorSet.id = product.configurator_set_id');
        $query->innerJoin('configuratorSet', 's_article_configurator_set_group_relations', 'groupRelation', 'groupRelation.set_id = configuratorSet.id');
        $query->innerJoin('configuratorSet', 's_article_configurator_set_option_relations', 'optionRelation', 'optionRelation.set_id = configuratorSet.id');
        $query->innerJoin('groupRelation', 's_article_configurator_groups', 'configuratorGroup', 'configuratorGroup.id = groupRelation.group_id');
        $query->innerJoin('optionRelation', 's_article_configurator_options', 'configuratorOption', 'configuratorOption.id = optionRelation.option_id AND configuratorOption.group_id = configuratorGroup.id');
        $query->leftJoin('configuratorGroup', 's_article_configurator_groups_attributes', 'configuratorGroupAttribute', 'configuratorGroupAttribute.groupID = configuratorGroup.id');
        $query->leftJoin('configuratorOption', 's_article_configurator_options_attributes', 'configuratorOptionAttribute', 'configuratorOptionAttribute.optionID = configuratorOption.id');

        $query->addOrderBy('configuratorGroup.position');
        $query->addOrderBy('configuratorGroup.name');
        $query->addOrderBy('configuratorOption.position');
        $query->addOrderBy('configuratorOption.name');

        $query->addGroupBy('product.id');
        $query->addGroupBy('configuratorOption.id');

        $query->where('variant.articleId IN (:articleIds)');
        

public function generateSorting(
        SortingInterface $sorting,
        QueryBuilder $query,
        ShopContextInterface $context
    ) {
        $this->addSorting($sorting, $query);
    }

    private function addSorting(PopularitySorting $sorting, QueryBuilder $query): void
    {
        if (!$query->hasState(SalesConditionHandler::STATE_INCLUDES_TOPSELLER_TABLE)) {
            $query->leftJoin(
                'product',
                's_articles_top_seller_ro',
                'topSeller',
                'topSeller.article_id = product.id'
            );
            $query->addState(SalesConditionHandler::STATE_INCLUDES_TOPSELLER_TABLE);
        }

        $query->addOrderBy('topSeller.sales', $sorting->getDirection());
    }
}

$iterator = $this->iteratorFactory->createIterator($this->definition, $lastId);
        $query = $iterator->getQuery();
        $query->setMaxResults($limit);

        $showAfterCloseout = !$this->systemConfigService->get(self::CONFIG_HIDE_AFTER_CLOSEOUT, $context->getSalesChannelId());

        $query->addSelect([
            '`product`.created_at as created_at',
            '`product`.updated_at as updated_at',
        ]);

        $query->leftJoin('`product`', '`product`', 'parent', '`product`.parent_id = parent.id');
        $query->innerJoin('`product`', 'product_visibility', 'visibilities', 'product.visibilities = visibilities.product_id');

        $query->andWhere('`product`.version_id = :versionId');

        if ($showAfterCloseout) {
            $query->andWhere('(`product`.available = 1 OR `product`.is_closeout)');
        } else {
            $query->andWhere('`product`.available = 1');
        }

        $query->andWhere('IFNULL(`product`.active, parent.active) = 1');
        

->addSelect($this->fieldHelper->getPropertyGroupFields())
            ->addSelect($this->fieldHelper->getPropertyOptionFields())
            ->addSelect($this->fieldHelper->getMediaFields())
        ;

        $query->from('s_filter_articles', 'filterArticles')
            ->innerJoin('filterArticles', 's_articles', 'products', 'products.id = filterArticles.articleID')
            ->innerJoin('filterArticles', 's_filter_values', 'propertyOption', 'propertyOption.id = filterArticles.valueID')
            ->innerJoin('products', 's_filter', 'propertySet', 'propertySet.id = products.filtergroupID')
            ->innerJoin('propertySet', 's_filter_relations', 'relations', 'relations.groupID = propertySet.id')
            ->innerJoin('propertyOption', 's_filter_options', 'propertyGroup', 'propertyGroup.id = propertyOption.optionID AND relations.optionID = propertyGroup.id')
            ->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id')
            ->leftJoin('propertySet', 's_filter_attributes', 'propertySetAttribute', 'propertySetAttribute.filterID = propertySet.id')
            ->leftJoin('propertyGroup', 's_filter_options_attributes', 'propertyGroupAttribute', 'propertyGroupAttribute.optionID = propertyGroup.id')
            ->leftJoin('propertyOption', 's_filter_values_attributes', 'propertyOptionAttribute', 'propertyOptionAttribute.valueID = propertyOption.id')
            ->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')
            ->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')
            ->where('products.id IN (:ids)')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY)
            ->orderBy('filterArticles.articleID');

        $this->fieldHelper->addPropertySetTranslation($query, $context);
        $this->fieldHelper->addPropertyGroupTranslation($query, $context);
        

return $conditions;
    }

    /** * {@inheritdoc} */
    protected function getListQuery(): QueryBuilder
    {
        $builder = parent::getListQuery();

        $builder->leftJoin('mailLog.order', self::JOIN_ALIAS_ORDER)
            ->leftJoin('mailLog.recipients', self::JOIN_ALIAS_RECIPIENTS)
            ->addSelect([self::JOIN_ALIAS_ORDER, self::JOIN_ALIAS_RECIPIENTS]);

        return $builder;
    }

    /** * {@inheritdoc} */
    protected function getDetailQuery($id): QueryBuilder
    {
        

    public function getCountries(array $ids, ShopContextInterface $context)
    {
        if (empty($ids)) {
            return [];
        }

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

        $query->select($this->fieldHelper->getCountryFields());
        $query->from('s_core_countries', 'country')
            ->leftJoin('country', 's_core_countries_attributes', 'countryAttribute', 'countryAttribute.countryID = country.id')
            ->where('country.id IN (:ids)')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

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

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

        $countries = [];
        foreach ($data as $row) {
            $country = $this->countryHydrator->hydrateCountry($row);
            $countries[$country->getId()] = $country;
        }

$builder = $this->modelManager->createQueryBuilder();

        return $builder->select([
                'emotion',
                'elements',
                'viewports',
                'component',
                'data',
                'fields',
            ])
            ->from(Emotion::class, 'emotion')
            ->leftJoin('emotion.elements', 'elements')
            ->leftJoin('elements.viewports', 'viewports')
            ->leftJoin('elements.component', 'component')
            ->leftJoin('component.fields', 'fields')
            ->leftJoin('elements.data', 'data')
            ->where('emotion.id = :emotionId')
            ->orderBy('elements.id')
            ->setParameter('emotionId', $emotionId)
            ->getQuery()
            ->getSingleResult(Query::HYDRATE_ARRAY);
    }

    

    public function getListQueryBuilder($filter = null, $orderBy = null)
    {
        /** @var QueryBuilder $builder */
        $builder = $this->getEntityManager()->createQueryBuilder();
        $builder->select(['emotions', 'categories'])
            ->from(Emotion::class, 'emotions')
            ->leftJoin('emotions.categories', 'categories');

        // Filter the displayed columns with the passed filter string         if (!empty($filter)) {
            $builder->where('categories.name LIKE ?2')
                ->orWhere('emotions.name LIKE ?2')
                ->orWhere('emotions.modified LIKE ?2')
                ->setParameter(2, '%' . $filter . '%');
        }
        if (!empty($orderBy)) {
            $builder->addOrderBy($orderBy);
        }

        

if ($condition->expandVariants()) {
                $this->joinVariantCondition($query, $condition);

                $tableKey = $condition->getName();
                $variantCondition[] = 'listing_price.' . $tableKey . '_id = ' . $tableKey . '.option_id';
            }
        }

        $priceTable = $this->createListingPriceTable($criteria, $context);

        $query->addSelect('listing_price.*');
        $query->leftJoin('variant', '(' . $priceTable->getSQL() . ')', 'listing_price', implode(' AND ', $variantCondition));

        $query->andWhere('variant.laststock * variant.instock >= variant.laststock * variant.minpurchase');

        $query->andWhere('variant.active = 1');

        $query->setParameter(':fallbackCustomerGroup', $context->getFallbackCustomerGroup()->getKey());
        $query->setParameter(':priceGroupCustomerGroup', $context->getCurrentCustomerGroup()->getId());

        if ($this->hasDifferentCustomerGroups($context)) {
            $query->setParameter(':currentCustomerGroup', $context->getCurrentCustomerGroup()->getKey());
        }

        

    public function getProductAmountPerManufacturer($offset = null, $limit = null, ?DateTimeInterface $from = null, ?DateTimeInterface $to = null)
    {
        $builder = $this->createProductAmountBuilder($from, $to)
            ->addSelect('suppliers.name')
            ->leftJoin('articles', 's_articles_supplier', 'suppliers', 'articles.supplierID = suppliers.id')
            ->groupBy('articles.supplierID')
            ->orderBy('turnover', 'DESC');

        $this->addPagination($builder, (int) $offset, $limit);

        $builder = $this->eventManager->filter('Shopware_Analytics_ProductAmountPerManufacturer', $builder, [
            'subject' => $this,
        ]);

        return new Result($builder);
    }

    

    }

    private function addTranslations(QueryBuilder $query, ShopContextInterface $context): void
    {
        if ($context->getShop()->isDefault()) {
            return;
        }

        $query
            ->addSelect('attributeTranslations.objectdata as __attribute_translation')
            ->leftJoin(
                'product',
                's_core_translations',
                'attributeTranslations',
                'attributeTranslations.objectkey = product.id AND attributeTranslations.objecttype = "article" AND attributeTranslations.objectlanguage = :language'
            )
        ;
        $query->setParameter(':language', $context->getShop()->getId());

        if (!$context->getShop()->getFallbackId() || $context->getShop()->getFallbackId() === $context->getShop()->getId()) {
            return;
        }

        

    private function fetchMapping(array $ids, Context $context): array
    {
        $query = $this->connection->createQueryBuilder();
        $query->select([
            'LOWER(HEX(product.id)) as product_id',
            'GROUP_CONCAT(category.path SEPARATOR \'\') as paths',
            'GROUP_CONCAT(LOWER(HEX(category.id)) SEPARATOR \'|\') as ids',
        ]);
        $query->from('product');
        $query->leftJoin(
            'product',
            'product_category',
            'mapping',
            'mapping.product_id = product.categories AND mapping.product_version_id = product.version_id'
        );
        $query->leftJoin(
            'mapping',
            'category',
            'category',
            'mapping.category_id = category.id AND mapping.category_version_id = category.version_id AND mapping.category_version_id = :live'
        );

        

->fetchColumn();
    }

    /** * @return QueryBuilder */
    protected function getBaseQuery()
    {
        return $this->connection->createQueryBuilder()
            ->from('s_articles_categories_ro', 'art_cat')
            ->join('art_cat', 's_categories', 'cat', 'art_cat.categoryID = cat.id')
            ->leftJoin('art_cat', 's_categories', 'parent_cat', 'art_cat.categoryID = parent_cat.id')
            ->join('art_cat', 's_articles_details', 'details', 'art_cat.articleID = details.articleID')
            ->join('parent_cat', 's_core_shops', 'shop', 'cat.path LIKE CONCAT("%|", shop.category_id, "|%")')
            ->where('shop.id = :shop')
            ->andWhere('cat.active = 1')
            ->andWhere('parent_cat.active = 1')
            ->andWhere('details.active = 1');
    }
}