| blob | 9f870e82a5c6a282be92b85e9b5440e5137e708d |
1 <?php
2 // $Id: query.inc,v 1.42 2009/03/24 23:06:24 merlinofchaos Exp $
3 /**
4 * @file query.inc
5 * Defines the query object which is the underlying layer in a View.
6 */
8 /**
9 * Object used to create a SELECT query.
10 */
11 class views_query {
13 /**
14 * A list of tables in the order they should be added, keyed by alias.
15 */
16 var $table_queue = array();
18 /**
19 * Holds an array of tables and counts added so that we can create aliases
20 */
21 var $tables = array();
23 /**
24 * Holds an array of relationships, which are aliases of the primary
25 * table that represent different ways to join the same table in.
26 */
27 var $relationships = array();
29 /**
30 * An array of sections of the WHERE query. Each section is in itself
31 * an array of pieces and a flag as to whether or not it should be AND
32 * or OR.
33 */
34 var $where = array();
35 /**
36 * An array of sections of the HAVING query. Each section is in itself
37 * an array of pieces and a flag as to whether or not it should be AND
38 * or OR.
39 */
40 var $having = array();
41 /**
42 * The default operator to use when connecting the WHERE groups. May be
43 * AND or OR.
44 */
45 var $group_operator = 'AND';
47 /**
48 * A simple array of order by clauses.
49 */
50 var $orderby = array();
52 /**
53 * A simple array of group by clauses.
54 */
55 var $groupby = array();
57 /**
58 * The table header to use for tablesort. This matters because tablesort
59 * needs to modify the query and needs the header.
60 */
61 var $header = array();
63 /**
64 * A flag as to whether or not to make the primary field distinct.
65 */
66 var $distinct = FALSE;
68 /**
69 * Constructor; Create the basic query object and fill with default values.
70 */
71 function views_query($base_table = 'node', $base_field = 'nid') {
72 $this->base_table = $base_table; // Predefine these above, for clarity.
73 $this->base_field = $base_field;
74 $this->relationships[$base_table] = array(
75 'link' => NULL,
76 'table' => $base_table,
77 'alias' => $base_table,
78 'base' => $base_table
79 );
81 // init the table queue with our primary table.
82 $this->table_queue[$base_table] = array(
83 'alias' => $base_table,
84 'table' => $base_table,
85 'relationship' => $base_table,
86 'join' => NULL,
87 );
89 // init the tables with our primary table
90 $this->tables[$base_table][$base_table] = array(
91 'count' => 1,
92 'alias' => $base_table,
93 );
95 if ($base_field) {
96 $this->fields[$base_field] = array(
97 'table' => $base_table,
98 'field' => $base_field,
99 'alias' => $base_field,
100 );
101 }
103 $this->count_field = array(
104 'table' => $base_table,
105 'field' => $base_field,
106 'alias' => $base_field,
107 'count' => TRUE,
108 );
109 }
111 // ----------------------------------------------------------------
112 // Utility methods to set flags and data.
114 /**
115 * Set the base field to be distinct.
116 */
117 function set_distinct($value = TRUE) {
118 if (!(isset($this->no_distinct) && $value)) {
119 $this->distinct = $value;
120 }
121 }
123 /**
124 * Set what field the query will count() on for paging.
125 */
126 function set_count_field($table, $field, $alias = NULL) {
127 if (empty($alias)) {
128 $alias = $table . '_' . $field;
129 }
130 $this->count_field = array(
131 'table' => $table,
132 'field' => $field,
133 'alias' => $alias,
134 'count' => TRUE,
135 );
136 }
138 /**
139 * Set the table header; used for click-sorting because it's needed
140 * info to modify the ORDER BY clause.
141 */
142 function set_header($header) {
143 $this->header = $header;
144 }
146 // ----------------------------------------------------------------
147 // Table/join adding
149 /**
150 * A relationship is an alternative endpoint to a series of table
151 * joins. Relationships must be aliases of the primary table and
152 * they must join either to the primary table or to a pre-existing
153 * relationship.
154 *
155 * An example of a relationship would be a nodereference table.
156 * If you have a nodereference named 'book_parent' which links to a
157 * parent node, you could set up a relationship 'node_book_parent'
158 * to 'node'. Then, anything that links to 'node' can link to
159 * 'node_book_parent' instead, thus allowing all properties of
160 * both nodes to be available in the query.
161 *
162 * @param $alias
163 * What this relationship will be called, and is also the alias
164 * for the table.
165 * @param $join
166 * A views_join object (or derived object) to join the alias in.
167 * @param $base
168 * The name of the 'base' table this relationship represents; this
169 * tells the join search which path to attempt to use when finding
170 * the path to this relationship.
171 * @param $link_point
172 * If this relationship links to something other than the primary
173 * table, specify that table here. For example, a 'track' node
174 * might have a relationship to an 'album' node, which might
175 * have a relationship to an 'artist' node.
176 */
177 function add_relationship($alias, $join, $base, $link_point = NULL) {
178 if (empty($link_point)) {
179 $link_point = $this->base_table;
180 }
181 else if (!array_key_exists($link_point, $this->relationships)) {
182 return FALSE;
183 }
185 // Make sure $alias isn't already used; if it, start adding stuff.
186 $alias_base = $alias;
187 $count = 1;
188 while (!empty($this->relationships[$alias])) {
189 $alias = $alias_base . '_' . $count++;
190 }
192 // Add the table directly to the queue to avoid accidentally marking
193 // it.
194 $this->table_queue[$alias] = array(
195 'table' => $join->table,
196 'num' => 1,
197 'alias' => $alias,
198 'join' => $join,
199 'relationship' => $link_point,
200 );
202 $this->relationships[$alias] = array(
203 'link' => $link_point,
204 'table' => $join->table,
205 'base' => $base,
206 );
207 return $alias;
209 return $alias;
210 /*
211 if ($alias = $this->add_table($join->table, $link_point, $join, $alias)) {
212 }
213 */
214 }
216 /**
217 * Add a table to the query, ensuring the path exists.
218 *
219 * This function will test to ensure that the path back to the primary
220 * table is valid and exists; if you do not wish for this testing to
221 * occur, use $query->queue_table() instead.
222 *
223 * @param $table
224 * The name of the table to add. It needs to exist in the global table
225 * array.
226 * @param $relationship
227 * An alias of a table; if this is set, the path back to this table will
228 * be tested prior to adding the table, making sure that all intermediary
229 * tables exist and are properly aliased. If set to NULL the path to
230 * the primary table will be ensured. If the path cannot be made, the
231 * table will NOT be added.
232 * @param $join
233 * In some join configurations this table may actually join back through
234 * a different method; this is most likely to be used when tracing
235 * a hierarchy path. (node->parent->parent2->parent3). This parameter
236 * will specify how this table joins if it is not the default.
237 * @param $alias
238 * A specific alias to use, rather than the default alias.
239 *
240 * @return $alias
241 * The alias of the table; this alias can be used to access information
242 * about the table and should always be used to refer to the table when
243 * adding parts to the query. Or FALSE if the table was not able to be
244 * added.
245 */
246 function add_table($table, $relationship = NULL, $join = NULL, $alias = NULL) {
247 if (!$this->ensure_path($table, $relationship, $join)) {
248 return FALSE;
249 }
251 return $this->queue_table($table, $relationship, $this->adjust_join($join, $relationship), $alias);
252 }
254 /**
255 * Add a table to the query, without ensuring the path.
256 *
257 * This function will test to ensure that the path back to the primary
258 * table is valid and exists; if you do not wish for this testing to
259 * occur, use $query->queue_table() instead.
260 *
261 * @param $table
262 * The name of the table to add. It needs to exist in the global table
263 * array.
264 * @param $relationship
265 * The primary table alias this table is related to. If not set, the
266 * primary table will be used.
267 * @param $join
268 * In some join configurations this table may actually join back through
269 * a different method; this is most likely to be used when tracing
270 * a hierarchy path. (node->parent->parent2->parent3). This parameter
271 * will specify how this table joins if it is not the default.
272 * @param $alias
273 * A specific alias to use, rather than the default alias.
274 *
275 * @return $alias
276 * The alias of the table; this alias can be used to access information
277 * about the table and should always be used to refer to the table when
278 * adding parts to the query. Or FALSE if the table was not able to be
279 * added.
280 */
281 function queue_table($table, $relationship = NULL, $join = NULL, $alias = NULL) {
282 // If the alias is set, make sure it doesn't already exist.
283 if (isset($this->table_queue[$alias])) {
284 return $alias;
285 }
287 if (empty($relationship)) {
288 $relationship = $this->base_table;
289 }
291 if (!array_key_exists($relationship, $this->relationships)) {
292 return FALSE;
293 }
295 if (!$alias && $join && $relationship && !empty($join->adjusted) && $table != $join->table) {
296 $alias = $table;
297 }
299 // Check this again to make sure we don't blow up existing aliases for already
300 // adjusted joins.
301 if (isset($this->table_queue[$alias])) {
302 return $alias;
303 }
305 $alias = $this->mark_table($table, $relationship, $alias);
307 // If no alias is specified, give it the default.
308 if (!isset($alias)) {
309 $alias = $this->tables[$relationship][$table]['alias'] . $this->tables[$relationship][$table]['count'];
310 }
312 // If this is a relationship based table, add a marker with
313 // the relationship as a primary table for the alias.
314 if ($table != $alias) {
315 $this->mark_table($alias, $this->base_table, $alias);
316 }
318 // If no join is specified, pull it from the table data.
319 if (!isset($join)) {
320 $join = $this->get_join_data($table, $this->relationships[$relationship]['base']);
321 if (empty($join)) {
322 return FALSE;
323 }
325 $join = $this->adjust_join($join, $relationship);
326 }
328 $this->table_queue[$alias] = array(
329 'table' => $table,
330 'num' => $this->tables[$relationship][$table]['count'],
331 'alias' => $alias,
332 'join' => $join,
333 'relationship' => $relationship,
334 );
336 return $alias;
337 }
339 function mark_table($table, $relationship, $alias) {
340 // Mark that this table has been added.
341 if (empty($this->tables[$relationship][$table])) {
342 if (!isset($alias)) {
343 $alias = '';
344 if ($relationship != $this->base_table) {
345 // double underscore will help prevent accidental name
346 // space collisions.
347 $alias = $relationship . '__';
348 }
349 $alias .= $table;
350 }
351 $this->tables[$relationship][$table] = array(
352 'count' => 1,
353 'alias' => $alias,
354 );
355 }
356 else {
357 $this->tables[$relationship][$table]['count']++;
358 }
360 return $alias;
361 }
363 /**
364 * Ensure a table exists in the queue; if it already exists it won't
365 * do anything, but if it doesn't it will add the table queue. It will ensure
366 * a path leads back to the relationship table.
367 *
368 * @param $table
369 * The unaliased name of the table to ensure.
370 * @param $relationship
371 * The relationship to ensure the table links to. Each relationship will
372 * get a unique instance of the table being added. If not specified,
373 * will be the primary table.
374 * @param $join
375 * A views_join object (or derived object) to join the alias in.
376 *
377 * @return
378 * The alias used to refer to this specific table, or NULL if the table
379 * cannot be ensured.
380 */
381 function ensure_table($table, $relationship = NULL, $join = NULL) {
382 // ensure a relationship
383 if (empty($relationship)) {
384 $relationship = $this->base_table;
385 }
387 // If the relationship is the primary table, this actually be a relationship
388 // link back from an alias. We store all aliases along with the primary table
389 // to detect this state, because eventually it'll hit a table we already
390 // have and that's when we want to stop.
391 if ($relationship == $this->base_table && !empty($this->tables[$relationship][$table])) {
392 return $this->tables[$relationship][$table]['alias'];
393 }
395 if (!array_key_exists($relationship, $this->relationships)) {
396 return FALSE;
397 }
399 if ($table == $this->relationships[$relationship]['base']) {
400 return $relationship;
401 }
403 // If we do not have join info, fetch it.
404 if (!isset($join)) {
405 $join = $this->get_join_data($table, $this->relationships[$relationship]['base']);
406 }
408 // If it can't be fetched, this won't work.
409 if (empty($join)) {
410 return;
411 }
413 // Adjust this join for the relationship, which will ensure that the 'base'
414 // table it links to is correct. Tables adjoined to a relationship
415 // join to a link point, not the base table.
416 $join = $this->adjust_join($join, $relationship);
418 if ($this->ensure_path($table, $relationship, $join)) {
419 // Attempt to eliminate redundant joins. If this table's
420 // relationship and join exactly matches an existing table's
421 // relationship and join, we do not have to join to it again;
422 // just return the existing table's alias. See
423 // http://groups.drupal.org/node/11288 for details.
424 //
425 // This can be done safely here but not lower down in
426 // queue_table(), because queue_table() is also used by
427 // add_table() which requires the ability to intentionally add
428 // the same table with the same join multiple times. For
429 // example, a view that filters on 3 taxonomy terms using AND
430 // needs to join term_data 3 times with the same join.
432 // scan through the table queue to see if a matching join and
433 // relationship exists. If so, use it instead of this join.
435 // TODO: Scanning through $this->table_queue results in an
436 // O(N^2) algorithm, and this code runs every time the view is
437 // instantiated (Views 2 does not currently cache queries).
438 // There are a couple possible "improvements" but we should do
439 // some performance testing before picking one.
440 foreach ($this->table_queue as $queued_table) {
441 // In PHP 4 and 5, the == operation returns TRUE for two objects
442 // if they are instances of the same class and have the same
443 // attributes and values.
444 if ($queued_table['relationship'] == $relationship && $queued_table['join'] == $join) {
445 return $queued_table['alias'];
446 }
447 }
449 return $this->queue_table($table, $relationship, $join);
450 }
451 }
453 /**
454 * Make sure that the specified table can be properly linked to the primary
455 * table in the JOINs. This function uses recursion. If the tables
456 * needed to complete the path back to the primary table are not in the
457 * query they will be added, but additional copies will NOT be added
458 * if the table is already there.
459 */
460 function ensure_path($table, $relationship = NULL, $join = NULL, $traced = array(), $add = array()) {
461 if (!isset($relationship)) {
462 $relationship = $this->base_table;
463 }
465 if (!array_key_exists($relationship, $this->relationships)) {
466 return FALSE;
467 }
469 // If we do not have join info, fetch it.
470 if (!isset($join)) {
471 $join = $this->get_join_data($table, $this->relationships[$relationship]['base']);
472 }
474 // If it can't be fetched, this won't work.
475 if (empty($join)) {
476 return FALSE;
477 }
479 // Does a table along this path exist?
480 if (isset($this->tables[$relationship][$table]) ||
481 ($join && $join->left_table == $relationship) ||
482 ($join && $join->left_table == $this->relationships[$relationship]['table'])) {
484 // Make sure that we're linking to the correct table for our relationship.
485 foreach (array_reverse($add) as $table => $path_join) {
486 $this->queue_table($table, $relationship, $this->adjust_join($path_join, $relationship));
487 }
488 return TRUE;
489 }
491 // Have we been this way?
492 if (isset($traced[$join->left_table])) {
493 // we looped. Broked.
494 return FALSE;
495 }
497 // Do we have to add this table?
498 $left_join = $this->get_join_data($join->left_table, $this->relationships[$relationship]['base']);
499 if (!isset($this->tables[$relationship][$join->left_table])) {
500 $add[$join->left_table] = $left_join;
501 }
503 // Keep looking.
504 $traced[$join->left_table] = TRUE;
505 return $this->ensure_path($join->left_table, $relationship, $left_join, $traced, $add);
506 }
508 /**
509 * Fix a join to adhere to the proper relationship; the left table can vary
510 * based upon what relationship items are joined in on.
511 */
512 function adjust_join($join, $relationship) {
513 if (!empty($join->adjusted)) {
514 return $join;
515 }
517 if (empty($relationship) || empty($this->relationships[$relationship])) {
518 return $join;
519 }
521 // Adjusts the left table for our relationship.
522 if ($relationship != $this->base_table) {
523 // If we're linking to the primary table, the relationship to use will
524 // be the prior relationship. Unless it's a direct link.
526 // Safety! Don't modify an original here.
527 $join = drupal_clone($join);
529 // Do we need to try to ensure a path?
530 if ($join->left_table != $this->relationships[$relationship]['table'] &&
531 $join->left_table != $this->relationships[$relationship]['base'] &&
532 !isset($this->tables[$relationship][$join->left_table]['alias'])) {
533 $this->ensure_table($join->left_table, $relationship);
534 }
536 // First, if this is our link point/anchor table, just use the relationship
537 if ($join->left_table == $this->relationships[$relationship]['table']) {
538 $join->left_table = $relationship;
539 }
540 // then, try the base alias.
541 else if (isset($this->tables[$relationship][$join->left_table]['alias'])) {
542 $join->left_table = $this->tables[$relationship][$join->left_table]['alias'];
543 }
544 // But if we're already looking at an alias, use that instead.
545 else if (isset($this->table_queue[$relationship]['alias'])) {
546 $join->left_table = $this->table_queue[$relationship]['alias'];
547 }
548 }
550 $join->adjusted = TRUE;
551 return $join;
552 }
554 /**
555 * Retrieve join data from the larger join data cache.
556 *
557 * @param $table
558 * The table to get the join information for.
559 * @param $base_table
560 * The path we're following to get this join.
561 *
562 * @return
563 * A views_join object or child object, if one exists.
564 */
565 function get_join_data($table, $base_table) {
566 // Check to see if we're linking to a known alias. If so, get the real
567 // table's data instead.
568 if (!empty($this->table_queue[$table])) {
569 $table = $this->table_queue[$table]['table'];
570 }
571 return views_get_table_join($table, $base_table);
573 }
575 /**
576 * Get the information associated with a table.
577 *
578 * If you need the alias of a table with a particular relationship, use
579 * ensure_table().
580 */
581 function get_table_info($table) {
582 if (!empty($this->table_queue[$table])) {
583 return $this->table_queue[$table];
584 }
586 // In rare cases we might *only* have aliased versions of the table.
587 if (!empty($this->tables[$this->base_table][$table])) {
588 $alias = $this->tables[$this->base_table][$table]['alias'];
589 if (!empty($this->table_queue[$alias])) {
590 return $this->table_queue[$alias];
591 }
592 }
593 }
595 /**
596 * Add a field to the query table, possibly with an alias. This will
597 * automatically call ensure_table to make sure the required table
598 * exists, *unless* $table is unset.
599 *
600 * @param $table
601 * The table this field is attached to. If NULL, it is assumed this will
602 * be a formula; otherwise, ensure_table is used to make sure the
603 * table exists.
604 * @param $field
605 * The name of the field to add. This may be a real field or a formula.
606 * @param $alias
607 * The alias to create. If not specified, the alias will be $table_$field
608 * unless $table is NULL. When adding formulae, it is recommended that an
609 * alias be used.
610 *
611 * @return $name
612 * The name that this field can be referred to as. Usually this is the alias.
613 */
614 function add_field($table, $field, $alias = '', $params = NULL) {
615 // We check for this specifically because it gets a special alias.
616 if ($table == $this->base_table && $field == $this->base_field && empty($alias)) {
617 $alias = $this->base_field;
618 }
620 if ($table && empty($this->table_queue[$table])) {
621 $this->ensure_table($table);
622 }
624 if (!$alias && $table) {
625 $alias = $table . '_' . $field;
626 }
628 $name = $alias ? $alias : $field;
630 // @todo FIXME -- $alias, then $name is inconsistent
631 if (empty($this->fields[$alias])) {
632 $this->fields[$name] = array(
633 'field' => $field,
634 'table' => $table,
635 'alias' => $alias,
636 );
637 }
639 foreach ((array)$params as $key => $value) {
640 $this->fields[$name][$key] = $value;
641 }
643 return $name;
644 }
646 /**
647 * Remove all fields that may've been added; primarily used for summary
648 * mode where we're changing the query because we didn't get data we needed.
649 */
650 function clear_fields() {
651 $this->fields = array();
652 }
654 /**
655 * Create a new grouping for the WHERE or HAVING clause.
656 *
657 * @param $type
658 * Either 'AND' or 'OR'. All items within this group will be added
659 * to the WHERE clause with this logical operator.
660 * @param $group
661 * An ID to use for this group. If unspecified, an ID will be generated.
662 * @param $where
663 * 'where' or 'having'.
664 *
665 * @return $group
666 * The group ID generated.
667 */
668 function set_where_group($type = 'AND', $group = NULL, $where = 'where') {
669 // Set an alias.
670 $groups = &$this->$where;
672 if (!isset($group)) {
673 $group = max(array_keys($groups)) + 1;
674 }
676 // Create an empty group
677 if (empty($groups[$group])) {
678 $groups[$group] = array('clauses' => array(), 'args' => array());
679 }
681 $groups[$group]['type'] = strtoupper($type);
682 return $group;
683 }
685 /**
686 * Control how all WHERE and HAVING groups are put together.
687 *
688 * @param $type
689 * Either 'AND' or 'OR'
690 */
691 function set_group_operator($type = 'AND') {
692 $this->group_operator = strtoupper($type);
693 }
695 /**
696 * Add a simple WHERE clause to the query. The caller is responsible for
697 * ensuring that all fields are fully qualified (TABLE.FIELD) and that
698 * the table already exists in the query.
699 *
700 * @param $group
701 * The WHERE group to add these to; groups are used to create AND/OR
702 * sections. Groups cannot be nested. Use 0 as the default group.
703 * If the group does not yet exist it will be created as an AND group.
704 * @param $clause
705 * The actual clause to add. When adding a where clause it is important
706 * that all tables are addressed by the alias provided by add_table or
707 * ensure_table and that all fields are addressed by their alias wehn
708 * possible. Please use %d and %s for arguments.
709 * @param ...
710 * A number of arguments as used in db_query(). May be many args or one
711 * array full of args.
712 */
713 function add_where($group, $clause) {
714 $args = func_get_args();
715 array_shift($args); // ditch $group
716 array_shift($args); // ditch $clause
718 // Expand an array of args if it came in.
719 if (count($args) == 1 && is_array(reset($args))) {
720 $args = current($args);
721 }
723 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
724 // the default group.
725 if (empty($group)) {
726 $group = 0;
727 }
729 // Check for a group.
730 if (!isset($this->where[$group])) {
731 $this->set_where_group('AND', $group);
732 }
734 // Add the clause and the args.
735 if (is_array($args)) {
736 $this->where[$group]['clauses'][] = $clause;
737 // we use array_values() here to prevent array_merge errors as keys from multiple
738 // sources occasionally collide.
739 $this->where[$group]['args'] = array_merge($this->where[$group]['args'], array_values($args));
740 }
741 }
743 /**
744 * Add a simple HAVING clause to the query. The caller is responsible for
745 * ensuring that all fields are fully qualified (TABLE.FIELD) and that
746 * the table and an appropriate GROUP BY already exist in the query.
747 *
748 * @param $group
749 * The HAVING group to add these to; groups are used to create AND/OR
750 * sections. Groups cannot be nested. Use 0 as the default group.
751 * If the group does not yet exist it will be created as an AND group.
752 * @param $clause
753 * The actual clause to add. When adding a having clause it is important
754 * that all tables are addressed by the alias provided by add_table or
755 * ensure_table and that all fields are addressed by their alias wehn
756 * possible. Please use %d and %s for arguments.
757 * @param ...
758 * A number of arguments as used in db_query(). May be many args or one
759 * array full of args.
760 */
761 function add_having($group, $clause) {
762 $args = func_get_args();
763 array_shift($args); // ditch $group
764 array_shift($args); // ditch $clause
766 // Expand an array of args if it came in.
767 if (count($args) == 1 && is_array(reset($args))) {
768 $args = current($args);
769 }
771 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
772 // the default group.
773 if (empty($group)) {
774 $group = 0;
775 }
777 // Check for a group.
778 if (!isset($this->having[$group])) {
779 $this->set_where_group('AND', $group, 'having');
780 }
782 // Add the clause and the args.
783 if (is_array($args)) {
784 $this->having[$group]['clauses'][] = $clause;
785 $this->having[$group]['args'] = array_merge($this->having[$group]['args'], array_values($args));
786 }
787 }
789 /**
790 * Add an ORDER BY clause to the query.
791 *
792 * @param $table
793 * The table this field is part of. If a formula, enter NULL.
794 * @param $field
795 * The field or formula to sort on. If already a field, enter NULL
796 * and put in the alias.
797 * @param $order
798 * Either ASC or DESC.
799 * @param $alias
800 * The alias to add the field as. In SQL, all fields in the order by
801 * must also be in the SELECT portion. If an $alias isn't specified
802 * one will be generated for from the $field; however, if the
803 * $field is a formula, this alias will likely fail.
804 */
805 function add_orderby($table, $field, $order, $alias = '') {
806 if ($table) {
807 $this->ensure_table($table);
808 }
810 // Only fill out this aliasing if there is a table;
811 // otherwise we assume it is a formula.
812 if (!$alias && $table) {
813 $as = $table . '_' . $field;
814 }
815 else {
816 $as = $alias;
817 }
819 if ($field) {
820 $this->add_field($table, $field, $as);
821 }
823 $this->orderby[] = "$as " . strtoupper($order);
825 // If grouping, all items in the order by must also be in the
826 // group by clause. Check $table to ensure that this is not a
827 // formula.
828 if ($this->groupby && $table) {
829 $this->add_groupby($as);
830 }
831 }
833 /**
834 * Add a simple GROUP BY clause to the query. The caller is responsible
835 * for ensuring that the fields are fully qualified and the table is properly
836 * added.
837 */
838 function add_groupby($clause) {
839 // Only add it if it's not already in there.
840 if (!in_array($clause, $this->groupby)) {
841 $this->groupby[] = $clause;
842 }
843 }
845 /**
846 * Construct the "WHERE" or "HAVING" part of the query.
847 *
848 * @param $where
849 * 'where' or 'having'.
850 */
851 function condition_sql($where = 'where') {
852 $clauses = array();
853 foreach ($this->$where as $group => $info) {
854 $clause = implode(") " . $info['type'] . " (", $info['clauses']);
855 if (count($info['clauses']) > 1) {
856 $clause = '(' . $clause . ')';
857 }
858 $clauses[] = $clause;
859 }
861 if ($clauses) {
862 $keyword = drupal_strtoupper($where);
863 if (count($clauses) > 1) {
864 return "$keyword (" . implode(")\n " . $this->group_operator . ' (', $clauses) . ")\n";
865 }
866 else {
867 return "$keyword " . array_shift($clauses) . "\n";
868 }
869 }
870 return "";
871 }
873 /**
874 * Generate a query and a countquery from all of the information supplied
875 * to the object.
876 *
877 * @param $get_count
878 * Provide a countquery if this is true, otherwise provide a normal query.
879 */
880 function query($get_count = FALSE) {
881 // Check query distinct value.
882 if (empty($this->no_distinct) && $this->distinct && !empty($this->fields)) {
883 if (!empty($this->fields[$this->base_field])) {
884 $this->fields[$this->base_field]['distinct'] = TRUE;
885 }
886 }
888 /**
889 * An optimized count query includes just the base field instead of all the fields.
890 * Determine of this query qualifies by checking for a groupby or distinct.
891 */
892 $fields_array = $this->fields;
893 if ($get_count && !$this->groupby) {
894 foreach ($fields_array as $field) {
895 if (!empty($field['distinct'])) {
896 $get_count_optimized = FALSE;
897 break;
898 }
899 }
900 }
901 else {
902 $get_count_optimized = FALSE;
903 }
904 if (!isset($get_count_optimized)) {
905 $get_count_optimized = TRUE;
906 }
908 $joins = $fields = $where = $having = $orderby = $groupby = '';
909 // Add all the tables to the query via joins. We assume all LEFT joins.
910 foreach ($this->table_queue as $table) {
911 if (is_object($table['join'])) {
912 $joins .= $table['join']->join($table, $this) . "\n";
913 }
914 }
916 $has_aggregate = FALSE;
917 $non_aggregates = array();
919 foreach ($fields_array as $field) {
920 if ($fields) {
921 $fields .= ",\n ";
922 }
923 $string = '';
924 if (!empty($field['table'])) {
925 $string .= $field['table'] . '.';
926 }
927 $string .= $field['field'];
929 // store for use with non-aggregates below
930 $fieldname = (!empty($field['alias']) ? $field['alias'] : $string);
932 if (!empty($field['distinct'])) {
933 $string = "DISTINCT($string)";
934 }
935 if (!empty($field['count'])) {
936 $string = "COUNT($string)";
937 $has_aggregate = TRUE;
938 }
939 else if (!empty($field['aggregate'])) {
940 $has_aggregate = TRUE;
941 }
942 else {
943 $non_aggregates[] = $fieldname;
944 }
945 if ($field['alias']) {
946 $string .= " AS $field[alias]";
947 }
948 $fields .= $string;
950 if ($get_count_optimized) {
951 // We only want the first field in this case.
952 break;
953 }
954 }
956 if ($has_aggregate || $this->groupby) {
957 $groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";
958 if ($this->having) {
959 $having = $this->condition_sql('having');
960 }
961 }
963 if (!$get_count_optimized) {
964 // we only add the groupby if we're not counting.
965 if ($this->orderby) {
966 $orderby = "ORDER BY " . implode(', ', $this->orderby) . "\n";
967 }
968 }
970 $where = $this->condition_sql();
972 $query = "SELECT $fields\n FROM {" . $this->base_table . "} $this->base_table \n$joins $where $groupby $having $orderby";
974 $replace = array('>' => '>', '<' => '<');
975 $query = strtr($query, $replace);
977 return $query;
978 }
980 /**
981 * Get the arguments attached to the WHERE and HAVING clauses of this query.
982 */
983 function get_where_args() {
984 $args = array();
985 foreach ($this->where as $group => $where) {
986 $args = array_merge($args, $where['args']);
987 }
988 foreach ($this->having as $group => $having) {
989 $args = array_merge($args, $having['args']);
990 }
991 return $args;
992 }
993 }