QueryBuilder.php 38 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347
  1. <?php
  2. namespace Doctrine\DBAL\Query;
  3. use Doctrine\DBAL\Connection;
  4. use Doctrine\DBAL\Driver\Statement;
  5. use Doctrine\DBAL\ParameterType;
  6. use Doctrine\DBAL\Query\Expression\CompositeExpression;
  7. use Doctrine\DBAL\Query\Expression\ExpressionBuilder;
  8. use function array_key_exists;
  9. use function array_keys;
  10. use function array_unshift;
  11. use function func_get_args;
  12. use function func_num_args;
  13. use function implode;
  14. use function is_array;
  15. use function is_object;
  16. use function key;
  17. use function strtoupper;
  18. use function substr;
  19. /**
  20. * QueryBuilder class is responsible to dynamically create SQL queries.
  21. *
  22. * Important: Verify that every feature you use will work with your database vendor.
  23. * SQL Query Builder does not attempt to validate the generated SQL at all.
  24. *
  25. * The query builder does no validation whatsoever if certain features even work with the
  26. * underlying database vendor. Limit queries and joins are NOT applied to UPDATE and DELETE statements
  27. * even if some vendors such as MySQL support it.
  28. */
  29. class QueryBuilder
  30. {
  31. /*
  32. * The query types.
  33. */
  34. public const SELECT = 0;
  35. public const DELETE = 1;
  36. public const UPDATE = 2;
  37. public const INSERT = 3;
  38. /*
  39. * The builder states.
  40. */
  41. public const STATE_DIRTY = 0;
  42. public const STATE_CLEAN = 1;
  43. /**
  44. * The DBAL Connection.
  45. *
  46. * @var Connection
  47. */
  48. private $connection;
  49. /**
  50. * The array of SQL parts collected.
  51. *
  52. * @var mixed[]
  53. */
  54. private $sqlParts = [
  55. 'select' => [],
  56. 'from' => [],
  57. 'join' => [],
  58. 'set' => [],
  59. 'where' => null,
  60. 'groupBy' => [],
  61. 'having' => null,
  62. 'orderBy' => [],
  63. 'values' => [],
  64. ];
  65. /**
  66. * The complete SQL string for this query.
  67. *
  68. * @var string
  69. */
  70. private $sql;
  71. /**
  72. * The query parameters.
  73. *
  74. * @var mixed[]
  75. */
  76. private $params = [];
  77. /**
  78. * The parameter type map of this query.
  79. *
  80. * @var int[]|string[]
  81. */
  82. private $paramTypes = [];
  83. /**
  84. * The type of query this is. Can be select, update or delete.
  85. *
  86. * @var int
  87. */
  88. private $type = self::SELECT;
  89. /**
  90. * The state of the query object. Can be dirty or clean.
  91. *
  92. * @var int
  93. */
  94. private $state = self::STATE_CLEAN;
  95. /**
  96. * The index of the first result to retrieve.
  97. *
  98. * @var int
  99. */
  100. private $firstResult = null;
  101. /**
  102. * The maximum number of results to retrieve.
  103. *
  104. * @var int
  105. */
  106. private $maxResults = null;
  107. /**
  108. * The counter of bound parameters used with {@see bindValue).
  109. *
  110. * @var int
  111. */
  112. private $boundCounter = 0;
  113. /**
  114. * Initializes a new <tt>QueryBuilder</tt>.
  115. *
  116. * @param Connection $connection The DBAL Connection.
  117. */
  118. public function __construct(Connection $connection)
  119. {
  120. $this->connection = $connection;
  121. }
  122. /**
  123. * Gets an ExpressionBuilder used for object-oriented construction of query expressions.
  124. * This producer method is intended for convenient inline usage. Example:
  125. *
  126. * <code>
  127. * $qb = $conn->createQueryBuilder()
  128. * ->select('u')
  129. * ->from('users', 'u')
  130. * ->where($qb->expr()->eq('u.id', 1));
  131. * </code>
  132. *
  133. * For more complex expression construction, consider storing the expression
  134. * builder object in a local variable.
  135. *
  136. * @return ExpressionBuilder
  137. */
  138. public function expr()
  139. {
  140. return $this->connection->getExpressionBuilder();
  141. }
  142. /**
  143. * Gets the type of the currently built query.
  144. *
  145. * @return int
  146. */
  147. public function getType()
  148. {
  149. return $this->type;
  150. }
  151. /**
  152. * Gets the associated DBAL Connection for this query builder.
  153. *
  154. * @return Connection
  155. */
  156. public function getConnection()
  157. {
  158. return $this->connection;
  159. }
  160. /**
  161. * Gets the state of this query builder instance.
  162. *
  163. * @return int Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.
  164. */
  165. public function getState()
  166. {
  167. return $this->state;
  168. }
  169. /**
  170. * Executes this query using the bound parameters and their types.
  171. *
  172. * Uses {@see Connection::executeQuery} for select statements and {@see Connection::executeUpdate}
  173. * for insert, update and delete statements.
  174. *
  175. * @return Statement|int
  176. */
  177. public function execute()
  178. {
  179. if ($this->type === self::SELECT) {
  180. return $this->connection->executeQuery($this->getSQL(), $this->params, $this->paramTypes);
  181. }
  182. return $this->connection->executeUpdate($this->getSQL(), $this->params, $this->paramTypes);
  183. }
  184. /**
  185. * Gets the complete SQL string formed by the current specifications of this QueryBuilder.
  186. *
  187. * <code>
  188. * $qb = $em->createQueryBuilder()
  189. * ->select('u')
  190. * ->from('User', 'u')
  191. * echo $qb->getSQL(); // SELECT u FROM User u
  192. * </code>
  193. *
  194. * @return string The SQL query string.
  195. */
  196. public function getSQL()
  197. {
  198. if ($this->sql !== null && $this->state === self::STATE_CLEAN) {
  199. return $this->sql;
  200. }
  201. switch ($this->type) {
  202. case self::INSERT:
  203. $sql = $this->getSQLForInsert();
  204. break;
  205. case self::DELETE:
  206. $sql = $this->getSQLForDelete();
  207. break;
  208. case self::UPDATE:
  209. $sql = $this->getSQLForUpdate();
  210. break;
  211. case self::SELECT:
  212. default:
  213. $sql = $this->getSQLForSelect();
  214. break;
  215. }
  216. $this->state = self::STATE_CLEAN;
  217. $this->sql = $sql;
  218. return $sql;
  219. }
  220. /**
  221. * Sets a query parameter for the query being constructed.
  222. *
  223. * <code>
  224. * $qb = $conn->createQueryBuilder()
  225. * ->select('u')
  226. * ->from('users', 'u')
  227. * ->where('u.id = :user_id')
  228. * ->setParameter(':user_id', 1);
  229. * </code>
  230. *
  231. * @param string|int $key The parameter position or name.
  232. * @param mixed $value The parameter value.
  233. * @param string|int|null $type One of the {@link \Doctrine\DBAL\ParameterType} constants.
  234. *
  235. * @return $this This QueryBuilder instance.
  236. */
  237. public function setParameter($key, $value, $type = null)
  238. {
  239. if ($type !== null) {
  240. $this->paramTypes[$key] = $type;
  241. }
  242. $this->params[$key] = $value;
  243. return $this;
  244. }
  245. /**
  246. * Sets a collection of query parameters for the query being constructed.
  247. *
  248. * <code>
  249. * $qb = $conn->createQueryBuilder()
  250. * ->select('u')
  251. * ->from('users', 'u')
  252. * ->where('u.id = :user_id1 OR u.id = :user_id2')
  253. * ->setParameters(array(
  254. * ':user_id1' => 1,
  255. * ':user_id2' => 2
  256. * ));
  257. * </code>
  258. *
  259. * @param mixed[] $params The query parameters to set.
  260. * @param int[]|string[] $types The query parameters types to set.
  261. *
  262. * @return $this This QueryBuilder instance.
  263. */
  264. public function setParameters(array $params, array $types = [])
  265. {
  266. $this->paramTypes = $types;
  267. $this->params = $params;
  268. return $this;
  269. }
  270. /**
  271. * Gets all defined query parameters for the query being constructed indexed by parameter index or name.
  272. *
  273. * @return mixed[] The currently defined query parameters indexed by parameter index or name.
  274. */
  275. public function getParameters()
  276. {
  277. return $this->params;
  278. }
  279. /**
  280. * Gets a (previously set) query parameter of the query being constructed.
  281. *
  282. * @param mixed $key The key (index or name) of the bound parameter.
  283. *
  284. * @return mixed The value of the bound parameter.
  285. */
  286. public function getParameter($key)
  287. {
  288. return $this->params[$key] ?? null;
  289. }
  290. /**
  291. * Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
  292. *
  293. * @return int[]|string[] The currently defined query parameter types indexed by parameter index or name.
  294. */
  295. public function getParameterTypes()
  296. {
  297. return $this->paramTypes;
  298. }
  299. /**
  300. * Gets a (previously set) query parameter type of the query being constructed.
  301. *
  302. * @param mixed $key The key (index or name) of the bound parameter type.
  303. *
  304. * @return mixed The value of the bound parameter type.
  305. */
  306. public function getParameterType($key)
  307. {
  308. return $this->paramTypes[$key] ?? null;
  309. }
  310. /**
  311. * Sets the position of the first result to retrieve (the "offset").
  312. *
  313. * @param int $firstResult The first result to return.
  314. *
  315. * @return $this This QueryBuilder instance.
  316. */
  317. public function setFirstResult($firstResult)
  318. {
  319. $this->state = self::STATE_DIRTY;
  320. $this->firstResult = $firstResult;
  321. return $this;
  322. }
  323. /**
  324. * Gets the position of the first result the query object was set to retrieve (the "offset").
  325. * Returns NULL if {@link setFirstResult} was not applied to this QueryBuilder.
  326. *
  327. * @return int The position of the first result.
  328. */
  329. public function getFirstResult()
  330. {
  331. return $this->firstResult;
  332. }
  333. /**
  334. * Sets the maximum number of results to retrieve (the "limit").
  335. *
  336. * @param int $maxResults The maximum number of results to retrieve.
  337. *
  338. * @return $this This QueryBuilder instance.
  339. */
  340. public function setMaxResults($maxResults)
  341. {
  342. $this->state = self::STATE_DIRTY;
  343. $this->maxResults = $maxResults;
  344. return $this;
  345. }
  346. /**
  347. * Gets the maximum number of results the query object was set to retrieve (the "limit").
  348. * Returns NULL if {@link setMaxResults} was not applied to this query builder.
  349. *
  350. * @return int The maximum number of results.
  351. */
  352. public function getMaxResults()
  353. {
  354. return $this->maxResults;
  355. }
  356. /**
  357. * Either appends to or replaces a single, generic query part.
  358. *
  359. * The available parts are: 'select', 'from', 'set', 'where',
  360. * 'groupBy', 'having' and 'orderBy'.
  361. *
  362. * @param string $sqlPartName
  363. * @param string $sqlPart
  364. * @param bool $append
  365. *
  366. * @return $this This QueryBuilder instance.
  367. */
  368. public function add($sqlPartName, $sqlPart, $append = false)
  369. {
  370. $isArray = is_array($sqlPart);
  371. $isMultiple = is_array($this->sqlParts[$sqlPartName]);
  372. if ($isMultiple && ! $isArray) {
  373. $sqlPart = [$sqlPart];
  374. }
  375. $this->state = self::STATE_DIRTY;
  376. if ($append) {
  377. if ($sqlPartName === 'orderBy' || $sqlPartName === 'groupBy' || $sqlPartName === 'select' || $sqlPartName === 'set') {
  378. foreach ($sqlPart as $part) {
  379. $this->sqlParts[$sqlPartName][] = $part;
  380. }
  381. } elseif ($isArray && is_array($sqlPart[key($sqlPart)])) {
  382. $key = key($sqlPart);
  383. $this->sqlParts[$sqlPartName][$key][] = $sqlPart[$key];
  384. } elseif ($isMultiple) {
  385. $this->sqlParts[$sqlPartName][] = $sqlPart;
  386. } else {
  387. $this->sqlParts[$sqlPartName] = $sqlPart;
  388. }
  389. return $this;
  390. }
  391. $this->sqlParts[$sqlPartName] = $sqlPart;
  392. return $this;
  393. }
  394. /**
  395. * Specifies an item that is to be returned in the query result.
  396. * Replaces any previously specified selections, if any.
  397. *
  398. * <code>
  399. * $qb = $conn->createQueryBuilder()
  400. * ->select('u.id', 'p.id')
  401. * ->from('users', 'u')
  402. * ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');
  403. * </code>
  404. *
  405. * @param mixed $select The selection expressions.
  406. *
  407. * @return $this This QueryBuilder instance.
  408. */
  409. public function select($select = null)
  410. {
  411. $this->type = self::SELECT;
  412. if (empty($select)) {
  413. return $this;
  414. }
  415. $selects = is_array($select) ? $select : func_get_args();
  416. return $this->add('select', $selects);
  417. }
  418. /**
  419. * Adds an item that is to be returned in the query result.
  420. *
  421. * <code>
  422. * $qb = $conn->createQueryBuilder()
  423. * ->select('u.id')
  424. * ->addSelect('p.id')
  425. * ->from('users', 'u')
  426. * ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');
  427. * </code>
  428. *
  429. * @param mixed $select The selection expression.
  430. *
  431. * @return $this This QueryBuilder instance.
  432. */
  433. public function addSelect($select = null)
  434. {
  435. $this->type = self::SELECT;
  436. if (empty($select)) {
  437. return $this;
  438. }
  439. $selects = is_array($select) ? $select : func_get_args();
  440. return $this->add('select', $selects, true);
  441. }
  442. /**
  443. * Turns the query being built into a bulk delete query that ranges over
  444. * a certain table.
  445. *
  446. * <code>
  447. * $qb = $conn->createQueryBuilder()
  448. * ->delete('users', 'u')
  449. * ->where('u.id = :user_id');
  450. * ->setParameter(':user_id', 1);
  451. * </code>
  452. *
  453. * @param string $delete The table whose rows are subject to the deletion.
  454. * @param string $alias The table alias used in the constructed query.
  455. *
  456. * @return $this This QueryBuilder instance.
  457. */
  458. public function delete($delete = null, $alias = null)
  459. {
  460. $this->type = self::DELETE;
  461. if (! $delete) {
  462. return $this;
  463. }
  464. return $this->add('from', [
  465. 'table' => $delete,
  466. 'alias' => $alias,
  467. ]);
  468. }
  469. /**
  470. * Turns the query being built into a bulk update query that ranges over
  471. * a certain table
  472. *
  473. * <code>
  474. * $qb = $conn->createQueryBuilder()
  475. * ->update('counters', 'c')
  476. * ->set('c.value', 'c.value + 1')
  477. * ->where('c.id = ?');
  478. * </code>
  479. *
  480. * @param string $update The table whose rows are subject to the update.
  481. * @param string $alias The table alias used in the constructed query.
  482. *
  483. * @return $this This QueryBuilder instance.
  484. */
  485. public function update($update = null, $alias = null)
  486. {
  487. $this->type = self::UPDATE;
  488. if (! $update) {
  489. return $this;
  490. }
  491. return $this->add('from', [
  492. 'table' => $update,
  493. 'alias' => $alias,
  494. ]);
  495. }
  496. /**
  497. * Turns the query being built into an insert query that inserts into
  498. * a certain table
  499. *
  500. * <code>
  501. * $qb = $conn->createQueryBuilder()
  502. * ->insert('users')
  503. * ->values(
  504. * array(
  505. * 'name' => '?',
  506. * 'password' => '?'
  507. * )
  508. * );
  509. * </code>
  510. *
  511. * @param string $insert The table into which the rows should be inserted.
  512. *
  513. * @return $this This QueryBuilder instance.
  514. */
  515. public function insert($insert = null)
  516. {
  517. $this->type = self::INSERT;
  518. if (! $insert) {
  519. return $this;
  520. }
  521. return $this->add('from', ['table' => $insert]);
  522. }
  523. /**
  524. * Creates and adds a query root corresponding to the table identified by the
  525. * given alias, forming a cartesian product with any existing query roots.
  526. *
  527. * <code>
  528. * $qb = $conn->createQueryBuilder()
  529. * ->select('u.id')
  530. * ->from('users', 'u')
  531. * </code>
  532. *
  533. * @param string $from The table.
  534. * @param string|null $alias The alias of the table.
  535. *
  536. * @return $this This QueryBuilder instance.
  537. */
  538. public function from($from, $alias = null)
  539. {
  540. return $this->add('from', [
  541. 'table' => $from,
  542. 'alias' => $alias,
  543. ], true);
  544. }
  545. /**
  546. * Creates and adds a join to the query.
  547. *
  548. * <code>
  549. * $qb = $conn->createQueryBuilder()
  550. * ->select('u.name')
  551. * ->from('users', 'u')
  552. * ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1');
  553. * </code>
  554. *
  555. * @param string $fromAlias The alias that points to a from clause.
  556. * @param string $join The table name to join.
  557. * @param string $alias The alias of the join table.
  558. * @param string $condition The condition for the join.
  559. *
  560. * @return $this This QueryBuilder instance.
  561. */
  562. public function join($fromAlias, $join, $alias, $condition = null)
  563. {
  564. return $this->innerJoin($fromAlias, $join, $alias, $condition);
  565. }
  566. /**
  567. * Creates and adds a join to the query.
  568. *
  569. * <code>
  570. * $qb = $conn->createQueryBuilder()
  571. * ->select('u.name')
  572. * ->from('users', 'u')
  573. * ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
  574. * </code>
  575. *
  576. * @param string $fromAlias The alias that points to a from clause.
  577. * @param string $join The table name to join.
  578. * @param string $alias The alias of the join table.
  579. * @param string $condition The condition for the join.
  580. *
  581. * @return $this This QueryBuilder instance.
  582. */
  583. public function innerJoin($fromAlias, $join, $alias, $condition = null)
  584. {
  585. return $this->add('join', [
  586. $fromAlias => [
  587. 'joinType' => 'inner',
  588. 'joinTable' => $join,
  589. 'joinAlias' => $alias,
  590. 'joinCondition' => $condition,
  591. ],
  592. ], true);
  593. }
  594. /**
  595. * Creates and adds a left join to the query.
  596. *
  597. * <code>
  598. * $qb = $conn->createQueryBuilder()
  599. * ->select('u.name')
  600. * ->from('users', 'u')
  601. * ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
  602. * </code>
  603. *
  604. * @param string $fromAlias The alias that points to a from clause.
  605. * @param string $join The table name to join.
  606. * @param string $alias The alias of the join table.
  607. * @param string $condition The condition for the join.
  608. *
  609. * @return $this This QueryBuilder instance.
  610. */
  611. public function leftJoin($fromAlias, $join, $alias, $condition = null)
  612. {
  613. return $this->add('join', [
  614. $fromAlias => [
  615. 'joinType' => 'left',
  616. 'joinTable' => $join,
  617. 'joinAlias' => $alias,
  618. 'joinCondition' => $condition,
  619. ],
  620. ], true);
  621. }
  622. /**
  623. * Creates and adds a right join to the query.
  624. *
  625. * <code>
  626. * $qb = $conn->createQueryBuilder()
  627. * ->select('u.name')
  628. * ->from('users', 'u')
  629. * ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
  630. * </code>
  631. *
  632. * @param string $fromAlias The alias that points to a from clause.
  633. * @param string $join The table name to join.
  634. * @param string $alias The alias of the join table.
  635. * @param string $condition The condition for the join.
  636. *
  637. * @return $this This QueryBuilder instance.
  638. */
  639. public function rightJoin($fromAlias, $join, $alias, $condition = null)
  640. {
  641. return $this->add('join', [
  642. $fromAlias => [
  643. 'joinType' => 'right',
  644. 'joinTable' => $join,
  645. 'joinAlias' => $alias,
  646. 'joinCondition' => $condition,
  647. ],
  648. ], true);
  649. }
  650. /**
  651. * Sets a new value for a column in a bulk update query.
  652. *
  653. * <code>
  654. * $qb = $conn->createQueryBuilder()
  655. * ->update('counters', 'c')
  656. * ->set('c.value', 'c.value + 1')
  657. * ->where('c.id = ?');
  658. * </code>
  659. *
  660. * @param string $key The column to set.
  661. * @param string $value The value, expression, placeholder, etc.
  662. *
  663. * @return $this This QueryBuilder instance.
  664. */
  665. public function set($key, $value)
  666. {
  667. return $this->add('set', $key . ' = ' . $value, true);
  668. }
  669. /**
  670. * Specifies one or more restrictions to the query result.
  671. * Replaces any previously specified restrictions, if any.
  672. *
  673. * <code>
  674. * $qb = $conn->createQueryBuilder()
  675. * ->select('c.value')
  676. * ->from('counters', 'c')
  677. * ->where('c.id = ?');
  678. *
  679. * // You can optionally programatically build and/or expressions
  680. * $qb = $conn->createQueryBuilder();
  681. *
  682. * $or = $qb->expr()->orx();
  683. * $or->add($qb->expr()->eq('c.id', 1));
  684. * $or->add($qb->expr()->eq('c.id', 2));
  685. *
  686. * $qb->update('counters', 'c')
  687. * ->set('c.value', 'c.value + 1')
  688. * ->where($or);
  689. * </code>
  690. *
  691. * @param mixed $predicates The restriction predicates.
  692. *
  693. * @return $this This QueryBuilder instance.
  694. */
  695. public function where($predicates)
  696. {
  697. if (! (func_num_args() === 1 && $predicates instanceof CompositeExpression)) {
  698. $predicates = new CompositeExpression(CompositeExpression::TYPE_AND, func_get_args());
  699. }
  700. return $this->add('where', $predicates);
  701. }
  702. /**
  703. * Adds one or more restrictions to the query results, forming a logical
  704. * conjunction with any previously specified restrictions.
  705. *
  706. * <code>
  707. * $qb = $conn->createQueryBuilder()
  708. * ->select('u')
  709. * ->from('users', 'u')
  710. * ->where('u.username LIKE ?')
  711. * ->andWhere('u.is_active = 1');
  712. * </code>
  713. *
  714. * @see where()
  715. *
  716. * @param mixed $where The query restrictions.
  717. *
  718. * @return $this This QueryBuilder instance.
  719. */
  720. public function andWhere($where)
  721. {
  722. $args = func_get_args();
  723. $where = $this->getQueryPart('where');
  724. if ($where instanceof CompositeExpression && $where->getType() === CompositeExpression::TYPE_AND) {
  725. $where->addMultiple($args);
  726. } else {
  727. array_unshift($args, $where);
  728. $where = new CompositeExpression(CompositeExpression::TYPE_AND, $args);
  729. }
  730. return $this->add('where', $where, true);
  731. }
  732. /**
  733. * Adds one or more restrictions to the query results, forming a logical
  734. * disjunction with any previously specified restrictions.
  735. *
  736. * <code>
  737. * $qb = $em->createQueryBuilder()
  738. * ->select('u.name')
  739. * ->from('users', 'u')
  740. * ->where('u.id = 1')
  741. * ->orWhere('u.id = 2');
  742. * </code>
  743. *
  744. * @see where()
  745. *
  746. * @param mixed $where The WHERE statement.
  747. *
  748. * @return $this This QueryBuilder instance.
  749. */
  750. public function orWhere($where)
  751. {
  752. $args = func_get_args();
  753. $where = $this->getQueryPart('where');
  754. if ($where instanceof CompositeExpression && $where->getType() === CompositeExpression::TYPE_OR) {
  755. $where->addMultiple($args);
  756. } else {
  757. array_unshift($args, $where);
  758. $where = new CompositeExpression(CompositeExpression::TYPE_OR, $args);
  759. }
  760. return $this->add('where', $where, true);
  761. }
  762. /**
  763. * Specifies a grouping over the results of the query.
  764. * Replaces any previously specified groupings, if any.
  765. *
  766. * <code>
  767. * $qb = $conn->createQueryBuilder()
  768. * ->select('u.name')
  769. * ->from('users', 'u')
  770. * ->groupBy('u.id');
  771. * </code>
  772. *
  773. * @param mixed $groupBy The grouping expression.
  774. *
  775. * @return $this This QueryBuilder instance.
  776. */
  777. public function groupBy($groupBy)
  778. {
  779. if (empty($groupBy)) {
  780. return $this;
  781. }
  782. $groupBy = is_array($groupBy) ? $groupBy : func_get_args();
  783. return $this->add('groupBy', $groupBy, false);
  784. }
  785. /**
  786. * Adds a grouping expression to the query.
  787. *
  788. * <code>
  789. * $qb = $conn->createQueryBuilder()
  790. * ->select('u.name')
  791. * ->from('users', 'u')
  792. * ->groupBy('u.lastLogin');
  793. * ->addGroupBy('u.createdAt')
  794. * </code>
  795. *
  796. * @param mixed $groupBy The grouping expression.
  797. *
  798. * @return $this This QueryBuilder instance.
  799. */
  800. public function addGroupBy($groupBy)
  801. {
  802. if (empty($groupBy)) {
  803. return $this;
  804. }
  805. $groupBy = is_array($groupBy) ? $groupBy : func_get_args();
  806. return $this->add('groupBy', $groupBy, true);
  807. }
  808. /**
  809. * Sets a value for a column in an insert query.
  810. *
  811. * <code>
  812. * $qb = $conn->createQueryBuilder()
  813. * ->insert('users')
  814. * ->values(
  815. * array(
  816. * 'name' => '?'
  817. * )
  818. * )
  819. * ->setValue('password', '?');
  820. * </code>
  821. *
  822. * @param string $column The column into which the value should be inserted.
  823. * @param string $value The value that should be inserted into the column.
  824. *
  825. * @return $this This QueryBuilder instance.
  826. */
  827. public function setValue($column, $value)
  828. {
  829. $this->sqlParts['values'][$column] = $value;
  830. return $this;
  831. }
  832. /**
  833. * Specifies values for an insert query indexed by column names.
  834. * Replaces any previous values, if any.
  835. *
  836. * <code>
  837. * $qb = $conn->createQueryBuilder()
  838. * ->insert('users')
  839. * ->values(
  840. * array(
  841. * 'name' => '?',
  842. * 'password' => '?'
  843. * )
  844. * );
  845. * </code>
  846. *
  847. * @param mixed[] $values The values to specify for the insert query indexed by column names.
  848. *
  849. * @return $this This QueryBuilder instance.
  850. */
  851. public function values(array $values)
  852. {
  853. return $this->add('values', $values);
  854. }
  855. /**
  856. * Specifies a restriction over the groups of the query.
  857. * Replaces any previous having restrictions, if any.
  858. *
  859. * @param mixed $having The restriction over the groups.
  860. *
  861. * @return $this This QueryBuilder instance.
  862. */
  863. public function having($having)
  864. {
  865. if (! (func_num_args() === 1 && $having instanceof CompositeExpression)) {
  866. $having = new CompositeExpression(CompositeExpression::TYPE_AND, func_get_args());
  867. }
  868. return $this->add('having', $having);
  869. }
  870. /**
  871. * Adds a restriction over the groups of the query, forming a logical
  872. * conjunction with any existing having restrictions.
  873. *
  874. * @param mixed $having The restriction to append.
  875. *
  876. * @return $this This QueryBuilder instance.
  877. */
  878. public function andHaving($having)
  879. {
  880. $args = func_get_args();
  881. $having = $this->getQueryPart('having');
  882. if ($having instanceof CompositeExpression && $having->getType() === CompositeExpression::TYPE_AND) {
  883. $having->addMultiple($args);
  884. } else {
  885. array_unshift($args, $having);
  886. $having = new CompositeExpression(CompositeExpression::TYPE_AND, $args);
  887. }
  888. return $this->add('having', $having);
  889. }
  890. /**
  891. * Adds a restriction over the groups of the query, forming a logical
  892. * disjunction with any existing having restrictions.
  893. *
  894. * @param mixed $having The restriction to add.
  895. *
  896. * @return $this This QueryBuilder instance.
  897. */
  898. public function orHaving($having)
  899. {
  900. $args = func_get_args();
  901. $having = $this->getQueryPart('having');
  902. if ($having instanceof CompositeExpression && $having->getType() === CompositeExpression::TYPE_OR) {
  903. $having->addMultiple($args);
  904. } else {
  905. array_unshift($args, $having);
  906. $having = new CompositeExpression(CompositeExpression::TYPE_OR, $args);
  907. }
  908. return $this->add('having', $having);
  909. }
  910. /**
  911. * Specifies an ordering for the query results.
  912. * Replaces any previously specified orderings, if any.
  913. *
  914. * @param string $sort The ordering expression.
  915. * @param string $order The ordering direction.
  916. *
  917. * @return $this This QueryBuilder instance.
  918. */
  919. public function orderBy($sort, $order = null)
  920. {
  921. return $this->add('orderBy', $sort . ' ' . (! $order ? 'ASC' : $order), false);
  922. }
  923. /**
  924. * Adds an ordering to the query results.
  925. *
  926. * @param string $sort The ordering expression.
  927. * @param string $order The ordering direction.
  928. *
  929. * @return $this This QueryBuilder instance.
  930. */
  931. public function addOrderBy($sort, $order = null)
  932. {
  933. return $this->add('orderBy', $sort . ' ' . (! $order ? 'ASC' : $order), true);
  934. }
  935. /**
  936. * Gets a query part by its name.
  937. *
  938. * @param string $queryPartName
  939. *
  940. * @return mixed
  941. */
  942. public function getQueryPart($queryPartName)
  943. {
  944. return $this->sqlParts[$queryPartName];
  945. }
  946. /**
  947. * Gets all query parts.
  948. *
  949. * @return mixed[]
  950. */
  951. public function getQueryParts()
  952. {
  953. return $this->sqlParts;
  954. }
  955. /**
  956. * Resets SQL parts.
  957. *
  958. * @param string[]|null $queryPartNames
  959. *
  960. * @return $this This QueryBuilder instance.
  961. */
  962. public function resetQueryParts($queryPartNames = null)
  963. {
  964. if ($queryPartNames === null) {
  965. $queryPartNames = array_keys($this->sqlParts);
  966. }
  967. foreach ($queryPartNames as $queryPartName) {
  968. $this->resetQueryPart($queryPartName);
  969. }
  970. return $this;
  971. }
  972. /**
  973. * Resets a single SQL part.
  974. *
  975. * @param string $queryPartName
  976. *
  977. * @return $this This QueryBuilder instance.
  978. */
  979. public function resetQueryPart($queryPartName)
  980. {
  981. $this->sqlParts[$queryPartName] = is_array($this->sqlParts[$queryPartName])
  982. ? [] : null;
  983. $this->state = self::STATE_DIRTY;
  984. return $this;
  985. }
  986. /**
  987. * @return string
  988. *
  989. * @throws QueryException
  990. */
  991. private function getSQLForSelect()
  992. {
  993. $query = 'SELECT ' . implode(', ', $this->sqlParts['select']);
  994. $query .= ($this->sqlParts['from'] ? ' FROM ' . implode(', ', $this->getFromClauses()) : '')
  995. . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : '')
  996. . ($this->sqlParts['groupBy'] ? ' GROUP BY ' . implode(', ', $this->sqlParts['groupBy']) : '')
  997. . ($this->sqlParts['having'] !== null ? ' HAVING ' . ((string) $this->sqlParts['having']) : '')
  998. . ($this->sqlParts['orderBy'] ? ' ORDER BY ' . implode(', ', $this->sqlParts['orderBy']) : '');
  999. if ($this->isLimitQuery()) {
  1000. return $this->connection->getDatabasePlatform()->modifyLimitQuery(
  1001. $query,
  1002. $this->maxResults,
  1003. $this->firstResult
  1004. );
  1005. }
  1006. return $query;
  1007. }
  1008. /**
  1009. * @return string[]
  1010. */
  1011. private function getFromClauses()
  1012. {
  1013. $fromClauses = [];
  1014. $knownAliases = [];
  1015. // Loop through all FROM clauses
  1016. foreach ($this->sqlParts['from'] as $from) {
  1017. if ($from['alias'] === null) {
  1018. $tableSql = $from['table'];
  1019. $tableReference = $from['table'];
  1020. } else {
  1021. $tableSql = $from['table'] . ' ' . $from['alias'];
  1022. $tableReference = $from['alias'];
  1023. }
  1024. $knownAliases[$tableReference] = true;
  1025. $fromClauses[$tableReference] = $tableSql . $this->getSQLForJoins($tableReference, $knownAliases);
  1026. }
  1027. $this->verifyAllAliasesAreKnown($knownAliases);
  1028. return $fromClauses;
  1029. }
  1030. /**
  1031. * @param string[] $knownAliases
  1032. *
  1033. * @throws QueryException
  1034. */
  1035. private function verifyAllAliasesAreKnown(array $knownAliases)
  1036. {
  1037. foreach ($this->sqlParts['join'] as $fromAlias => $joins) {
  1038. if (! isset($knownAliases[$fromAlias])) {
  1039. throw QueryException::unknownAlias($fromAlias, array_keys($knownAliases));
  1040. }
  1041. }
  1042. }
  1043. /**
  1044. * @return bool
  1045. */
  1046. private function isLimitQuery()
  1047. {
  1048. return $this->maxResults !== null || $this->firstResult !== null;
  1049. }
  1050. /**
  1051. * Converts this instance into an INSERT string in SQL.
  1052. *
  1053. * @return string
  1054. */
  1055. private function getSQLForInsert()
  1056. {
  1057. return 'INSERT INTO ' . $this->sqlParts['from']['table'] .
  1058. ' (' . implode(', ', array_keys($this->sqlParts['values'])) . ')' .
  1059. ' VALUES(' . implode(', ', $this->sqlParts['values']) . ')';
  1060. }
  1061. /**
  1062. * Converts this instance into an UPDATE string in SQL.
  1063. *
  1064. * @return string
  1065. */
  1066. private function getSQLForUpdate()
  1067. {
  1068. $table = $this->sqlParts['from']['table'] . ($this->sqlParts['from']['alias'] ? ' ' . $this->sqlParts['from']['alias'] : '');
  1069. return 'UPDATE ' . $table
  1070. . ' SET ' . implode(', ', $this->sqlParts['set'])
  1071. . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : '');
  1072. }
  1073. /**
  1074. * Converts this instance into a DELETE string in SQL.
  1075. *
  1076. * @return string
  1077. */
  1078. private function getSQLForDelete()
  1079. {
  1080. $table = $this->sqlParts['from']['table'] . ($this->sqlParts['from']['alias'] ? ' ' . $this->sqlParts['from']['alias'] : '');
  1081. return 'DELETE FROM ' . $table . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : '');
  1082. }
  1083. /**
  1084. * Gets a string representation of this QueryBuilder which corresponds to
  1085. * the final SQL query being constructed.
  1086. *
  1087. * @return string The string representation of this QueryBuilder.
  1088. */
  1089. public function __toString()
  1090. {
  1091. return $this->getSQL();
  1092. }
  1093. /**
  1094. * Creates a new named parameter and bind the value $value to it.
  1095. *
  1096. * This method provides a shortcut for PDOStatement::bindValue
  1097. * when using prepared statements.
  1098. *
  1099. * The parameter $value specifies the value that you want to bind. If
  1100. * $placeholder is not provided bindValue() will automatically create a
  1101. * placeholder for you. An automatic placeholder will be of the name
  1102. * ':dcValue1', ':dcValue2' etc.
  1103. *
  1104. * For more information see {@link http://php.net/pdostatement-bindparam}
  1105. *
  1106. * Example:
  1107. * <code>
  1108. * $value = 2;
  1109. * $q->eq( 'id', $q->bindValue( $value ) );
  1110. * $stmt = $q->executeQuery(); // executed with 'id = 2'
  1111. * </code>
  1112. *
  1113. * @link http://www.zetacomponents.org
  1114. *
  1115. * @param mixed $value
  1116. * @param mixed $type
  1117. * @param string $placeHolder The name to bind with. The string must start with a colon ':'.
  1118. *
  1119. * @return string the placeholder name used.
  1120. */
  1121. public function createNamedParameter($value, $type = ParameterType::STRING, $placeHolder = null)
  1122. {
  1123. if ($placeHolder === null) {
  1124. $this->boundCounter++;
  1125. $placeHolder = ':dcValue' . $this->boundCounter;
  1126. }
  1127. $this->setParameter(substr($placeHolder, 1), $value, $type);
  1128. return $placeHolder;
  1129. }
  1130. /**
  1131. * Creates a new positional parameter and bind the given value to it.
  1132. *
  1133. * Attention: If you are using positional parameters with the query builder you have
  1134. * to be very careful to bind all parameters in the order they appear in the SQL
  1135. * statement , otherwise they get bound in the wrong order which can lead to serious
  1136. * bugs in your code.
  1137. *
  1138. * Example:
  1139. * <code>
  1140. * $qb = $conn->createQueryBuilder();
  1141. * $qb->select('u.*')
  1142. * ->from('users', 'u')
  1143. * ->where('u.username = ' . $qb->createPositionalParameter('Foo', ParameterType::STRING))
  1144. * ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', ParameterType::STRING))
  1145. * </code>
  1146. *
  1147. * @param mixed $value
  1148. * @param int $type
  1149. *
  1150. * @return string
  1151. */
  1152. public function createPositionalParameter($value, $type = ParameterType::STRING)
  1153. {
  1154. $this->boundCounter++;
  1155. $this->setParameter($this->boundCounter, $value, $type);
  1156. return '?';
  1157. }
  1158. /**
  1159. * @param string $fromAlias
  1160. * @param string[] $knownAliases
  1161. *
  1162. * @return string
  1163. *
  1164. * @throws QueryException
  1165. */
  1166. private function getSQLForJoins($fromAlias, array &$knownAliases)
  1167. {
  1168. $sql = '';
  1169. if (isset($this->sqlParts['join'][$fromAlias])) {
  1170. foreach ($this->sqlParts['join'][$fromAlias] as $join) {
  1171. if (array_key_exists($join['joinAlias'], $knownAliases)) {
  1172. throw QueryException::nonUniqueAlias($join['joinAlias'], array_keys($knownAliases));
  1173. }
  1174. $sql .= ' ' . strtoupper($join['joinType'])
  1175. . ' JOIN ' . $join['joinTable'] . ' ' . $join['joinAlias']
  1176. . ' ON ' . ((string) $join['joinCondition']);
  1177. $knownAliases[$join['joinAlias']] = true;
  1178. }
  1179. foreach ($this->sqlParts['join'][$fromAlias] as $join) {
  1180. $sql .= $this->getSQLForJoins($join['joinAlias'], $knownAliases);
  1181. }
  1182. }
  1183. return $sql;
  1184. }
  1185. /**
  1186. * Deep clone of all expression objects in the SQL parts.
  1187. *
  1188. * @return void
  1189. */
  1190. public function __clone()
  1191. {
  1192. foreach ($this->sqlParts as $part => $elements) {
  1193. if (is_array($this->sqlParts[$part])) {
  1194. foreach ($this->sqlParts[$part] as $idx => $element) {
  1195. if (! is_object($element)) {
  1196. continue;
  1197. }
  1198. $this->sqlParts[$part][$idx] = clone $element;
  1199. }
  1200. } elseif (is_object($elements)) {
  1201. $this->sqlParts[$part] = clone $elements;
  1202. }
  1203. }
  1204. foreach ($this->params as $name => $param) {
  1205. if (! is_object($param)) {
  1206. continue;
  1207. }
  1208. $this->params[$name] = clone $param;
  1209. }
  1210. }
  1211. }