767 lines
24 KiB
PHP
767 lines
24 KiB
PHP
<?php
|
|
|
|
namespace Query;
|
|
|
|
use Doctrine\Bundle\DoctrineBundle\Twig\DoctrineExtension;
|
|
use Doctrine\DBAL\Connection;
|
|
use KupShop\KupShopBundle\Context\CountryContext;
|
|
use KupShop\KupShopBundle\Context\VatContext;
|
|
use KupShop\KupShopBundle\Util\Contexts;
|
|
use KupShop\PricelistBundle\Context\PricelistContext;
|
|
|
|
class QueryBuilderBase extends \Doctrine\DBAL\Query\QueryBuilder
|
|
{
|
|
private $joins = [];
|
|
|
|
protected $calcRows = false;
|
|
|
|
protected $onDuplicateKeyUpdate = [];
|
|
|
|
protected $sendToMaster = false;
|
|
|
|
protected $forUpdate = false;
|
|
|
|
protected bool $multiInsert = false;
|
|
|
|
protected array $forceIndexes = [];
|
|
|
|
protected array $withExpressions = [];
|
|
|
|
/**
|
|
* @param mixed|null $selects
|
|
*
|
|
* @return $this
|
|
*/
|
|
public function select($selects = null)
|
|
{
|
|
$selects = func_get_args();
|
|
|
|
return call_user_func_array([parent::class, __FUNCTION__], $this->evaluateClosures($selects));
|
|
}
|
|
|
|
public function from($from, $alias = null)
|
|
{
|
|
$from = $this->evaluateClosures([$from])[0];
|
|
|
|
return call_user_func_array([parent::class, __FUNCTION__], [$from, $alias]);
|
|
}
|
|
|
|
public function addSelect($selects = null)
|
|
{
|
|
$selects = func_get_args();
|
|
|
|
return call_user_func_array([parent::class, __FUNCTION__], $this->evaluateClosures($selects));
|
|
}
|
|
|
|
public function set($key, $value)
|
|
{
|
|
[$key, $value] = $this->evaluateClosures([$key, $value]);
|
|
|
|
return call_user_func_array([parent::class, __FUNCTION__], [$key, $value]);
|
|
}
|
|
|
|
/**
|
|
* @return $this
|
|
*/
|
|
public function where($predicates)
|
|
{
|
|
$predicates = func_get_args();
|
|
|
|
if (isDevelopment()) {
|
|
if ($this->getQueryPart('where') !== null) {
|
|
/* @noinspection PhpUnhandledExceptionInspection */
|
|
throw new \Exception('This call of \'where\' method will reset where query part! Use \'andWhere\' instead.');
|
|
}
|
|
}
|
|
|
|
return call_user_func_array([parent::class, __FUNCTION__], $this->evaluateClosures($predicates));
|
|
}
|
|
|
|
/**
|
|
* @return $this
|
|
*/
|
|
public function andWhere($where)
|
|
{
|
|
$where = func_get_args();
|
|
|
|
$where = $this->evaluateClosures($where);
|
|
|
|
// Remove null wheres - caused by specs
|
|
$where = array_filter($where, function ($x) {
|
|
return $x !== null;
|
|
});
|
|
|
|
if (!count($where)) {
|
|
return $this;
|
|
}
|
|
|
|
return call_user_func_array([parent::class, __FUNCTION__], $where);
|
|
}
|
|
|
|
/**
|
|
* @return $this
|
|
*/
|
|
public function orWhere($where)
|
|
{
|
|
$where = func_get_args();
|
|
|
|
return call_user_func_array([parent::class, __FUNCTION__], $this->evaluateClosures($where));
|
|
}
|
|
|
|
/**
|
|
* @param string $productsAlias
|
|
*
|
|
* @return \Query\QueryBuilder
|
|
*/
|
|
public function fromProducts($productsAlias = 'p')
|
|
{
|
|
return $this->from('products', $productsAlias);
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @internal
|
|
*/
|
|
public function evaluateClosures(array $predicates)
|
|
{
|
|
foreach ($predicates as &$predicate) {
|
|
while ($predicate instanceof \Closure) {
|
|
$predicate = $predicate($this);
|
|
}
|
|
}
|
|
|
|
return $predicates;
|
|
}
|
|
|
|
public function add($sqlPartName, $sqlPart, $append = false)
|
|
{
|
|
if ($sqlPartName === 'join') {
|
|
return $this->addJoin(current($sqlPart)['joinAlias'], $sqlPart, $append);
|
|
}
|
|
|
|
if ($sqlPartName === 'select' && $append) {
|
|
$selects = $this->getQueryPart('select');
|
|
foreach ($sqlPart as $part) {
|
|
if (in_array($part, $selects)) {
|
|
return $this;
|
|
}
|
|
}
|
|
}
|
|
|
|
return parent::add($sqlPartName, $sqlPart, $append);
|
|
}
|
|
|
|
public function addParameters(array $parameters, array $types = [])
|
|
{
|
|
foreach ($parameters as $name => $value) {
|
|
$this->setParameter($name, $value, $types[$name] ?? null);
|
|
}
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function orderBySql($orderBySql)
|
|
{
|
|
return $this->add('orderBy', $orderBySql);
|
|
}
|
|
|
|
public function addSubselect(QueryBuilder $qb, $alias, $template = '{}')
|
|
{
|
|
$fields = str_replace('{}', "({$qb->getSQL()})", $template);
|
|
|
|
return $this->addSelect("{$fields} AS {$alias}")
|
|
->addParameters($qb->getParameters(), $qb->getParameterTypes());
|
|
}
|
|
|
|
public function joinSubQuery($fromAlias, QueryBuilder $qb, $alias, $condition)
|
|
{
|
|
return $this->join($fromAlias, '('.$qb->getSQL().')', $alias, $condition)
|
|
->addParameters($qb->getParameters(), $qb->getParameterTypes());
|
|
}
|
|
|
|
public function leftJoinSubQuery($fromAlias, QueryBuilder $qb, $alias, $condition)
|
|
{
|
|
return $this->leftJoin($fromAlias, '('.$qb->getSQL().')', $alias, $condition)
|
|
->addParameters($qb->getParameters(), $qb->getParameterTypes());
|
|
}
|
|
|
|
public function crossJoin(string $fromAlias, string $table, string $alias): self
|
|
{
|
|
return $this->add('join', [
|
|
$fromAlias => [
|
|
'joinType' => 'cross',
|
|
'joinTable' => $table,
|
|
'joinAlias' => $alias,
|
|
'joinCondition' => null,
|
|
],
|
|
], true);
|
|
}
|
|
|
|
private function addJoin($joinAlias, $joinDeclaration, $append)
|
|
{
|
|
$normalizedJoin = mb_strtolower(preg_replace('/\s/', '', implode(current($joinDeclaration))));
|
|
if ($this->isJoinPresent($joinAlias, $normalizedJoin)) {
|
|
return $this;
|
|
}
|
|
|
|
$this->joins[$joinAlias] = $normalizedJoin;
|
|
|
|
return parent::add('join', $joinDeclaration, $append);
|
|
}
|
|
|
|
private function isJoinPresent($joinAlias, $normalizedJoin)
|
|
{
|
|
if (isset($this->joins[$joinAlias])) {
|
|
if ($this->joins[$joinAlias] !== $normalizedJoin) {
|
|
$existingNormalizedJoin = $this->joins[$joinAlias];
|
|
throw new \LogicException("Alias '{$joinAlias}' has already been set with the declaration '{$existingNormalizedJoin}'. Now trying: '{$normalizedJoin}'");
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
public function isAliasPresent($alias)
|
|
{
|
|
$fromAlias = $this->getQueryPart('from')[0]['alias'] ?? $this->getQueryPart('from')['alias'] ?? '';
|
|
|
|
return ($fromAlias == $alias) || isset($this->joins[$alias]);
|
|
}
|
|
|
|
public function execute()
|
|
{
|
|
wpj_debug([$this->getSQL(), $this->getParameters()]);
|
|
|
|
return parent::execute();
|
|
}
|
|
|
|
public function setUnbufferedMode(): QueryBuilder
|
|
{
|
|
$this->getConnection()->getWrappedConnection()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinVatsOnProducts()
|
|
{
|
|
$vatContext = Contexts::get(VatContext::class);
|
|
|
|
if ($vatContext->isCountryOssActive()) {
|
|
$countryContext = Contexts::get(CountryContext::class);
|
|
$defaultCn = \Settings::getDefault()['oss_vats']['default'] ?? null;
|
|
$this->leftJoin('p', 'vats_cns', 'vatcns', 'COALESCE(p.id_cn, :defaultCn) = vatcns.id_cn')
|
|
->leftJoin('vatcns', 'vats', 'v', 'vatcns.id_vat = v.id')
|
|
->andWhere(
|
|
Operator::orX(
|
|
Operator::equals(['v.id_country ' => $countryContext->getActiveId()]),
|
|
'vatcns.id_cn IS NULL'
|
|
)
|
|
)
|
|
->setParameter('defaultCn', $defaultCn);
|
|
} else {
|
|
$this->leftJoin('p', 'vats', 'v', 'p.vat = v.id');
|
|
}
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinVariationsOnProducts()
|
|
{
|
|
$this->leftJoin('p', 'products_variations', 'pv', 'p.id = pv.id_product');
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinSectionsOnProducts()
|
|
{
|
|
$this->join('p', 'products_in_sections', 'ps', 'p.id = ps.id_product');
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinProducersOnProducts()
|
|
{
|
|
$this->leftJoin('p', 'producers', 'pr', 'p.producer = pr.id');
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinTemplatesOnProducts()
|
|
{
|
|
$this->leftJoin('p', 'templates_products', 'tp', 'p.id = tp.id_product');
|
|
$this->join('tp', 'templates', 't', 't.id = tp.id_template');
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinVariationsLabelsAndValues()
|
|
{
|
|
$this->join('pv', 'products_variations_combination', 'pvc', 'pv.id = pvc.id_variation')
|
|
->join('pvc', 'products_variations_choices_labels', 'pvcl', 'pvc.id_label=pvcl.id')
|
|
->join('pvc', 'products_variations_choices_values', 'pvcv', 'pvc.id_value=pvcv.id')
|
|
->join('pvc', 'products_variations_choices_categorization', 'pvcc', 'pvc.id_label=pvcc.id_label and pv.id_product=pvcc.id_product')
|
|
->orderBy('pvcc.list_order')
|
|
->addOrderBy('pvcv.sort');
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinParametersOnProducts($parametersAlias = 'pap')
|
|
{
|
|
$this->join('p', 'parameters_products', $parametersAlias, "p.id = {$parametersAlias}.id_product");
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinProductsOfSuppliers(string $productAlias = 'p')
|
|
{
|
|
// if the spaceship operator causes problems, return it to ((pv.id=pos.id_variation) OR (pv.id IS NULL and pos.id_variation IS NULL))
|
|
$this->leftJoin($productAlias, 'products_of_suppliers', 'pos', "{$productAlias}.id = pos.id_product AND pv.id <=> pos.id_variation");
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function joinPriceListsProducts(?int $priceListId = null, string $productAlias = 'p', string $variationAlias = 'pv'): self
|
|
{
|
|
$priceListId ??= Contexts::get(PricelistContext::class)->getActiveId();
|
|
|
|
if ($priceListId) {
|
|
$priceListIds = \KupShop\PricelistBundle\Query\Product::getActivePriceLists((int) $priceListId);
|
|
$this->setParameter('id_pricelist', $priceListIds, Connection::PARAM_INT_ARRAY)
|
|
->leftJoin('p', 'pricelists_products', 'prlp', "prlp.id_product = {$productAlias}.id AND prlp.id_variation IS null AND prlp.id_pricelist IN (:id_pricelist)")
|
|
->leftJoin('p', 'pricelists_products', 'prlv', "prlv.id_product = {$productAlias}.id AND prlv.id_variation = {$variationAlias}.id AND prlv.id_pricelist IN (:id_pricelist)")
|
|
->leftJoin('prlv', 'pricelists', 'prl', 'prlp.id_pricelist = prl.id')
|
|
->leftJoin('prlv', 'pricelists', 'prl_v', 'prlv.id_pricelist = prl_v.id');
|
|
|
|
if (count($priceListIds) <= 1) {
|
|
$this->setForceIndexForJoin('prlp', 'pricelist_product')
|
|
->setForceIndexForJoin('prlv', 'pricelist_product');
|
|
}
|
|
}
|
|
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* @return QueryBuilder
|
|
* Specifies values for an multi insert query indexed by column names.
|
|
* Replaces any previous values, if any.
|
|
*
|
|
* <code>
|
|
* $array =[
|
|
* ['name' => '?', 'password' => '?'],
|
|
* ['name' => '?', 'password' => '?']
|
|
* ];
|
|
* </code>
|
|
*/
|
|
public function multiValues(array $values): QueryBuilder
|
|
{
|
|
if ($this->getType() !== self::INSERT) {
|
|
throw new \InvalidArgumentException('multiValues without INSERT');
|
|
}
|
|
$this->setMultiInsert(true);
|
|
|
|
return $this->values($values);
|
|
}
|
|
|
|
/**
|
|
* @return QueryBuilder
|
|
* Specifies values for a multi insert query indexed by column names.
|
|
* One row insert per call
|
|
* <code>
|
|
* $values =['name' => '?', 'password' => '?']
|
|
* </code>
|
|
*/
|
|
public function multiDirectValues(array $values, array $types = []): QueryBuilder
|
|
{
|
|
$sqlParts = $this->getQueryParts();
|
|
if ($this->getType() !== self::INSERT) {
|
|
throw new \InvalidArgumentException('multiValues without INSERT');
|
|
}
|
|
if (!$this->getMultiInsert()) {
|
|
$this->multiValues([]); // vyčistíme values
|
|
}
|
|
$pf = 'multi'.count($sqlParts['values']).'_';
|
|
$array = [];
|
|
foreach ($values as $param => $value) {
|
|
$key = ":{$pf}{$param}";
|
|
$this->setParameter($key, $value, isset($types[$param]) ? $types[$param] : null);
|
|
$array['`'.$param.'`'] = $key;
|
|
}
|
|
$sqlParts['values'][] = $array;
|
|
|
|
return $this->multiValues($sqlParts['values']);
|
|
}
|
|
|
|
protected function setMultiInsert(bool $multiInsert)
|
|
{
|
|
$this->multiInsert = $multiInsert;
|
|
}
|
|
|
|
protected function getMultiInsert(): bool
|
|
{
|
|
return $this->multiInsert;
|
|
}
|
|
|
|
/**
|
|
* Specifies values for an insert query indexed by column names
|
|
* without need to use parameters()
|
|
* Replaces any previous parameters with same name.
|
|
*
|
|
* <code>
|
|
* $qb = $conn->createQueryBuilder()
|
|
* ->insert('users')
|
|
* ->directValues(
|
|
* array(
|
|
* 'name' => 'test',
|
|
* 'password' => 10
|
|
* )
|
|
* );
|
|
* </code>
|
|
*
|
|
* @param array $values the values to specify for the insert query indexed by column names
|
|
*
|
|
* @return $this
|
|
*/
|
|
public function directValues(array $values, array $types = [])
|
|
{
|
|
if ($this->getMultiInsert()) {
|
|
throw new \InvalidArgumentException('directValues mixed with multiInsertValues');
|
|
}
|
|
foreach ($values as $param => $value) {
|
|
$key = ":{$param}";
|
|
|
|
if ($this->getType() == self::INSERT) {
|
|
$this->setValue('`'.$param.'`', $key);
|
|
} elseif ($this->getType() == self::UPDATE) {
|
|
$this->set('`'.$param.'`', $key);
|
|
} elseif ($this->getType() == self::DELETE) {
|
|
throw new \InvalidArgumentException('directValues with DELETE not implemented');
|
|
}
|
|
|
|
$this->setParameter($param, $value, isset($types[$param]) ? $types[$param] : null);
|
|
}
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function getSQL()
|
|
{
|
|
$hasLimit = $this->getMaxResults() !== null && $this->getMaxResults() > 0;
|
|
|
|
// custom support for JOINs in UPDATE statements
|
|
if ($this->getType() == self::UPDATE && (!empty($this->getQueryPart('join')) || $hasLimit)) {
|
|
return $this->getSQLForUpdate();
|
|
}
|
|
|
|
if ($this->getType() === self::INSERT) {
|
|
return $this->getSQLForInsert();
|
|
}
|
|
|
|
// custom support for JOINs in DELETE statements
|
|
$tableAlias = $this->getQueryPart('from')[0]['alias'] ?? $this->getQueryPart('from')['alias'] ?? '';
|
|
// we should use custom supports in DELETE statements even if there is table alias without JOINs
|
|
if ($this->getType() == self::DELETE && (!empty($this->getQueryPart('join')) || !empty($tableAlias) || $hasLimit)) {
|
|
return $this->getSQLForDelete();
|
|
}
|
|
|
|
if ($this->getType() == self::SELECT && $this->calcRows) {
|
|
$selectPart = $this->getQueryPart('select');
|
|
if (!empty($selectPart) && strpos($selectPart[0], 'SQL_CALC_FOUND_ROWS') === false) {
|
|
$selectPart[0] = 'SQL_CALC_FOUND_ROWS '.$selectPart[0];
|
|
$this->select($selectPart);
|
|
}
|
|
}
|
|
|
|
$sql = parent::getSQL();
|
|
|
|
if ($this->forceIndexes) {
|
|
$sql = $this->modifySqlAddForceIndexes($sql);
|
|
}
|
|
|
|
if ($this->withExpressions) {
|
|
$sql = $this->modifySqlAddWithExpressions($sql);
|
|
}
|
|
|
|
return $sql.($this->forUpdate ? ' FOR UPDATE' : '').($this->sendToMaster ? ' -- maxscale route to master' : '');
|
|
}
|
|
|
|
private function getSQLForUpdate()
|
|
{
|
|
$sqlParts = $this->getQueryParts();
|
|
|
|
// Hack: Njncsdd, je to vsechno private a radsi reflection nez kopirovat pulku QB
|
|
$reflector = new \ReflectionClass(\Doctrine\DBAL\Query\QueryBuilder::class);
|
|
$method = $reflector->getMethod('getFromClauses');
|
|
$method->setAccessible(true);
|
|
|
|
// UPDATE has different "from" structure than SELECT
|
|
$from = $this->getQueryPart('from');
|
|
if (!isset($from[0])) {
|
|
$this->resetQueryPart('from')
|
|
->add('from', $from, true);
|
|
}
|
|
|
|
$table = implode(', ', $method->invoke($this));
|
|
|
|
$query = 'UPDATE '.$table
|
|
.' SET '.implode(', ', $sqlParts['set'])
|
|
.($sqlParts['where'] !== null ? ' WHERE '.((string) $sqlParts['where']) : '');
|
|
|
|
if ($orderBy = $sqlParts['orderBy'] ?? []) {
|
|
$query .= ' ORDER BY '.implode(', ', $orderBy);
|
|
}
|
|
|
|
if (null !== ($limit = $this->getMaxResults()) && $limit > 0) {
|
|
$query .= ' LIMIT '.$limit;
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
private function getSQLForDelete()
|
|
{
|
|
$sqlParts = $this->getQueryParts();
|
|
|
|
// Hack: Njncsdd, je to vsechno private a radsi reflection nez kopirovat pulku QB
|
|
$reflector = new \ReflectionClass(\Doctrine\DBAL\Query\QueryBuilder::class);
|
|
$method = $reflector->getMethod('getFromClauses');
|
|
$method->setAccessible(true);
|
|
|
|
$tableAlias = $this->getQueryPart('from')[0]['alias'] ?? '';
|
|
$isLimitQuery = $this->getMaxResults() !== null && $this->getMaxResults() > 0;
|
|
|
|
// https://mariadb.com/kb/en/delete/#description
|
|
if (($isLimitQuery || !empty($sqlParts['orderBy'])) && (
|
|
!empty($sqlParts['join'])
|
|
|| $tableAlias
|
|
|| (isset($from['table'], $from[0]) && $from['table'] !== $from[0]['table'])
|
|
)) {
|
|
throw new \Doctrine\DBAL\Exception('Cannot use multi-table syntax with LIMIT or ORDER BY. Remove any joins, multi-table expressions or table aliases in DELETE FROM.');
|
|
}
|
|
|
|
// UPDATE has different "from" structure than SELECT
|
|
$from = $this->getQueryPart('from');
|
|
if (!isset($from[0])) {
|
|
$this->resetQueryPart('from')
|
|
->add('from', $from, true);
|
|
}
|
|
|
|
$fromTable = implode(', ', $method->invoke($this));
|
|
|
|
$query = 'DELETE'.($tableAlias ? " {$tableAlias}" : '').' FROM '.$fromTable.($sqlParts['where'] !== null ? ' WHERE '.((string) $sqlParts['where']) : '');
|
|
|
|
if ($orderBy = $sqlParts['orderBy'] ?? []) {
|
|
$query .= ' ORDER BY '.implode(', ', $orderBy);
|
|
}
|
|
|
|
if ($isLimitQuery) {
|
|
$query .= ' LIMIT '.$this->getMaxResults();
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
private function getSQLForInsert()
|
|
{
|
|
$sqlParts = $this->getQueryParts();
|
|
|
|
return 'INSERT INTO '.$sqlParts['from']['table'].$this->getSQLForValues().$this->getSQLForOnDuplicateKeyUpdate();
|
|
}
|
|
|
|
/**
|
|
* Implements support for multi-insert.
|
|
*
|
|
* @return string
|
|
*/
|
|
private function getSQLForValues()
|
|
{
|
|
$sqlParts = $this->getQueryParts();
|
|
$values = [];
|
|
$columnsToUpdate = [];
|
|
if ($this->getMultiInsert()) {
|
|
$first = reset($sqlParts['values']);
|
|
$columnsToUpdate = array_keys($first);
|
|
foreach ($sqlParts['values'] as $vls) {
|
|
$values[] = '('.implode(', ', $vls).')';
|
|
}
|
|
} else {
|
|
$columnsToUpdate = array_keys($sqlParts['values']);
|
|
$values[] = '('.implode(', ', $sqlParts['values']).')';
|
|
}
|
|
|
|
return ' ('.implode(',', $columnsToUpdate).') VALUES '.implode(',', $values).' ';
|
|
}
|
|
|
|
protected function modifySqlAddForceIndexes($sql)
|
|
{
|
|
$sqlParts = $this->getQueryParts()['join'];
|
|
|
|
$joinsToModify = [];
|
|
|
|
foreach ($sqlParts as $tableJoins) {
|
|
foreach ($tableJoins as $join) {
|
|
if (array_key_exists($join['joinAlias'], $this->forceIndexes)) {
|
|
$joinsToModify[] = $join;
|
|
}
|
|
}
|
|
}
|
|
|
|
$getJoin = function ($join, $index = '') {
|
|
$sql = 'JOIN '.$join['joinTable'].' '.$join['joinAlias'];
|
|
if ($index) {
|
|
$sql .= " FORCE INDEX ({$index})";
|
|
}
|
|
|
|
return $sql.' ON';
|
|
};
|
|
|
|
foreach ($joinsToModify as $join) {
|
|
$replacedCount = 0;
|
|
$sql = str_replace($getJoin($join), $getJoin($join, $this->forceIndexes[$join['joinAlias']]), $sql, $replacedCount);
|
|
|
|
if ($replacedCount > 1) {
|
|
throw new \LogicException('QueryBuilder tried to add FORCE UPDATE clause to join, but multiple matching joins were found for the alias '.$join['joinAlias']);
|
|
}
|
|
}
|
|
|
|
return $sql;
|
|
}
|
|
|
|
public function setForceIndexForJoin($joinAlias, $indexName)
|
|
{
|
|
$this->forceIndexes[$joinAlias] = $indexName;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function withExpression(string $alias, QueryBuilder $qb, ?int $priority = null)
|
|
{
|
|
$data = [
|
|
'sql' => $qb->getSQL(),
|
|
'alias' => $alias,
|
|
];
|
|
|
|
if ($priority) {
|
|
$this->withExpressions[$priority] = $data;
|
|
} else {
|
|
$this->withExpressions[] = $data;
|
|
}
|
|
|
|
$this->addParameters($qb->getParameters(), $qb->getParameterTypes());
|
|
|
|
return $this;
|
|
}
|
|
|
|
protected function modifySqlAddWithExpressions($sql): string
|
|
{
|
|
$withSql = 'WITH ';
|
|
|
|
foreach ($this->withExpressions as $with) {
|
|
$withSql .= $with['alias'].' AS ('.$with['sql'].')';
|
|
if (next($this->withExpressions)) {
|
|
$withSql .= ', ';
|
|
}
|
|
}
|
|
|
|
return $withSql.' '.$sql;
|
|
}
|
|
|
|
/**
|
|
* Implements support for ON DUPLICATE KEY UPDATE.
|
|
*
|
|
* @return string
|
|
*/
|
|
private function getSQLForOnDuplicateKeyUpdate()
|
|
{
|
|
$onDuplicateKeyUpdate = '';
|
|
// build on duplicate key update
|
|
if ($this->onDuplicateKeyUpdate) {
|
|
$onDuplicateKeyUpdate = ' ON DUPLICATE KEY UPDATE ';
|
|
$duplicateKeyValues = [];
|
|
foreach ($this->onDuplicateKeyUpdate as $column => $value) {
|
|
$duplicateKeyValues[] = is_numeric($column) ? $value.'=VALUES('.$value.')' : $column.'='.$value;
|
|
}
|
|
$onDuplicateKeyUpdate .= implode(', ', $duplicateKeyValues).' ';
|
|
}
|
|
|
|
return $onDuplicateKeyUpdate;
|
|
}
|
|
|
|
/** Používat jen v krajní nouzi */
|
|
public function removeJoin($table, $table2)
|
|
{
|
|
unset($this->joins[$table2]);
|
|
$parts = $this->getQueryPart('join');
|
|
$this->resetQueryPart('join');
|
|
foreach ($parts as $tmp_table => $tmp_part) {
|
|
foreach ($tmp_part as $part) {
|
|
if ($part['joinAlias'] != $table2) {
|
|
parent::add('join', [$tmp_table => $part], true);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public function addQueryBuilderParameters(QueryBuilder $qb): QueryBuilder
|
|
{
|
|
$this->addParameters($qb->getParameters(), $qb->getParameterTypes());
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function addCalcRows()
|
|
{
|
|
$this->calcRows = true;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function onDuplicateKeyUpdate(array $values): QueryBuilder
|
|
{
|
|
if ($this->getType() !== self::INSERT) {
|
|
throw new \InvalidArgumentException('Call of "onDuplicateKeyUpdate" is allowed only with insert query');
|
|
}
|
|
|
|
$this->onDuplicateKeyUpdate = $values;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function sendToMaster()
|
|
{
|
|
$this->sendToMaster = true;
|
|
|
|
return $this;
|
|
}
|
|
|
|
public function forUpdate()
|
|
{
|
|
$this->forUpdate = true;
|
|
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Using on queries with ? may not work.
|
|
*
|
|
* @return string SQL with inserted params
|
|
*/
|
|
public function getRunnableSQL(): string
|
|
{
|
|
$util = new DoctrineExtension();
|
|
$sql = $util->replaceQueryParameters($this->getSQL(), $this->getParameters());
|
|
|
|
return $util->formatSql($sql, false);
|
|
}
|
|
}
|
|
|
|
if (empty($subclass)) {
|
|
class QueryBuilder extends QueryBuilderBase
|
|
{
|
|
}
|
|
}
|