123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359 |
- <?php
- /**
- * @copyright Copyright (c) 2016, ownCloud, Inc.
- *
- * @author Arthur Schiwon <blizzz@arthur-schiwon.de>
- * @author Christoph Wurst <christoph@winzerhof-wurst.at>
- * @author Daniel Kesselberg <mail@danielkesselberg.de>
- * @author J0WI <J0WI@users.noreply.github.com>
- * @author Joas Schilling <coding@schilljs.com>
- * @author Lukas Reschke <lukas@statuscode.ch>
- * @author Robin Appelman <robin@icewind.nl>
- * @author Roeland Jago Douma <roeland@famdouma.nl>
- * @author Thomas Müller <thomas.mueller@tmit.eu>
- * @author Vincent Petry <vincent@nextcloud.com>
- *
- * @license AGPL-3.0
- *
- * This code is free software: you can redistribute it and/or modify
- * it under the terms of the GNU Affero General Public License, version 3,
- * as published by the Free Software Foundation.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- * GNU Affero General Public License for more details.
- *
- * You should have received a copy of the GNU Affero General Public License, version 3,
- * along with this program. If not, see <http://www.gnu.org/licenses/>
- *
- */
- namespace OC\DB\QueryBuilder;
- use Doctrine\DBAL\Platforms\MySQLPlatform;
- use Doctrine\DBAL\Platforms\OraclePlatform;
- use Doctrine\DBAL\Platforms\PostgreSQL94Platform;
- use Doctrine\DBAL\Platforms\SqlitePlatform;
- use Doctrine\DBAL\Query\QueryException;
- use OC\DB\ConnectionAdapter;
- use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder;
- use OC\DB\QueryBuilder\ExpressionBuilder\MySqlExpressionBuilder;
- use OC\DB\QueryBuilder\ExpressionBuilder\OCIExpressionBuilder;
- use OC\DB\QueryBuilder\ExpressionBuilder\PgSqlExpressionBuilder;
- use OC\DB\QueryBuilder\ExpressionBuilder\SqliteExpressionBuilder;
- use OC\DB\QueryBuilder\FunctionBuilder\FunctionBuilder;
- use OC\DB\QueryBuilder\FunctionBuilder\OCIFunctionBuilder;
- use OC\DB\QueryBuilder\FunctionBuilder\PgSqlFunctionBuilder;
- use OC\DB\QueryBuilder\FunctionBuilder\SqliteFunctionBuilder;
- use OC\DB\ResultAdapter;
- use OC\SystemConfig;
- use OCP\DB\IResult;
- use OCP\DB\QueryBuilder\ICompositeExpression;
- use OCP\DB\QueryBuilder\ILiteral;
- use OCP\DB\QueryBuilder\IParameter;
- use OCP\DB\QueryBuilder\IQueryBuilder;
- use OCP\DB\QueryBuilder\IQueryFunction;
- use Psr\Log\LoggerInterface;
- class QueryBuilder implements IQueryBuilder {
- /** @var ConnectionAdapter */
- private $connection;
- /** @var SystemConfig */
- private $systemConfig;
- private LoggerInterface $logger;
- /** @var \Doctrine\DBAL\Query\QueryBuilder */
- private $queryBuilder;
- /** @var QuoteHelper */
- private $helper;
- /** @var bool */
- private $automaticTablePrefix = true;
- /** @var string */
- protected $lastInsertedTable;
- /**
- * Initializes a new QueryBuilder.
- *
- * @param ConnectionAdapter $connection
- * @param SystemConfig $systemConfig
- */
- public function __construct(ConnectionAdapter $connection, SystemConfig $systemConfig, LoggerInterface $logger) {
- $this->connection = $connection;
- $this->systemConfig = $systemConfig;
- $this->logger = $logger;
- $this->queryBuilder = new \Doctrine\DBAL\Query\QueryBuilder($this->connection->getInner());
- $this->helper = new QuoteHelper();
- }
- /**
- * Enable/disable automatic prefixing of table names with the oc_ prefix
- *
- * @param bool $enabled If set to true table names will be prefixed with the
- * owncloud database prefix automatically.
- * @since 8.2.0
- */
- public function automaticTablePrefix($enabled) {
- $this->automaticTablePrefix = (bool) $enabled;
- }
- /**
- * Gets an ExpressionBuilder used for object-oriented construction of query expressions.
- * This producer method is intended for convenient inline usage. Example:
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u')
- * ->from('users', 'u')
- * ->where($qb->expr()->eq('u.id', 1));
- * </code>
- *
- * For more complex expression construction, consider storing the expression
- * builder object in a local variable.
- *
- * @return \OCP\DB\QueryBuilder\IExpressionBuilder
- */
- public function expr() {
- if ($this->connection->getDatabasePlatform() instanceof OraclePlatform) {
- return new OCIExpressionBuilder($this->connection, $this);
- }
- if ($this->connection->getDatabasePlatform() instanceof PostgreSQL94Platform) {
- return new PgSqlExpressionBuilder($this->connection, $this);
- }
- if ($this->connection->getDatabasePlatform() instanceof MySQLPlatform) {
- return new MySqlExpressionBuilder($this->connection, $this);
- }
- if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) {
- return new SqliteExpressionBuilder($this->connection, $this);
- }
- return new ExpressionBuilder($this->connection, $this);
- }
- /**
- * Gets an FunctionBuilder used for object-oriented construction of query functions.
- * This producer method is intended for convenient inline usage. Example:
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u')
- * ->from('users', 'u')
- * ->where($qb->fun()->md5('u.id'));
- * </code>
- *
- * For more complex function construction, consider storing the function
- * builder object in a local variable.
- *
- * @return \OCP\DB\QueryBuilder\IFunctionBuilder
- */
- public function func() {
- if ($this->connection->getDatabasePlatform() instanceof OraclePlatform) {
- return new OCIFunctionBuilder($this->connection, $this, $this->helper);
- }
- if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) {
- return new SqliteFunctionBuilder($this->connection, $this, $this->helper);
- }
- if ($this->connection->getDatabasePlatform() instanceof PostgreSQL94Platform) {
- return new PgSqlFunctionBuilder($this->connection, $this, $this->helper);
- }
- return new FunctionBuilder($this->connection, $this, $this->helper);
- }
- /**
- * Gets the type of the currently built query.
- *
- * @return integer
- */
- public function getType() {
- return $this->queryBuilder->getType();
- }
- /**
- * Gets the associated DBAL Connection for this query builder.
- *
- * @return \OCP\IDBConnection
- */
- public function getConnection() {
- return $this->connection;
- }
- /**
- * Gets the state of this query builder instance.
- *
- * @return integer Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.
- */
- public function getState() {
- return $this->queryBuilder->getState();
- }
- /**
- * Executes this query using the bound parameters and their types.
- *
- * Uses {@see Connection::executeQuery} for select statements and {@see Connection::executeUpdate}
- * for insert, update and delete statements.
- *
- * @return IResult|int
- */
- public function execute() {
- if ($this->systemConfig->getValue('log_query', false)) {
- try {
- $params = [];
- foreach ($this->getParameters() as $placeholder => $value) {
- if ($value instanceof \DateTime) {
- $params[] = $placeholder . ' => DateTime:\'' . $value->format('c') . '\'';
- } elseif (is_array($value)) {
- $params[] = $placeholder . ' => (\'' . implode('\', \'', $value) . '\')';
- } else {
- $params[] = $placeholder . ' => \'' . $value . '\'';
- }
- }
- if (empty($params)) {
- $this->logger->debug('DB QueryBuilder: \'{query}\'', [
- 'query' => $this->getSQL(),
- 'app' => 'core',
- ]);
- } else {
- $this->logger->debug('DB QueryBuilder: \'{query}\' with parameters: {params}', [
- 'query' => $this->getSQL(),
- 'params' => implode(', ', $params),
- 'app' => 'core',
- ]);
- }
- } catch (\Error $e) {
- // likely an error during conversion of $value to string
- $this->logger->error('DB QueryBuilder: error trying to log SQL query', ['exception' => $e]);
- }
- }
- if (!empty($this->getQueryPart('select'))) {
- $select = $this->getQueryPart('select');
- $hasSelectAll = array_filter($select, static function ($s) {
- return $s === '*';
- });
- $hasSelectSpecific = array_filter($select, static function ($s) {
- return $s !== '*';
- });
- if (empty($hasSelectAll) === empty($hasSelectSpecific)) {
- $exception = new QueryException('Query is selecting * and specific values in the same query. This is not supported in Oracle.');
- $this->logger->error($exception->getMessage(), [
- 'query' => $this->getSQL(),
- 'app' => 'core',
- 'exception' => $exception,
- ]);
- }
- }
- $numberOfParameters = 0;
- $hasTooLargeArrayParameter = false;
- foreach ($this->getParameters() as $parameter) {
- if (is_array($parameter)) {
- $count = count($parameter);
- $numberOfParameters += $count;
- $hasTooLargeArrayParameter = $hasTooLargeArrayParameter || ($count > 1000);
- }
- }
- if ($hasTooLargeArrayParameter) {
- $exception = new QueryException('More than 1000 expressions in a list are not allowed on Oracle.');
- $this->logger->error($exception->getMessage(), [
- 'query' => $this->getSQL(),
- 'app' => 'core',
- 'exception' => $exception,
- ]);
- }
- if ($numberOfParameters > 65535) {
- $exception = new QueryException('The number of parameters must not exceed 65535. Restriction by PostgreSQL.');
- $this->logger->error($exception->getMessage(), [
- 'query' => $this->getSQL(),
- 'app' => 'core',
- 'exception' => $exception,
- ]);
- }
- $result = $this->queryBuilder->execute();
- if (is_int($result)) {
- return $result;
- }
- return new ResultAdapter($result);
- }
- public function executeQuery(): IResult {
- if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
- throw new \RuntimeException('Invalid query type, expected SELECT query');
- }
- try {
- $result = $this->execute();
- } catch (\Doctrine\DBAL\Exception $e) {
- throw \OC\DB\Exceptions\DbalException::wrap($e);
- }
- if ($result instanceof IResult) {
- return $result;
- }
- throw new \RuntimeException('Invalid return type for query');
- }
- /**
- * Monkey-patched compatibility layer for apps that were adapted for Nextcloud 22 before
- * the first beta, where executeStatement was named executeUpdate.
- *
- * Static analysis should catch those misuses, but until then let's try to keep things
- * running.
- *
- * @internal
- * @deprecated
- * @todo drop ASAP
- */
- public function executeUpdate(): int {
- return $this->executeStatement();
- }
- public function executeStatement(): int {
- if ($this->getType() === \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
- throw new \RuntimeException('Invalid query type, expected INSERT, DELETE or UPDATE statement');
- }
- try {
- $result = $this->execute();
- } catch (\Doctrine\DBAL\Exception $e) {
- throw \OC\DB\Exceptions\DbalException::wrap($e);
- }
- if (!is_int($result)) {
- throw new \RuntimeException('Invalid return type for statement');
- }
- return $result;
- }
- /**
- * Gets the complete SQL string formed by the current specifications of this QueryBuilder.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u')
- * ->from('User', 'u')
- * echo $qb->getSQL(); // SELECT u FROM User u
- * </code>
- *
- * @return string The SQL query string.
- */
- public function getSQL() {
- return $this->queryBuilder->getSQL();
- }
- /**
- * Sets a query parameter for the query being constructed.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u')
- * ->from('users', 'u')
- * ->where('u.id = :user_id')
- * ->setParameter(':user_id', 1);
- * </code>
- *
- * @param string|integer $key The parameter position or name.
- * @param mixed $value The parameter value.
- * @param string|null|int $type One of the IQueryBuilder::PARAM_* constants.
- *
- * @return $this This QueryBuilder instance.
- */
- public function setParameter($key, $value, $type = null) {
- $this->queryBuilder->setParameter($key, $value, $type);
- return $this;
- }
- /**
- * Sets a collection of query parameters for the query being constructed.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u')
- * ->from('users', 'u')
- * ->where('u.id = :user_id1 OR u.id = :user_id2')
- * ->setParameters(array(
- * ':user_id1' => 1,
- * ':user_id2' => 2
- * ));
- * </code>
- *
- * @param array $params The query parameters to set.
- * @param array $types The query parameters types to set.
- *
- * @return $this This QueryBuilder instance.
- */
- public function setParameters(array $params, array $types = []) {
- $this->queryBuilder->setParameters($params, $types);
- return $this;
- }
- /**
- * Gets all defined query parameters for the query being constructed indexed by parameter index or name.
- *
- * @return array The currently defined query parameters indexed by parameter index or name.
- */
- public function getParameters() {
- return $this->queryBuilder->getParameters();
- }
- /**
- * Gets a (previously set) query parameter of the query being constructed.
- *
- * @param mixed $key The key (index or name) of the bound parameter.
- *
- * @return mixed The value of the bound parameter.
- */
- public function getParameter($key) {
- return $this->queryBuilder->getParameter($key);
- }
- /**
- * Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
- *
- * @return array The currently defined query parameter types indexed by parameter index or name.
- */
- public function getParameterTypes() {
- return $this->queryBuilder->getParameterTypes();
- }
- /**
- * Gets a (previously set) query parameter type of the query being constructed.
- *
- * @param mixed $key The key (index or name) of the bound parameter type.
- *
- * @return mixed The value of the bound parameter type.
- */
- public function getParameterType($key) {
- return $this->queryBuilder->getParameterType($key);
- }
- /**
- * Sets the position of the first result to retrieve (the "offset").
- *
- * @param int $firstResult The first result to return.
- *
- * @return $this This QueryBuilder instance.
- */
- public function setFirstResult($firstResult) {
- $this->queryBuilder->setFirstResult((int) $firstResult);
- return $this;
- }
- /**
- * Gets the position of the first result the query object was set to retrieve (the "offset").
- * Returns 0 if {@link setFirstResult} was not applied to this QueryBuilder.
- *
- * @return int The position of the first result.
- */
- public function getFirstResult() {
- return $this->queryBuilder->getFirstResult();
- }
- /**
- * Sets the maximum number of results to retrieve (the "limit").
- *
- * NOTE: Setting max results to "0" will cause mixed behaviour. While most
- * of the databases will just return an empty result set, Oracle will return
- * all entries.
- *
- * @param int|null $maxResults The maximum number of results to retrieve.
- *
- * @return $this This QueryBuilder instance.
- */
- public function setMaxResults($maxResults) {
- if ($maxResults === null) {
- $this->queryBuilder->setMaxResults($maxResults);
- } else {
- $this->queryBuilder->setMaxResults((int) $maxResults);
- }
- return $this;
- }
- /**
- * Gets the maximum number of results the query object was set to retrieve (the "limit").
- * Returns NULL if {@link setMaxResults} was not applied to this query builder.
- *
- * @return int|null The maximum number of results.
- */
- public function getMaxResults() {
- return $this->queryBuilder->getMaxResults();
- }
- /**
- * Specifies an item that is to be returned in the query result.
- * Replaces any previously specified selections, if any.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.id', 'p.id')
- * ->from('users', 'u')
- * ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');
- * </code>
- *
- * @param mixed ...$selects The selection expressions.
- *
- * '@return $this This QueryBuilder instance.
- */
- public function select(...$selects) {
- if (count($selects) === 1 && is_array($selects[0])) {
- $selects = $selects[0];
- }
- $this->queryBuilder->select(
- $this->helper->quoteColumnNames($selects)
- );
- return $this;
- }
- /**
- * Specifies an item that is to be returned with a different name in the query result.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->selectAlias('u.id', 'user_id')
- * ->from('users', 'u')
- * ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');
- * </code>
- *
- * @param mixed $select The selection expressions.
- * @param string $alias The column alias used in the constructed query.
- *
- * @return $this This QueryBuilder instance.
- */
- public function selectAlias($select, $alias) {
- $this->queryBuilder->addSelect(
- $this->helper->quoteColumnName($select) . ' AS ' . $this->helper->quoteColumnName($alias)
- );
- return $this;
- }
- /**
- * Specifies an item that is to be returned uniquely in the query result.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->selectDistinct('type')
- * ->from('users');
- * </code>
- *
- * @param mixed $select The selection expressions.
- *
- * @return $this This QueryBuilder instance.
- */
- public function selectDistinct($select) {
- if (!is_array($select)) {
- $select = [$select];
- }
- $quotedSelect = $this->helper->quoteColumnNames($select);
- $this->queryBuilder->addSelect(
- 'DISTINCT ' . implode(', ', $quotedSelect)
- );
- return $this;
- }
- /**
- * Adds an item that is to be returned in the query result.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.id')
- * ->addSelect('p.id')
- * ->from('users', 'u')
- * ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');
- * </code>
- *
- * @param mixed ...$selects The selection expression.
- *
- * @return $this This QueryBuilder instance.
- */
- public function addSelect(...$selects) {
- if (count($selects) === 1 && is_array($selects[0])) {
- $selects = $selects[0];
- }
- $this->queryBuilder->addSelect(
- $this->helper->quoteColumnNames($selects)
- );
- return $this;
- }
- /**
- * Turns the query being built into a bulk delete query that ranges over
- * a certain table.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->delete('users', 'u')
- * ->where('u.id = :user_id');
- * ->setParameter(':user_id', 1);
- * </code>
- *
- * @param string $delete The table whose rows are subject to the deletion.
- * @param string $alias The table alias used in the constructed query.
- *
- * @return $this This QueryBuilder instance.
- */
- public function delete($delete = null, $alias = null) {
- $this->queryBuilder->delete(
- $this->getTableName($delete),
- $alias
- );
- return $this;
- }
- /**
- * Turns the query being built into a bulk update query that ranges over
- * a certain table
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->update('users', 'u')
- * ->set('u.password', md5('password'))
- * ->where('u.id = ?');
- * </code>
- *
- * @param string $update The table whose rows are subject to the update.
- * @param string $alias The table alias used in the constructed query.
- *
- * @return $this This QueryBuilder instance.
- */
- public function update($update = null, $alias = null) {
- $this->queryBuilder->update(
- $this->getTableName($update),
- $alias
- );
- return $this;
- }
- /**
- * Turns the query being built into an insert query that inserts into
- * a certain table
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->insert('users')
- * ->values(
- * array(
- * 'name' => '?',
- * 'password' => '?'
- * )
- * );
- * </code>
- *
- * @param string $insert The table into which the rows should be inserted.
- *
- * @return $this This QueryBuilder instance.
- */
- public function insert($insert = null) {
- $this->queryBuilder->insert(
- $this->getTableName($insert)
- );
- $this->lastInsertedTable = $insert;
- return $this;
- }
- /**
- * Creates and adds a query root corresponding to the table identified by the
- * given alias, forming a cartesian product with any existing query roots.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.id')
- * ->from('users', 'u')
- * </code>
- *
- * @param string|IQueryFunction $from The table.
- * @param string|null $alias The alias of the table.
- *
- * @return $this This QueryBuilder instance.
- */
- public function from($from, $alias = null) {
- $this->queryBuilder->from(
- $this->getTableName($from),
- $this->quoteAlias($alias)
- );
- return $this;
- }
- /**
- * Creates and adds a join to the query.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.name')
- * ->from('users', 'u')
- * ->join('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|ICompositeExpression|null $condition The condition for the join.
- *
- * @return $this This QueryBuilder instance.
- */
- public function join($fromAlias, $join, $alias, $condition = null) {
- $this->queryBuilder->join(
- $this->quoteAlias($fromAlias),
- $this->getTableName($join),
- $this->quoteAlias($alias),
- $condition
- );
- return $this;
- }
- /**
- * Creates and adds a join to the query.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.name')
- * ->from('users', 'u')
- * ->innerJoin('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|ICompositeExpression|null $condition The condition for the join.
- *
- * @return $this This QueryBuilder instance.
- */
- public function innerJoin($fromAlias, $join, $alias, $condition = null) {
- $this->queryBuilder->innerJoin(
- $this->quoteAlias($fromAlias),
- $this->getTableName($join),
- $this->quoteAlias($alias),
- $condition
- );
- return $this;
- }
- /**
- * 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|ICompositeExpression|null $condition The condition for the join.
- *
- * @return $this This QueryBuilder instance.
- */
- public function leftJoin($fromAlias, $join, $alias, $condition = null) {
- $this->queryBuilder->leftJoin(
- $this->quoteAlias($fromAlias),
- $this->getTableName($join),
- $this->quoteAlias($alias),
- $condition
- );
- return $this;
- }
- /**
- * Creates and adds a right join to the query.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.name')
- * ->from('users', 'u')
- * ->rightJoin('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|ICompositeExpression|null $condition The condition for the join.
- *
- * @return $this This QueryBuilder instance.
- */
- public function rightJoin($fromAlias, $join, $alias, $condition = null) {
- $this->queryBuilder->rightJoin(
- $this->quoteAlias($fromAlias),
- $this->getTableName($join),
- $this->quoteAlias($alias),
- $condition
- );
- return $this;
- }
- /**
- * Sets a new value for a column in a bulk update query.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->update('users', 'u')
- * ->set('u.password', md5('password'))
- * ->where('u.id = ?');
- * </code>
- *
- * @param string $key The column to set.
- * @param ILiteral|IParameter|IQueryFunction|string $value The value, expression, placeholder, etc.
- *
- * @return $this This QueryBuilder instance.
- */
- public function set($key, $value) {
- $this->queryBuilder->set(
- $this->helper->quoteColumnName($key),
- $this->helper->quoteColumnName($value)
- );
- return $this;
- }
- /**
- * Specifies one or more restrictions to the query result.
- * Replaces any previously specified restrictions, if any.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.name')
- * ->from('users', 'u')
- * ->where('u.id = ?');
- *
- * // You can optionally programmatically build and/or expressions
- * $qb = $conn->getQueryBuilder();
- *
- * $or = $qb->expr()->orx();
- * $or->add($qb->expr()->eq('u.id', 1));
- * $or->add($qb->expr()->eq('u.id', 2));
- *
- * $qb->update('users', 'u')
- * ->set('u.password', md5('password'))
- * ->where($or);
- * </code>
- *
- * @param mixed ...$predicates The restriction predicates.
- *
- * @return $this This QueryBuilder instance.
- */
- public function where(...$predicates) {
- if ($this->getQueryPart('where') !== null && $this->systemConfig->getValue('debug', false)) {
- // Only logging a warning, not throwing for now.
- $e = new QueryException('Using where() on non-empty WHERE part, please verify it is intentional to not call whereAnd() or whereOr() instead. Otherwise consider creating a new query builder object or call resetQueryPart(\'where\') first.');
- $this->logger->warning($e->getMessage(), ['exception' => $e]);
- }
- call_user_func_array(
- [$this->queryBuilder, 'where'],
- $predicates
- );
- return $this;
- }
- /**
- * Adds one or more restrictions to the query results, forming a logical
- * conjunction with any previously specified restrictions.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u')
- * ->from('users', 'u')
- * ->where('u.username LIKE ?')
- * ->andWhere('u.is_active = 1');
- * </code>
- *
- * @param mixed ...$where The query restrictions.
- *
- * @return $this This QueryBuilder instance.
- *
- * @see where()
- */
- public function andWhere(...$where) {
- call_user_func_array(
- [$this->queryBuilder, 'andWhere'],
- $where
- );
- return $this;
- }
- /**
- * Adds one or more restrictions to the query results, forming a logical
- * disjunction with any previously specified restrictions.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.name')
- * ->from('users', 'u')
- * ->where('u.id = 1')
- * ->orWhere('u.id = 2');
- * </code>
- *
- * @param mixed ...$where The WHERE statement.
- *
- * @return $this This QueryBuilder instance.
- *
- * @see where()
- */
- public function orWhere(...$where) {
- call_user_func_array(
- [$this->queryBuilder, 'orWhere'],
- $where
- );
- return $this;
- }
- /**
- * Specifies a grouping over the results of the query.
- * Replaces any previously specified groupings, if any.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.name')
- * ->from('users', 'u')
- * ->groupBy('u.id');
- * </code>
- *
- * @param mixed ...$groupBys The grouping expression.
- *
- * @return $this This QueryBuilder instance.
- */
- public function groupBy(...$groupBys) {
- if (count($groupBys) === 1 && is_array($groupBys[0])) {
- $groupBys = $groupBys[0];
- }
- call_user_func_array(
- [$this->queryBuilder, 'groupBy'],
- $this->helper->quoteColumnNames($groupBys)
- );
- return $this;
- }
- /**
- * Adds a grouping expression to the query.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->select('u.name')
- * ->from('users', 'u')
- * ->groupBy('u.lastLogin');
- * ->addGroupBy('u.createdAt')
- * </code>
- *
- * @param mixed ...$groupBy The grouping expression.
- *
- * @return $this This QueryBuilder instance.
- */
- public function addGroupBy(...$groupBys) {
- if (count($groupBys) === 1 && is_array($groupBys[0])) {
- $$groupBys = $groupBys[0];
- }
- call_user_func_array(
- [$this->queryBuilder, 'addGroupBy'],
- $this->helper->quoteColumnNames($groupBys)
- );
- return $this;
- }
- /**
- * Sets a value for a column in an insert query.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->insert('users')
- * ->values(
- * array(
- * 'name' => '?'
- * )
- * )
- * ->setValue('password', '?');
- * </code>
- *
- * @param string $column The column into which the value should be inserted.
- * @param IParameter|string $value The value that should be inserted into the column.
- *
- * @return $this This QueryBuilder instance.
- */
- public function setValue($column, $value) {
- $this->queryBuilder->setValue(
- $this->helper->quoteColumnName($column),
- (string) $value
- );
- return $this;
- }
- /**
- * Specifies values for an insert query indexed by column names.
- * Replaces any previous values, if any.
- *
- * <code>
- * $qb = $conn->getQueryBuilder()
- * ->insert('users')
- * ->values(
- * array(
- * 'name' => '?',
- * 'password' => '?'
- * )
- * );
- * </code>
- *
- * @param array $values The values to specify for the insert query indexed by column names.
- *
- * @return $this This QueryBuilder instance.
- */
- public function values(array $values) {
- $quotedValues = [];
- foreach ($values as $key => $value) {
- $quotedValues[$this->helper->quoteColumnName($key)] = $value;
- }
- $this->queryBuilder->values($quotedValues);
- return $this;
- }
- /**
- * Specifies a restriction over the groups of the query.
- * Replaces any previous having restrictions, if any.
- *
- * @param mixed ...$having The restriction over the groups.
- *
- * @return $this This QueryBuilder instance.
- */
- public function having(...$having) {
- call_user_func_array(
- [$this->queryBuilder, 'having'],
- $having
- );
- return $this;
- }
- /**
- * Adds a restriction over the groups of the query, forming a logical
- * conjunction with any existing having restrictions.
- *
- * @param mixed ...$having The restriction to append.
- *
- * @return $this This QueryBuilder instance.
- */
- public function andHaving(...$having) {
- call_user_func_array(
- [$this->queryBuilder, 'andHaving'],
- $having
- );
- return $this;
- }
- /**
- * Adds a restriction over the groups of the query, forming a logical
- * disjunction with any existing having restrictions.
- *
- * @param mixed ...$having The restriction to add.
- *
- * @return $this This QueryBuilder instance.
- */
- public function orHaving(...$having) {
- call_user_func_array(
- [$this->queryBuilder, 'orHaving'],
- $having
- );
- return $this;
- }
- /**
- * Specifies an ordering for the query results.
- * Replaces any previously specified orderings, if any.
- *
- * @param string|IQueryFunction|ILiteral|IParameter $sort The ordering expression.
- * @param string $order The ordering direction.
- *
- * @return $this This QueryBuilder instance.
- */
- public function orderBy($sort, $order = null) {
- $this->queryBuilder->orderBy(
- $this->helper->quoteColumnName($sort),
- $order
- );
- return $this;
- }
- /**
- * Adds an ordering to the query results.
- *
- * @param string|ILiteral|IParameter|IQueryFunction $sort The ordering expression.
- * @param string $order The ordering direction.
- *
- * @return $this This QueryBuilder instance.
- */
- public function addOrderBy($sort, $order = null) {
- $this->queryBuilder->addOrderBy(
- $this->helper->quoteColumnName($sort),
- $order
- );
- return $this;
- }
- /**
- * Gets a query part by its name.
- *
- * @param string $queryPartName
- *
- * @return mixed
- */
- public function getQueryPart($queryPartName) {
- return $this->queryBuilder->getQueryPart($queryPartName);
- }
- /**
- * Gets all query parts.
- *
- * @return array
- */
- public function getQueryParts() {
- return $this->queryBuilder->getQueryParts();
- }
- /**
- * Resets SQL parts.
- *
- * @param array|null $queryPartNames
- *
- * @return $this This QueryBuilder instance.
- */
- public function resetQueryParts($queryPartNames = null) {
- $this->queryBuilder->resetQueryParts($queryPartNames);
- return $this;
- }
- /**
- * Resets a single SQL part.
- *
- * @param string $queryPartName
- *
- * @return $this This QueryBuilder instance.
- */
- public function resetQueryPart($queryPartName) {
- $this->queryBuilder->resetQueryPart($queryPartName);
- return $this;
- }
- /**
- * Creates a new named parameter and bind the value $value to it.
- *
- * This method provides a shortcut for PDOStatement::bindValue
- * when using prepared statements.
- *
- * The parameter $value specifies the value that you want to bind. If
- * $placeholder is not provided bindValue() will automatically create a
- * placeholder for you. An automatic placeholder will be of the name
- * ':dcValue1', ':dcValue2' etc.
- *
- * For more information see {@link https://www.php.net/pdostatement-bindparam}
- *
- * Example:
- * <code>
- * $value = 2;
- * $q->eq( 'id', $q->bindValue( $value ) );
- * $stmt = $q->executeQuery(); // executed with 'id = 2'
- * </code>
- *
- * @license New BSD License
- * @link http://www.zetacomponents.org
- *
- * @param mixed $value
- * @param mixed $type
- * @param string $placeHolder The name to bind with. The string must start with a colon ':'.
- *
- * @return IParameter the placeholder name used.
- */
- public function createNamedParameter($value, $type = IQueryBuilder::PARAM_STR, $placeHolder = null) {
- return new Parameter($this->queryBuilder->createNamedParameter($value, $type, $placeHolder));
- }
- /**
- * Creates a new positional parameter and bind the given value to it.
- *
- * Attention: If you are using positional parameters with the query builder you have
- * to be very careful to bind all parameters in the order they appear in the SQL
- * statement , otherwise they get bound in the wrong order which can lead to serious
- * bugs in your code.
- *
- * Example:
- * <code>
- * $qb = $conn->getQueryBuilder();
- * $qb->select('u.*')
- * ->from('users', 'u')
- * ->where('u.username = ' . $qb->createPositionalParameter('Foo', IQueryBuilder::PARAM_STR))
- * ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', IQueryBuilder::PARAM_STR))
- * </code>
- *
- * @param mixed $value
- * @param integer $type
- *
- * @return IParameter
- */
- public function createPositionalParameter($value, $type = IQueryBuilder::PARAM_STR) {
- return new Parameter($this->queryBuilder->createPositionalParameter($value, $type));
- }
- /**
- * Creates a new parameter
- *
- * Example:
- * <code>
- * $qb = $conn->getQueryBuilder();
- * $qb->select('u.*')
- * ->from('users', 'u')
- * ->where('u.username = ' . $qb->createParameter('name'))
- * ->setParameter('name', 'Bar', IQueryBuilder::PARAM_STR))
- * </code>
- *
- * @param string $name
- *
- * @return IParameter
- */
- public function createParameter($name) {
- return new Parameter(':' . $name);
- }
- /**
- * Creates a new function
- *
- * Attention: Column names inside the call have to be quoted before hand
- *
- * Example:
- * <code>
- * $qb = $conn->getQueryBuilder();
- * $qb->select($qb->createFunction('COUNT(*)'))
- * ->from('users', 'u')
- * echo $qb->getSQL(); // SELECT COUNT(*) FROM `users` u
- * </code>
- * <code>
- * $qb = $conn->getQueryBuilder();
- * $qb->select($qb->createFunction('COUNT(`column`)'))
- * ->from('users', 'u')
- * echo $qb->getSQL(); // SELECT COUNT(`column`) FROM `users` u
- * </code>
- *
- * @param string $call
- *
- * @return IQueryFunction
- */
- public function createFunction($call) {
- return new QueryFunction($call);
- }
- /**
- * Used to get the id of the last inserted element
- * @return int
- * @throws \BadMethodCallException When being called before an insert query has been run.
- */
- public function getLastInsertId(): int {
- if ($this->getType() === \Doctrine\DBAL\Query\QueryBuilder::INSERT && $this->lastInsertedTable) {
- // lastInsertId() needs the prefix but no quotes
- $table = $this->prefixTableName($this->lastInsertedTable);
- return $this->connection->lastInsertId($table);
- }
- throw new \BadMethodCallException('Invalid call to getLastInsertId without using insert() before.');
- }
- /**
- * Returns the table name quoted and with database prefix as needed by the implementation
- *
- * @param string|IQueryFunction $table
- * @return string
- */
- public function getTableName($table) {
- if ($table instanceof IQueryFunction) {
- return (string) $table;
- }
- $table = $this->prefixTableName($table);
- return $this->helper->quoteColumnName($table);
- }
- /**
- * Returns the table name with database prefix as needed by the implementation
- *
- * @param string $table
- * @return string
- */
- protected function prefixTableName($table) {
- if ($this->automaticTablePrefix === false || strpos($table, '*PREFIX*') === 0) {
- return $table;
- }
- return '*PREFIX*' . $table;
- }
- /**
- * Returns the column name quoted and with table alias prefix as needed by the implementation
- *
- * @param string $column
- * @param string $tableAlias
- * @return string
- */
- public function getColumnName($column, $tableAlias = '') {
- if ($tableAlias !== '') {
- $tableAlias .= '.';
- }
- return $this->helper->quoteColumnName($tableAlias . $column);
- }
- /**
- * Returns the column name quoted and with table alias prefix as needed by the implementation
- *
- * @param string $alias
- * @return string
- */
- public function quoteAlias($alias) {
- if ($alias === '' || $alias === null) {
- return $alias;
- }
- return $this->helper->quoteColumnName($alias);
- }
- }
|