$qb = $conn->createQueryBuilder()
->select('u.name')
->from('users', 'u')
->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
/** * {@inheritdoc} */ public function apply($fromAlias, $fromIdentifier, $resourcePrefix, array $requesterIdentifiers, $mask, array $orX = []) { $this->queryBuilder->leftJoin($fromAlias, $this->getAclSchema()->getPermissionsTableName(), 'acl_p', 'acl_p.resource = ' . $this->connection->getDatabasePlatform()->getConcatExpression(':acl_prefix', $fromAlias . '.' . $fromIdentifier)); $orX[] = 'acl_p.requester IN (:acl_identifiers) AND :acl_mask = (acl_p.mask & :acl_mask)'; $this->queryBuilder->andWhere(implode(' OR ', $orX)); $this->queryBuilder->setParameter('acl_prefix', $resourcePrefix, \PDO::PARAM_STR)->setParameter('acl_identifiers', $requesterIdentifiers, Connection::PARAM_STR_ARRAY)->setParameter('acl_mask', $mask, \PDO::PARAM_INT); return $this->queryBuilder; }
/** * @test */ public function leftJoinQuotesIdentifiersAndDelegatesToConcreteQueryBuilder() { $this->connection->quoteIdentifier('fromAlias')->shouldBeCalled()->willReturnArgument(0); $this->connection->quoteIdentifier('join')->shouldBeCalled()->willReturnArgument(0); $this->connection->quoteIdentifier('alias')->shouldBeCalled()->willReturnArgument(0); $this->concreteQueryBuilder->leftJoin('fromAlias', 'join', 'alias', null)->shouldBeCalled()->willReturn($this->subject); $this->subject->leftJoin('fromAlias', 'join', 'alias'); }
/** * {@inheritdoc} */ public function buildQuery(QueryBuilder $queryBuilder, array $values = []) { if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) { return; } $certifications = $values[$this->getName()]->toArray(); $certifications = array_map(function (Certification $item) { return $item->getId(); }, $certifications); $queryBuilder->leftJoin('response', 'response_certification', 'response_certification', 'response.id = response_certification.response_id')->andWhere($queryBuilder->expr()->in('response_certification.certification_id', $certifications)); }
/** * {@inheritdoc} * @return NativeQueryBuilder */ public function rightJoin($fromAlias, $join, $alias, $condition = null) { if ($condition !== NULL) { list($condition) = array_values(Helpers::separateParameters($this, array_slice(func_get_args(), 3))); } return parent::leftJoin($fromAlias, $this->addTableResultMapping($join, $alias, $fromAlias), $alias, $condition); }
/** * @inheritdoc */ protected function addJoin(QueryBuilder $queryBuilder) { $queryBuilder->leftJoin('main', $this->getTableName(CategoryRepository::TABLE_NAME), 'c', 'main.category_id = c.id'); }
/** * Creates and adds a left join to the query. * * <code> * $qb = $conn->getQueryBuilder() * ->select('u.name') * ->from('users', 'u') * ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias The alias that points to a from clause. * @param string $join The table name to join. * @param string $alias The alias of the join table. * @param string $condition The condition for the join. * * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance. */ public function leftJoin($fromAlias, $join, $alias, $condition = null) { $this->queryBuilder->leftJoin($fromAlias, $this->getTableName($join), $alias, $condition); return $this; }
/** * Creates and adds a left join to the query. * * @param string $fromAlias The alias that points to a from clause. * @param string $join The table name to join. * @param string $alias The alias of the join table. * @param string $condition The condition for the join. * * @return QueryBuilder This QueryBuilder instance. */ public function leftJoin(string $fromAlias, string $join, string $alias, string $condition = null) : QueryBuilder { $this->concreteQueryBuilder->leftJoin($this->quoteIdentifier($fromAlias), $this->quoteIdentifier($join), $this->quoteIdentifier($alias), $condition); return $this; }
/** * Add IP left join * * @param QueryBuilder $queryBuilder * @param $prefix * @param string $ipPrefix */ public function addIpAddressLeftJoin(QueryBuilder &$queryBuilder, $prefix, $ipPrefix = 'i') { $queryBuilder->leftJoin($prefix, MAUTIC_TABLE_PREFIX . 'ip_addresses', $ipPrefix, 'i.id = ' . $prefix . '.ip_id'); }
/** * @param QueryBuilder $query * @param string $suffix */ private function addProductStreamTranslationWithSuffix(QueryBuilder $query, $suffix = '') { $selectSuffix = !empty($suffix) ? '_' . strtolower($suffix) : ''; $query->leftJoin('stream', 's_core_translations', 'streamTranslation' . $suffix, 'streamTranslation' . $suffix . '.objecttype = :streamType AND streamTranslation' . $suffix . '.objectkey = stream.id AND streamTranslation' . $suffix . '.objectlanguage = :language' . $suffix); $query->addSelect(['streamTranslation' . $suffix . '.objectdata as __stream_translation' . $selectSuffix])->setParameter(':streamType', 'productStream'); }
/** * Sort products for current category by passed sort type * * @param QueryBuilder $builder * @param integer $orderBy */ private function sortUnsortedByDefault($builder, $orderBy) { switch ($orderBy) { case 1: $builder->addOrderBy('product.datum', 'DESC')->addOrderBy('product.changetime', 'DESC'); break; case 2: $builder->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id')->addOrderBy('topSeller.sales', 'DESC')->addOrderBy('topSeller.article_id', 'DESC'); break; case 3: $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'ASC')->addOrderBy('product.id', 'DESC'); break; case 4: $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'DESC')->addOrderBy('product.id', 'DESC'); break; case 5: $builder->addOrderBy('product.name', 'ASC'); break; case 6: $builder->addOrderBy('product.name', 'DESC'); break; case 7: $builder->addSelect('(SUM(vote.points) / COUNT(vote.id)) as votes')->leftJoin('product', 's_articles_vote', 'vote', 'product.id = vote.articleID')->addOrderBy('votes', 'DESC')->addOrderBy('product.id', 'DESC')->groupBy('product.id'); break; case 9: $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'ASC')->addOrderBy('product.id', 'DESC'); break; case 10: $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'DESC')->addOrderBy('product.id', 'DESC'); break; } }
/** * Creates and adds a left join to the query. * * @param string $fromAlias The alias that points to a from clause. * @param string $join The table name to join. * @param string $alias The alias of the join table. * @param string $condition The condition for the join. * * @return self */ public function leftJoin($fromAlias, $join, $alias, $condition = null) { $this->qb->leftJoin($fromAlias, $join, $alias, $condition); return $this; }
/** * @inheritdoc */ protected function addJoin(QueryBuilder $queryBuilder) { $queryBuilder->leftJoin('main', $this->getTableName(ModulesRepository::TABLE_NAME), 'm', 'main.module_id = m.id'); }
/** * Creates and adds a left join to the query. * * <code> * $qb = $conn->getQueryBuilder() * ->select('u.name') * ->from('users', 'u') * ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); * </code> * * @param string $fromAlias The alias that points to a from clause. * @param string $join The table name to join. * @param string $alias The alias of the join table. * @param string $condition The condition for the join. * * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance. */ public function leftJoin($fromAlias, $join, $alias, $condition = null) { $this->queryBuilder->leftJoin($fromAlias, $this->helper->quoteColumnName($join), $alias, $condition); return $this; }
/** * Generate select field for a given table and column. * * @staticvar integer $index * * @param \Doctrine\DBAL\Query\QueryBuilder $qb * @param \Lokhman\Silex\ARM\Repository $repository * @param string $alias * @param string $column * * @return string */ private function select(QueryBuilder $qb, Repository $repository, $alias, $column) { if (!$this->translate || !$repository->getMetadata()->isTrans($column)) { return $alias . '.' . $column . ' ' . $repository->getTable() . self::UNIQUE . $column; } static $index = 0; $table = $repository->getTable(); $t = $this->app['arm.trans'] . $index++; $qb->leftJoin($alias, $this->app['arm.trans'], $t, $t . '._table = ' . $this->expr()->literal($table) . ' AND ' . $t . '._key = ' . $alias . '.' . $repository->getMetadata()->getPrimary() . ' AND ' . $t . '._column = ' . $this->expr()->literal($column) . ' AND ' . $t . '._locale = ' . $this->expr()->literal($this->locale)); return 'COALESCE(' . $t . '._content, ' . $alias . '.' . $column . ') ' . $table . self::UNIQUE . $column; }
/** * @inheritdoc */ protected function addJoin(QueryBuilder $queryBuilder) { $queryBuilder->leftJoin('main', $this->getTableName(PictureRepository::TABLE_NAME), 'p', 'main.id = p.gallery_id'); }