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