1. <?php
2. /**
3. * File containing the ezcQuerySelect class.
4. *
5. * @package Database
6. * @version 1.4
7. * @copyright Copyright (C) 2005-2008 eZ systems as. All rights reserved.
8. * @license http://ez.no/licenses/new_bsd New BSD License
9. */
10.
11. /**
12. * Class to create select database independent SELECT queries.
13. *
14. * Note that this class creates queries that are syntactically independent
15. * of database. Semantically the queries still differ and so the same
16. * query may produce different results on different databases. Such
17. * differences are noted throughout the documentation of this class.
18. *
19. * This class implements SQL92. If your database differs from the SQL92
20. * implementation extend this class and reimplement the methods that produce
21. * different results. Some methods implemented in ezcQuery are not defined by SQL92.
22. * These methods are marked and ezcQuery will return MySQL syntax for these cases.
23. *
24. * The examples show the SQL generated by this class.
25. * Database specific implementations may produce different results.
26. *
27. * Example:
28. * <code>
29. * $q = ezcDbInstance::get()->createSelectQuery();
30. * $q->select( '*' )->from( 'Greetings' )
31. * ->where( $q->expr->gt( 'age', 10 ),
32. * $q->expr->eq( 'greeting', $q->bindValue( 'Hello world' ) ) )
33. * ->orderBy( 'owner' )
34. * ->limit( 10 );
35. * $stmt = $q->prepare(); // $stmt is a normal PDOStatement
36. * $stmt->execute();
37. * </code>
38. *
39. * Database independence:
40. * TRUE/FALSE, MySQL accepts 0 and 1 as boolean values. PostgreSQL does not, but accepts TRUE/FALSE.
41. * @todo introduction needs examples with clone(), reusing a query and advanced binding.
42. * @package Database
43. * @version 1.4
44. * @mainclass
45. */
46. class ezcQuerySelect extends ezcQuery
47. {
48. /**
49. * Sort the result ascending.
50. */
51. const ASC = 'ASC';
52.
53. /**
54. * Sort the result descending.
55. */
56. const DESC = 'DESC';
57.
58. /**
59. * Stores the SELECT part of the SQL.
60. *
61. * Everything from 'SELECT' until 'FROM' is stored.
62. * @var string
63. */
64. protected $selectString = null;
65.
66. /**
67. * Stores the FROM part of the SQL.
68. *
69. * Everything from 'FROM' until 'WHERE' is stored.
70. * @var string
71. */
72. protected $fromString = null;
73.
74. /**
75. * Stores the WHERE part of the SQL.
76. *
77. * Everything from 'WHERE' until 'GROUP', 'LIMIT', 'ORDER' or 'SORT' is stored.
78. * @var string
79. */
80. protected $whereString = null;
81.
82. /**
83. * Stores the GROUP BY part of the SQL.
84. *
85. * @var string
86. */
87. protected $groupString = null;
88.
89. /**
90. * Stores the HAVING part of SQL
91. *
92. * @var string
93. */
94. protected $havingString = null;
95.
96. /**
97. * Stores the ORDER BY part of the SQL.
98. *
99. * @var string
100. */
101. protected $orderString = null;
102.
103. /**
104. * Stores the LIMIT part of the SQL.
105. *
106. * @var string
107. */
108. protected $limitString = null;
109.
110. /**
111. * Stores the name of last invoked SQL clause method.
112. *
113. * Could be 'select', 'from', 'where', 'group', 'having', 'order', 'limit'
114. * @var string
115. */
116. protected $lastInvokedMethod = null;
117.
118. /**
119. * Constructs a new ezcQuery object.
120. *
121. * For an introduction to aliases see {@link ezcQuery::__construct()}.
122. *
123. * @param PDO $db a pointer to the database object.
124. * @param array(string=>string) $aliases
125. */
126. public function __construct( PDO $db, array $aliases = array() )
127. {
128. parent::__construct( $db, $aliases );
129. }
130.
131. /**
132. * Resets the query object for reuse.
133. *
134. * @return void
135. */
136. public function reset()
137. {
138. $this->selectString = null;
139. $this->fromString = null;
140. $this->whereString = null;
141. $this->groupString = null;
142. $this->havingString = null;
143. $this->orderString = null;
144. $this->limitString = null;
145. $this->lastInvokedClauseMethod = null;
146.
147. $this->boundCounter = 0;
148. $this->boundValues = array();
149. }
150.
151. /**
152. * Opens the query and selects which columns you want to return with
153. * the query.
154. *
155. * select() accepts an arbitrary number of parameters. Each parameter
156. * must contain either the name of a column or an array containing
157. * the names of the columns.
158. * Each call to select() appends columns to the list of columns that will be
159. * used in the query.
160. *
161. * Example:
162. * <code>
163. * $q->select( 'column1', 'column2' );
164. * </code>
165. * The same could also be written
166. * <code>
167. * $columns[] = 'column1';
168. * $columns[] = 'column2;
169. * $q->select( $columns );
170. * </code>
171. * or using several calls
172. * <code>
173. * $q->select( 'column1' )->select( 'column2' );
174. * </code>
175. *
176. * Each of above code produce SQL clause 'SELECT column1, column2' for the query.
177. *
178. * @throws ezcQueryVariableParameterException if called with no parameters..
179. * @param string|array(string)$... Either a string with a column name or an array of column names.
180. * @return ezcQuery returns a pointer to $this.
181. */
182. public function select()
183. {
184. if ( $this->selectString == null )
185. {
186. $this->selectString = 'SELECT ';
187. }
188.
189. $args = func_get_args();
190. $cols = self::arrayFlatten( $args );
191.
192. if ( count( $cols ) < 1 )
193. {
194. throw new ezcQueryVariableParameterException( 'select', count( $args ), 1 );
195. }
196. $this->lastInvokedMethod = 'select';
197. $cols = $this->getIdentifiers( $cols );
198.
199. // glue string should be inserted each time but not before first entry
200. if ( ( $this->selectString !== 'SELECT ' ) &&
201. ( $this->selectString !== 'SELECT DISTINCT ' ) )
202. {
203. $this->selectString .= ', ';
204. }
205.
206. $this->selectString .= join( ', ', $cols );
207. return $this;
208. }
209.
210. /**
211. * Returns SQL to create an alias
212. *
213. * This method can be used to create an alias for either a
214. * table or a column.
215. * Example:
216. * <code>
217. * // this will make the table users have the alias employees
218. * // and the column user_id the alias employee_id
219. * $q->select( $q->alias( 'user_id', 'employee_id' )
220. * ->from( $q->alias( 'users', 'employees' ) );
221. * </code>
222. *
223. * @param string $name
224. * @param string $alias
225. * @return string the query string "columnname as targetname"
226. */
227. public function alias( $name, $alias )
228. {
229. $name = $this->getIdentifier( $name );
230. return "{$name} AS {$alias}";
231. }
232.
233. /**
234. * Opens the query and uses a distinct select on the columns you want to
235. * return with the query.
236. *
237. * selectDistinct() accepts an arbitrary number of parameters. Each
238. * parameter must contain either the name of a column or an array
239. * containing the names of the columns.
240. * Each call to selectDistinct() appends columns to the list of columns
241. * that will be used in the query.
242. *
243. * Example:
244. * <code>
245. * $q->selectDistinct( 'column1', 'column2' );
246. * </code>
247. * The same could also be written
248. * <code>
249. * $columns[] = 'column1';
250. * $columns[] = 'column2;
251. * $q->selectDistinct( $columns );
252. * </code>
253. * or using several calls
254. * <code>
255. * $q->selectDistinct( 'column1' )->select( 'column2' );
256. * </code>
257. *
258. * Each of above code produce SQL clause 'SELECT DISTINCT column1, column2'
259. * for the query.
260. *
261. * You may call select() after calling selectDistinct() which will result
262. * in the additional columns beein added. A call of selectDistinct() after
263. * select() will result in an ezcQueryInvalidException.
264. *
265. * @throws ezcQueryVariableParameterException if called with no parameters..
266. * @throws ezcQueryInvalidException if called after select()
267. * @param string|array(string)$... Either a string with a column name or an array of column names.
268. * @return ezcQuery returns a pointer to $this.
269. */
270. public function selectDistinct()
271. {
272. if ( $this->selectString == null )
273. {
274. $this->selectString = 'SELECT DISTINCT ';
275. }
276. elseif ( strpos ( $this->selectString, 'DISTINCT' ) === false )
277. {
278. throw new ezcQueryInvalidException(
279. 'SELECT',
280. 'You can\'t use selectDistinct() after using select() in the same query.'
281. );
282. }
283.
284. // Call ezcQuerySelect::select() to do the parameter processing
285. $args = func_get_args();
286. return call_user_func_array(
287. array( $this, 'select' ),
288. $args
289. );
290. }
291.
292. /**
293. * Select which tables you want to select from.
294. *
295. * from() accepts an arbitrary number of parameters. Each parameter
296. * must contain either the name of a table or an array containing
297. * the names of tables..
298. * Each call to from() appends tables to the list of tables that will be
299. * used in the query.
300. *
301. * Example:
302. * <code>
303. * // the following code will produce the SQL
304. * // SELECT id FROM table_name
305. * $q->select( 'id' )->from( 'table_name' );
306. * </code>
307. *
308. * @throws ezcQueryVariableParameterException if called with no parameters.
309. * @param string|array(string)$... Either a string with a table name or an array of table names.
310. * @return ezcQuery a pointer to $this
311. */
312. public function from()
313. {
314. if ( $this->fromString == '' )
315. {
316. $this->fromString = 'FROM ';
317. }
318.
319. $args = func_get_args();
320. $tables = self::arrayFlatten( $args );
321. if ( count( $tables ) < 1 )
322. {
323. throw new ezcQueryVariableParameterException( 'from', count( $args ), 1 );
324. }
325. $this->lastInvokedMethod = 'from';
326. $tables = $this->getIdentifiers( $tables );
327.
328. // glue string should be inserted each time but not before first entry
329. if ( $this->fromString != 'FROM ' )
330. {
331. $this->fromString .= ', ';
332. }
333.
334. $this->fromString .= join( ', ', $tables );
335. return $this;
336. }
337.
338. /**
339. * Returns the SQL for a join or prepares $fromString for a join.
340. *
341. * This method could be used in two forms:
342. *
343. * <b>doJoin( $joinType, 't2', $joinCondition )</b>
344. *
345. * Takes the join type and two string arguments and returns ezcQuery.
346. *
347. * The second parameter is the name of the table to join with. The table to
348. * which is joined should have been previously set with the from() method.
349. *
350. * The third parameter should be a string containing a join condition that
351. * is returned by an ezcQueryExpression.
352. *
353. * <b>doJoin( $joinType, 't2', 't1.id', 't2.id' )</b>
354. *
355. * Takes the join type and three string arguments and returns ezcQuery.
356. * This is a simplified form of the three parameter version. doJoin(
357. * 'inner', 't2', 't1.id', 't2.id' ) is equal to doJoin( 'inner', 't2',
358. * $this->expr->eq('t1.id', 't2.id' ) );
359. *
360. * The second parameter is the name of the table to join with. The table to
361. * which is joined should have been previously set with the from() method.
362. *
363. * The third parameter is the name of the column on the table set
364. * previously with the from() method and the fourth parameter the name of
365. * the column to join with on the table that was specified in the first
366. * parameter.
367. *
368. * @apichange Remove "5" argument version.
369. *
370. * @throws ezcQueryInvalidException if called with inconsistent parameters or if
371. * invoked without preceding call to from().
372. *
373. * @param string $type The join type: inner, right or left.
374. * @param string $table2,... The table to join with, followed by either the
375. * two join columns, or a join condition.
376. * @return ezcQuery
377. */
378. protected function doJoin( $type )
379. {
380. $args = func_get_args();
381. // Remove the first one, as that's the $type of join.
382. array_shift( $args );
383.
384. $sqlType = strtoupper( $type );
385.
386. $passedArgsCount = func_num_args() - 1;
387. if ( $passedArgsCount < 2 || $passedArgsCount > 4 )
388. {
389. throw new ezcQueryInvalidException( 'SELECT', "Wrong argument count passed to {$type}Join(): {$passedArgsCount}" );
390. }
391.
392. // deprecated syntax
393. if ( $passedArgsCount == 4 )
394. {
395. if ( is_string( $args[0] ) && is_string( $args[1] ) &&
396. is_string( $args[2] ) && is_string( $args[3] )
397. )
398. {
399. $table1 = $this->getIdentifier( $args[0] );
400. $table2 = $this->getIdentifier( $args[1] );
401. $column1 = $this->getIdentifier( $args[2] );
402. $column2 = $this->getIdentifier( $args[3] );
403.
404. return "{$table1} {$sqlType} JOIN {$table2} ON {$column1} = {$column2}";
405. }
406. else
407. {
408. throw new ezcQueryInvalidException( 'SELECT', "Inconsistent types of arguments passed to {$type}Join()." );
409. }
410. }
411.
412. // using from()->*Join() syntax assumed, so check if last call was to from()
413. if ( $this->lastInvokedMethod != 'from' )
414. {
415. throw new ezcQueryInvalidException( 'SELECT', "Invoking {$type}Join() not immediately after from()." );
416. }
417.
418. $table = '';
419. if ( !is_string( $args[0] ) )
420. {
421. throw new ezcQueryInvalidException( 'SELECT',
422. "Inconsistent type of first argument passed to {$type}Join(). Should be string with name of table." );
423. }
424. $table = $this->getIdentifier( $args[0] );
425.
426. $condition = '';
427. if ( $passedArgsCount == 2 && is_string( $args[1] ) )
428. {
429. $condition = $args[1];
430. }
431. else if ( $passedArgsCount == 3 && is_string( $args[1] ) && is_string( $args[2] ) )
432. {
433. $arg1 = $this->getIdentifier( $args[1] );
434. $arg2 = $this->getIdentifier( $args[2] );
435.
436. $condition = "{$arg1} = {$arg2}";
437. }
438.
439. $this->fromString .= " {$sqlType} JOIN {$table} ON {$condition}";
440. return $this;
441. }
442.
443. /**
444. * Returns the SQL for an inner join or prepares $fromString for an inner join.
445. *
446. * This method could be used in two forms:
447. *
448. * <b>innerJoin( 't2', $joinCondition )</b>
449. *
450. * Takes 2 string arguments and returns ezcQuery.
451. *
452. * The first parameter is the name of the table to join with. The table to
453. * which is joined should have been previously set with the from() method.
454. *
455. * The second parameter should be a string containing a join condition that
456. * is returned by an ezcQueryExpression.
457. *
458. * Example:
459. * <code>
460. * // the following code will produce the SQL
461. * // SELECT id FROM t1 INNER JOIN t2 ON t1.id = t2.id
462. * $q->select( 'id' )->from( 't1' )->innerJoin( 't2', $q->expr->eq('t1.id', 't2.id' ) );
463. * </code>
464. *
465. * <b>innerJoin( 't2', 't1.id', 't2.id' )</b>
466. *
467. * Takes 3 string arguments and returns ezcQuery. This is a simplified form
468. * of the 2 parameter version. innerJoin( 't2', 't1.id', 't2.id' ) is
469. * equal to innerJoin( 't2', $this->expr->eq('t1.id', 't2.id' ) );
470. *
471. * The first parameter is the name of the table to join with. The table to
472. * which is joined should have been previously set with the from() method.
473. *
474. * The second parameter is the name of the column on the table set
475. * previously with the from() method and the third parameter the name of
476. * the column to join with on the table that was specified in the first
477. * parameter.
478. *
479. * Example:
480. * <code>
481. * // the following code will produce the SQL
482. * // SELECT id FROM t1 INNER JOIN t2 ON t1.id = t2.id
483. * $q->select( 'id' )->from( 't1' )->innerJoin( 't2', 't1.id', 't2.id' );
484. * </code>
485. *
486. * @apichange Remove 4 argument version.
487. *
488. * @throws ezcQueryInvalidException if called with inconsistent parameters or if
489. * invoked without preceding call to from().
490. *
491. * @param string $table2,... The table to join with, followed by either the
492. * two join columns, or a join condition.
493. * @return ezcQuery
494. */
495. public function innerJoin()
496. {
497. $args = func_get_args();
498. array_unshift( $args, 'inner' );
499. return call_user_func_array( array( $this, 'doJoin' ), $args );
500. }
501.
502. /**
503. * Returns the SQL for a left join or prepares $fromString for a left join.
504. *
505. * This method could be used in two forms:
506. *
507. * <b>leftJoin( 't2', $joinCondition )</b>
508. *
509. * Takes 2 string arguments and returns ezcQuery.
510. *
511. * The first parameter is the name of the table to join with. The table to
512. * which is joined should have been previously set with the from() method.
513. *
514. * The second parameter should be a string containing a join condition that
515. * is returned by an ezcQueryExpression.
516. *
517. * Example:
518. * <code>
519. * // the following code will produce the SQL
520. * // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
521. * $q->select( 'id' )->from( 't1' )->leftJoin( 't2', $q->expr->eq('t1.id', 't2.id' ) );
522. * </code>
523. *
524. * <b>leftJoin( 't2', 't1.id', 't2.id' )</b>
525. *
526. * Takes 3 string arguments and returns ezcQuery. This is a simplified form
527. * of the 2 parameter version. leftJoin( 't2', 't1.id', 't2.id' ) is
528. * equal to leftJoin( 't2', $this->expr->eq('t1.id', 't2.id' ) );
529. *
530. * The first parameter is the name of the table to join with. The table to
531. * which is joined should have been previously set with the from() method.
532. *
533. * The second parameter is the name of the column on the table set
534. * previously with the from() method and the third parameter the name of
535. * the column to join with on the table that was specified in the first
536. * parameter.
537. *
538. * Example:
539. * <code>
540. * // the following code will produce the SQL
541. * // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
542. * $q->select( 'id' )->from( 't1' )->leftJoin( 't2', 't1.id', 't2.id' );
543. * </code>
544. *
545. * @apichange Remove 4 argument version.
546. *
547. * @throws ezcQueryInvalidException if called with inconsistent parameters or if
548. * invoked without preceding call to from().
549. *
550. * @param string $table2,... The table to join with, followed by either the
551. * two join columns, or a join condition.
552. * @return ezcQuery
553. */
554. public function leftJoin()
555. {
556. $args = func_get_args();
557. array_unshift( $args, 'left' );
558. return call_user_func_array( array( $this, 'doJoin' ), $args );
559. }
560.
561. /**
562. * Returns the SQL for a right join or prepares $fromString for a right join.
563. *
564. * This method could be used in two forms:
565. *
566. * <b>rightJoin( 't2', $joinCondition )</b>
567. *
568. * Takes 2 string arguments and returns ezcQuery.
569. *
570. * The first parameter is the name of the table to join with. The table to
571. * which is joined should have been previously set with the from() method.
572. *
573. * The second parameter should be a string containing a join condition that
574. * is returned by an ezcQueryExpression.
575. *
576. * Example:
577. * <code>
578. * // the following code will produce the SQL
579. * // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
580. * $q->select( 'id' )->from( 't1' )->rightJoin( 't2', $q->expr->eq('t1.id', 't2.id' ) );
581. * </code>
582. *
583. * <b>rightJoin( 't2', 't1.id', 't2.id' )</b>
584. *
585. * Takes 3 string arguments and returns ezcQuery. This is a simplified form
586. * of the 2 parameter version. rightJoin( 't2', 't1.id', 't2.id' ) is
587. * equal to rightJoin( 't2', $this->expr->eq('t1.id', 't2.id' ) );
588. *
589. * The first parameter is the name of the table to join with. The table to
590. * which is joined should have been previously set with the from() method.
591. *
592. * The second parameter is the name of the column on the table set
593. * previously with the from() method and the third parameter the name of
594. * the column to join with on the table that was specified in the first
595. * parameter.
596. *
597. * Example:
598. * <code>
599. * // the following code will produce the SQL
600. * // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
601. * $q->select( 'id' )->from( 't1' )->rightJoin( 't2', 't1.id', 't2.id' );
602. * </code>
603. *
604. * @apichange Remove 4 argument version.
605. *
606. * @throws ezcQueryInvalidException if called with inconsistent parameters or if
607. * invoked without preceding call to from().
608. *
609. * @param string $table2,... The table to join with, followed by either the
610. * two join columns, or a join condition.
611. * @return ezcQuery
612. */
613. public function rightJoin()
614. {
615. $args = func_get_args();
616. array_unshift( $args, 'right' );
617. return call_user_func_array( array( $this, 'doJoin' ), $args );
618. }
619.
620. /**
621. * Adds a where clause with logical expressions to the query.
622. *
623. * where() accepts an arbitrary number of parameters. Each parameter
624. * must contain a logical expression or an array with logical expressions.
625. * If you specify multiple logical expression they are connected using
626. * a logical and.
627. *
628. * Multiple calls to where() will join the expressions using a logical and.
629. *
630. * Example:
631. * <code>
632. * $q->select( '*' )->from( 'table' )->where( $q->expr->eq( 'id', 1 ) );
633. * </code>
634. *
635. * @throws ezcQueryVariableParameterException if called with no parameters.
636. * @param string|array(string)$... Either a string with a logical expression name
637. * or an array with logical expressions.
638. * @return ezcQuerySelect
639. */
640. public function where()
641. {
642. if ( $this->whereString == null )
643. {
644. $this->whereString = 'WHERE ';
645. }
646.
647. $args = func_get_args();
648. $expressions = self::arrayFlatten( $args );
649. if ( count( $expressions ) < 1 )
650. {
651. throw new ezcQueryVariableParameterException( 'where', count( $args ), 1 );
652. }
653.
654. $this->lastInvokedMethod = 'where';
655.
656. // glue string should be inserted each time but not before first entry
657. if ( $this->whereString != 'WHERE ' )
658. {
659. $this->whereString .= ' AND ';
660. }
661.
662. $this->whereString .= join( ' AND ', $expressions );
663. return $this;
664. }
665.
666.
667. // limit, order and group
668.
668.
669. /**
670. * Returns SQL that limits the result set.
671. *
672. * $limit controls the maximum number of rows that will be returned.
673. * $offset controls which row that will be the first in the result
674. * set from the total amount of matching rows.
675. *
676. * Example:
677. * <code>
678. * $q->select( '*' )->from( 'table' )
679. * ->limit( 10, 0 );
680. * </code>
681. *
682. * LIMIT is not part of SQL92. It is implemented here anyway since all
683. * databases support it one way or the other and because it is
684. * essential.
685. *
686. * @param string $limit integer expression
687. * @param string $offset integer expression
688. * @return ezcQuerySelect
689. */
690. public function limit( $limit, $offset = '' )
691. {
692. if ( $offset === '' )
693. {
694. $this->limitString = "LIMIT {$limit}";
695. }
696. else
697. {
698. $this->limitString = "LIMIT {$limit} OFFSET {$offset}";
699. }
700. $this->lastInvokedMethod = 'limit';
701.
702. return $this;
703. }
704.
705. /**
706. * Returns SQL that orders the result set by a given column.
707. *
708. * You can call orderBy multiple times. Each call will add a
709. * column to order by.
710. *
711. * Example:
712. * <code>
713. * $q->select( '*' )->from( 'table' )
714. * ->orderBy( 'id' );
715. * </code>
716. *
717. * @param string $column a column name in the result set
718. * @param string $type if the column should be sorted ascending or descending.
719. * you can specify this using ezcQuerySelect::ASC or ezcQuerySelect::DESC
720. * @return ezcQuery a pointer to $this
721. */
722. public function orderBy( $column, $type = self::ASC )
723. {
724. $string = $this->getIdentifier( $column );
725. if ( $type == self::DESC )
726. {
727. $string .= ' DESC';
728. }
729. if ( $this->orderString == '' )
730. {
731. $this->orderString = "ORDER BY {$string}";
732. }
733. else
734. {
735. $this->orderString .= ", {$string}";
736. }
737. $this->lastInvokedMethod = 'order';
738.
739. return $this;
740. }
741.
742. /**
743. * Returns SQL that groups the result set by a given column.
744. *
745. * You can call groupBy multiple times. Each call will add a
746. * column to group by.
747. *
748. * Example:
749. * <code>
750. * $q->select( '*' )->from( 'table' )
751. * ->groupBy( 'id' );
752. * </code>
753. *
754. * @throws ezcQueryVariableParameterException if called with no parameters.
755. * @param string $column a column name in the result set
756. * @return ezcQuery a pointer to $this
757. */
758. public function groupBy()
759. {
760. $args = func_get_args();
761. $columns = self::arrayFlatten( $args );
762. if ( count( $columns ) < 1 )
763. {
764. throw new ezcQueryVariableParameterException( 'groupBy', count( $args ), 1 );
765. }
766. $columns = $this->getIdentifiers( $columns );
767.
768. $string = join( ', ', $columns );
769. if ( $this->groupString == '' )
770. {
771. $this->groupString = "GROUP BY {$string}" ;
772. }
773. else
774. {
775. $this->groupString .= ", {$string}";
776. }
777.
778. $this->lastInvokedMethod = 'group';
779.
780. return $this;
781. }
782.
783. /**
784. * Returns SQL that set having by a given expression.
785. *
786. * You can call having multiple times. Each call will add an
787. * expression with a logical and.
788. *
789. * Example:
790. * <code>
791. * $q->select( '*' )->from( 'table' )->groupBy( 'id' )
792. * ->having( $q->expr->eq('id',1) );
793. * </code>
794. *
795. * @throws ezcQueryInvalidException
796. * if invoked without preceding call to groupBy().
797. * @throws ezcQueryVariableParameterException
798. * if called with no parameters.
799. * @param string|array(string)$... Either a string with a logical expression name
800. * or an array with logical expressions.
801. * @return ezcQuery a pointer to $this
802. */
803. public function having()
804. {
805. // using groupBy()->having() syntax assumed, so check if last call was to groupBy()
806. if ( $this->lastInvokedMethod != 'group' && $this->lastInvokedMethod != 'having' )
807. {
808. throw new ezcQueryInvalidException( 'SELECT', 'Invoking having() not immediately after groupBy().' );
809. }
810.
811. $args = func_get_args();
812. $expressions = self::arrayFlatten( $args );
813. if ( count( $expressions ) < 1 )
814. {
815. throw new ezcQueryVariableParameterException( 'having', count( $args ), 1 );
816. }
817.
818. if ( $this->havingString == null )
819. {
820. $this->havingString = 'HAVING ';
821. }
822.
823. // will add "AND expression" in subsequent calls to having()
824. if ( $this->havingString != 'HAVING ' )
825. {
826. $this->havingString .= ' AND ';
827. }
828.
829. $this->havingString .= join( ' AND ', $expressions );
830. $this->lastInvokedMethod = 'having';
831. return $this;
832. }
833.
834. /**
835. * Returns dummy table name.
836. *
837. * If your select query just evaluates an expression
838. * without fetching table data (e.g. 'SELECT 1+1')
839. * some databases require you to specify a dummy table in FROM clause.
840. * (Oracle: 'SELECT 1+1 FROM dual').
841. *
842. * This methods returns name of such a dummy table.
843. * For DBMSs that don't require that, the method returns false.
844. * Otherwise the dummy table name is returned.
845. *
846. * @return bool|stringa dummy table name or false if not needed
847. */
848. static public function getDummyTableName()
849. {
850. return false;
851. }
852.
853. /**
854. * Returns the complete select query string.
855. *
856. * This method uses the build methods to build the
857. * various parts of the select query.
858. *
859. * @todo add newlines? easier for debugging
860. * @throws ezcQueryInvalidException if it was not possible to build a valid query.
861. * @return string
862. */
863. public function getQuery()
864. {
865. if ( $this->selectString == null )
866. {
867. throw new ezcQueryInvalidException( "SELECT", "select() was not called before getQuery()." );
868. }
869.
870. $query = "{$this->selectString}";
871. if ( $this->fromString != null )
872. {
873. $query = "{$query} {$this->fromString}";
874. }
875. if ( $this->whereString != null )
876. {
877. $query = "{$query} {$this->whereString}";
878. }
879. if ( $this->groupString != null )
880. {
881. $query = "{$query} {$this->groupString}";
882. }
883. if ( $this->havingString != null )
884. {
885. $query = "{$query} {$this->havingString}";
886. }
887. if ( $this->orderString != null )
888. {
889. $query = "{$query} {$this->orderString}";
890. }
891. if ( $this->limitString != null )
892. {
893. $query = "{$query} {$this->limitString}";
894. }
895. return $query;
896. }
897.
898. }
899.
