innerJoin example

        if (\is_int($category)) {
            $category = $this->find($category);
        }
        if (!$category instanceof Category) {
            return null;
        }

        $builder = $this->getEntityManager()->createQueryBuilder();
        $builder->from($this->getEntityName(), 'c');
        $builder->select('MIN(a.id)')
            ->innerJoin('c.allArticles', 'a', Join::WITH, 'a.active=1')
            ->where('c.active=1')
            ->andWhere('c.id = :id');

        $builder->setParameter('id', $category->getId());

        return $builder->getQuery()->getResult(Query::HYDRATE_SINGLE_SCALAR);
    }

    /** * Returns the \Doctrine\ORM\Query to select all blog categories for example for the blog backend list * * @param int $parentId * @param int|null $offset * @param int|null $limit * * @internal param $filterBy * * @return Query<Category> */

private function getQuery(ShopContextInterface $context)
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->addSelect($this->fieldHelper->getPropertyGroupFields())
            ->addSelect($this->fieldHelper->getPropertyOptionFields())
            ->addSelect($this->fieldHelper->getMediaFields())
        ;

        $query->from('s_filter_options', 'propertyGroup')
            ->leftJoin('propertyGroup', 's_filter_options_attributes', 'propertyGroupAttribute', 'propertyGroupAttribute.optionID = propertyGroup.id')
            ->innerJoin('propertyGroup', 's_filter_values', 'propertyOption', 'propertyOption.optionID = propertyGroup.id')
            ->leftJoin('propertyOption', 's_filter_values_attributes', 'propertyOptionAttribute', 'propertyOptionAttribute.valueID = propertyOption.id')
            ->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id')
            ->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')
            ->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')
        ;

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

        $query->where('propertyGroup.id = :id');

        

$ids = [];
        foreach ($cartItems as $cartItem) {
            $ids[] = $cartItem->getId();
        }
        $defaultPriceGroup = 'EK';

        $queryBuilder = $this->connection->createQueryBuilder()
            ->select('order_cart.id, product_price.pricegroup, product_price.price, product.taxID, tax.tax, order_cart.tax_rate, product_detail.id AS articleDetailsID, product_detail.articleID, order_cart.config, order_cart.ordernumber, product_price.from, product_price.to')
            ->from('s_order_basket', 'order_cart')
            ->innerJoin('order_cart', 's_articles_details', 'product_detail', 'order_cart.ordernumber = product_detail.ordernumber')
            ->innerJoin('product_detail', 's_articles', 'product', 'product.id = product_detail.articleID')
            ->innerJoin('product_detail', 's_articles_prices', 'product_price', 'product_detail.id = product_price.articledetailsID')
            ->innerJoin('product', 's_core_tax', 'tax', 'product.taxID = tax.id')
            ->where('order_cart.id IN (:ids) AND order_cart.sessionID = :sessionId')
            ->andWhere('product_price.pricegroup = :pricegroup OR product_price.pricegroup = :defaultPriceGroup')
            ->setParameter('ids', $ids, Connection::PARAM_INT_ARRAY)
            ->setParameter('sessionId', $this->session->get('sessionId'))
            ->setParameter('pricegroup', $this->sSYSTEM->sUSERGROUP)
            ->setParameter('defaultPriceGroup', $defaultPriceGroup);

        $this->eventManager->notify(
            

    private function getManufacturersForSitemap(ShopContextInterface $shopContext): array
    {
        $categoryId = $shopContext->getShop()->getCategory()->getId();

        return $this->connection->createQueryBuilder()
            ->select(['manufacturer.id', 'manufacturer.name', 'manufacturer.changed'])
            ->from('s_articles_supplier', 'manufacturer')
            ->innerJoin('manufacturer', 's_articles', 'product', 'product.supplierID = manufacturer.id')
            ->innerJoin('product', 's_articles_categories_ro', 'categories', 'categories.articleID = product.id AND categories.categoryID = :categoryId')
            ->setParameter(':categoryId', $categoryId)
            ->groupBy('manufacturer.id')
            ->execute()
            ->fetchAll(PDO::FETCH_ASSOC);
    }
}

  /** * Tests that sessions are only saved when necessary. */
  public function testSessionWrite() {
    $user = $this->drupalCreateUser([]);
    $this->drupalLogin($user);
    $connection = Database::getConnection();

    $query = $connection->select('users_field_data', 'u');
    $query->innerJoin('sessions', 's', '[u].[uid] = [s].[uid]');
    $query->fields('u', ['access'])
      ->fields('s', ['timestamp'])
      ->condition('u.uid', $user->id());
    $times1 = $query->execute()->fetchObject();

    // Before every request we sleep one second to make sure that if the session     // is saved, its timestamp will change.
    // Modify the session.     sleep(1);
    $this->drupalGet('session-test/set/foo');
    

    $query = parent::query();
    $query->addField('td', 'language', 'td.language');

    // Add in the property, which is either name or description.     // Cast td.tid as char for PostgreSQL compatibility.     $query->leftJoin('i18n_strings', 'i18n', 'CAST([td].[tid] AS CHAR(255)) = [i18n].[objectid]');
    $query->condition('i18n.type', 'term');
    $query->addField('i18n', 'lid');
    $query->addField('i18n', 'property');

    // Add in the translation for the property.     $query->innerJoin('locales_target', 'lt', '[i18n].[lid] = [lt].[lid]');
    $query->addField('lt', 'language', 'lt.language');
    $query->addField('lt', 'translation');
    return $query;
  }

  /** * {@inheritdoc} */
  public function prepareRow(Row $row) {
    $language = $row->getSourceProperty('ltlanguage');
    $row->setSourceProperty('language', $language);
    

  public function query() {
    // Get translations for field labels and descriptions.     $query = $this->select('i18n_strings', 'i18n')
      ->fields('i18n', ['property', 'objectid', 'type'])
      ->fields('lt', ['lid', 'translation', 'language'])
      ->condition('i18n.type', 'field');
    $condition = $query->orConditionGroup()
      ->condition('property', 'widget_label')
      ->condition('property', 'widget_description');
    $query->condition($condition);
    $query->innerJoin('locales_target', 'lt', '[lt].[lid] = [i18n].[lid]');

    return $query;
  }

  /** * {@inheritdoc} */
  public function fields() {
    return [
      'property' => $this->t('Profile field ID.'),
      'lid' => $this->t('Locales target language ID.'),
      

class ProfileFieldTranslation extends ProfileField {

  /** * {@inheritdoc} */
  public function query() {
    $query = parent::query();
    $query->fields('i18n', ['property'])
      ->fields('lt', ['lid', 'translation', 'language']);
    $query->leftJoin('i18n_strings', 'i18n', '[i18n].[objectid] = [pf].[name]');
    $query->innerJoin('locales_target', 'lt', '[lt].[lid] = [i18n].[lid]');
    return $query;
  }

  /** * {@inheritdoc} */
  public function fields() {
    $fields = [
      'lid' => $this->t('Locales target language ID.'),
      'language' => $this->t('Language for this field.'),
      'translation' => $this->t('Translation of either the title or explanation.'),
    ];

    $query = parent::query();
    $query->addField('td', 'language', 'td.language');

    // Add in the property, which is either name or description.     // Cast td.tid as char for PostgreSQL compatibility.     $query->leftJoin('i18n_string', 'i18n', 'CAST([td].[tid] AS CHAR(255)) = [i18n].[objectid]');
    $query->condition('i18n.type', 'term');
    $query->addField('i18n', 'lid');
    $query->addField('i18n', 'property');

    // Add in the translation for the property.     $query->innerJoin('locales_target', 'lt', '[i18n].[lid] = [lt].[lid]');
    $query->addField('lt', 'language', 'lt.language');
    $query->addField('lt', 'translation');
    return $query;
  }

  /** * {@inheritdoc} */
  public function prepareRow(Row $row) {
    if (!parent::prepareRow($row)) {
      return FALSE;
    }

        $streams = $this->connection
            ->createQueryBuilder()
            ->select(['id', 'name'])
            ->from('s_customer_streams')
            ->execute()->fetchAll(PDO::FETCH_KEY_PAIR);

        $date = (new DateTime())->sub(new DateInterval('P' . (int) 12 . 'M'));

        $query = $this->createAmountPerMonthQuery($date);
        $query->addSelect('stream_mapping.stream_id as stream');
        $query->innerJoin('orders', 's_customer_streams_mapping', 'stream_mapping', 'stream_mapping.customer_id = orders.userID');
        $query->addGroupBy('stream_mapping.stream_id');
        $streamAmount = $query->execute()->fetchAll(PDO::FETCH_GROUP);

        $query = $this->createAmountPerMonthQuery($date);
        $query->andWhere('orders.userID NOT IN (SELECT DISTINCT customer_id FROM s_customer_streams_mapping)');
        $amount = $query->execute()->fetchAll(PDO::FETCH_KEY_PAIR);

        $default = ['unassigned' => 0];
        foreach (array_keys($streams) as $id) {
            $default['stream_' . $id] = 0;
        }

        

// Get the fields that have field options translations.     $query = $this->select('i18n_strings', 'i18n')
      ->fields('i18n')
      ->fields('lt', [
        'translation',
        'language',
        'plid',
        'plural',
      ])
      ->condition('i18n.type', 'field')
      ->condition('property', 'option\_%', 'LIKE');
    $query->innerJoin('locales_target', 'lt', '[lt].[lid] = [i18n].[lid]');
    $query->leftjoin('content_node_field', 'cnf', '[cnf].[field_name] = [i18n].[objectid]');
    $query->addField('cnf', 'field_name');
    $query->addField('cnf', 'global_settings');
    // Minimise changes to the d6_field_option_translation.yml, which is copied     // from d6_field.yml, by ensuring the 'type' property is from     // content_node_field table.     $query->addField('cnf', 'type');
    $query->addField('i18n', 'type', 'i18n_type');

    // The i18n_string module adds a status column to locale_target. It was     // originally 'status' in a later revision it was named 'i18n_status'.

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

            $query->from('(SELECT 1)', 'root');

            $fields = [];
            $params = ['parentId' => $parentId, 'versionId' => $versionBytes];
            foreach ($groups as $groupId) {
                $mappingAlias = 'mapping' . $groupId;
                $optionAlias = 'option' . $groupId;

                $query->innerJoin('root', 'product_option', $mappingAlias, $mappingAlias . '.product_id IS NOT NULL');
                $query->innerJoin($mappingAlias, 'property_group_option', $optionAlias, $optionAlias . '.id = ' . $mappingAlias . '.property_group_option_id AND ' . $optionAlias . '.property_group_id = :' . $optionAlias);
                $query->andWhere($mappingAlias . '.product_id = product.id');

                $fields[] = 'LOWER(HEX(' . $optionAlias . '.id))';

                $params[$optionAlias] = Uuid::fromHexToBytes($groupId);
            }

            $query->addSelect('CONCAT(' . implode(',', $fields) . ')');

            $sql = ' UPDATE product SET display_group = MD5( CONCAT( LOWER(HEX(product.parent_id)), ('

protected function createListQuery()
    {
        $query = $this->entityManager->createQueryBuilder();
        $query->select([
            'entity.id',
            'entity.orderNumberExport',
            'variant.number',
            'article.name',
            's.name as shop',
        ]);
        $query->from(Premium::class, 'entity', $this->getIdentifierField());
        $query->innerJoin('entity.articleDetail', 'variant');
        $query->innerJoin('variant.article', 'article');
        $query->leftJoin('entity.shop', 's');

        return $query;
    }
}

class PropertyOptionSearcher extends GenericSearcher
{
    /** * {@inheritdoc} */
    protected function createQuery(SearchCriteria $criteria)
    {
        $query = $this->entityManager->createQueryBuilder();
        $query->select($this->getIdentifierField());
        $query->from(Value::class, 'entity');
        $query->innerJoin('entity.option', 'option');

        return $query;
    }

    /** * @return array */
    protected function getSearchFields(SearchCriteria $criteria)
    {
        return [
            'entity.value',
            

$blogIds[] = (int) $blogCategory['id'];
        }

        if (\count($blogIds) === 0) {
            return [];
        }

        $blogs = $this->modelManager->getConnection()->createQueryBuilder()
            ->addSelect('blog.id, blog.category_id, DATE(blog.display_date) as changed')
            ->from('s_blog', 'blog')
            ->where('blog.active = 1')
            ->innerJoin('blog', 's_categories', 'cat', 'cat.id = blog.category_id')
            ->andWhere('category_id IN (:ids)')
            ->andWhere('cat.shops IS NULL OR cat.shops LIKE :shopLike')
            ->andWhere('blog.shop_ids IS NULL OR blog.shop_ids LIKE :shopLike')
            ->setParameter(':shopLike', '%|' . $shopId . '|%')
            ->setParameter('ids', $blogIds, Connection::PARAM_INT_ARRAY)
            ->execute()
            ->fetchAllAssociative();

        $blogIds = array_column($blogs, 'id');
        $blogTranslations = $this->fetchTranslations($blogIds, $shopContext);