| [ Index ] |
PHP Cross Reference of Drupal 6 (gatewave) |
[Summary view] [Print] [Text view]
1 <?php 2 // $Id: query.inc,v 1.50.2.4 2009/12/02 22:59:18 dww Exp $ 3 /** 4 * @file query.inc 5 * Defines the query object which is the underlying layer in a View. 6 */ 7 8 /** 9 * Object used to create a SELECT query. 10 */ 11 class views_query { 12 13 /** 14 * A list of tables in the order they should be added, keyed by alias. 15 */ 16 var $table_queue = array(); 17 18 /** 19 * Holds an array of tables and counts added so that we can create aliases 20 */ 21 var $tables = array(); 22 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(); 28 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'; 46 47 /** 48 * A simple array of order by clauses. 49 */ 50 var $orderby = array(); 51 52 /** 53 * A simple array of group by clauses. 54 */ 55 var $groupby = array(); 56 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(); 62 63 /** 64 * A flag as to whether or not to make the primary field distinct. 65 */ 66 var $distinct = FALSE; 67 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 ); 80 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 ); 88 89 // init the tables with our primary table 90 $this->tables[$base_table][$base_table] = array( 91 'count' => 1, 92 'alias' => $base_table, 93 ); 94 95 if ($base_field) { 96 $this->fields[$base_field] = array( 97 'table' => $base_table, 98 'field' => $base_field, 99 'alias' => $base_field, 100 ); 101 } 102 103 $this->count_field = array( 104 'table' => $base_table, 105 'field' => $base_field, 106 'alias' => $base_field, 107 'count' => TRUE, 108 ); 109 } 110 111 // ---------------------------------------------------------------- 112 // Utility methods to set flags and data. 113 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 } 122 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 } 137 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 } 145 146 // ---------------------------------------------------------------- 147 // Table/join adding 148 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 } 184 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 } 191 192 // Make sure this join is adjusted for our relationship. 193 if ($link_point && isset($this->relationships[$link_point])) { 194 $join = $this->adjust_join($join, $link_point); 195 } 196 197 // Add the table directly to the queue to avoid accidentally marking 198 // it. 199 $this->table_queue[$alias] = array( 200 'table' => $join->table, 201 'num' => 1, 202 'alias' => $alias, 203 'join' => $join, 204 'relationship' => $link_point, 205 ); 206 207 $this->relationships[$alias] = array( 208 'link' => $link_point, 209 'table' => $join->table, 210 'base' => $base, 211 ); 212 213 $this->tables[$this->base_table][$alias] = array( 214 'count' => 1, 215 'alias' => $alias, 216 ); 217 218 return $alias; 219 } 220 221 /** 222 * Add a table to the query, ensuring the path exists. 223 * 224 * This function will test to ensure that the path back to the primary 225 * table is valid and exists; if you do not wish for this testing to 226 * occur, use $query->queue_table() instead. 227 * 228 * @param $table 229 * The name of the table to add. It needs to exist in the global table 230 * array. 231 * @param $relationship 232 * An alias of a table; if this is set, the path back to this table will 233 * be tested prior to adding the table, making sure that all intermediary 234 * tables exist and are properly aliased. If set to NULL the path to 235 * the primary table will be ensured. If the path cannot be made, the 236 * table will NOT be added. 237 * @param $join 238 * In some join configurations this table may actually join back through 239 * a different method; this is most likely to be used when tracing 240 * a hierarchy path. (node->parent->parent2->parent3). This parameter 241 * will specify how this table joins if it is not the default. 242 * @param $alias 243 * A specific alias to use, rather than the default alias. 244 * 245 * @return $alias 246 * The alias of the table; this alias can be used to access information 247 * about the table and should always be used to refer to the table when 248 * adding parts to the query. Or FALSE if the table was not able to be 249 * added. 250 */ 251 function add_table($table, $relationship = NULL, $join = NULL, $alias = NULL) { 252 if (!$this->ensure_path($table, $relationship, $join)) { 253 return FALSE; 254 } 255 256 if (!$join) { 257 $join = $this->get_join_data($table, $this->relationships[$relationship]['base']); 258 } 259 260 $join = $this->adjust_join($join, $relationship); 261 262 return $this->queue_table($table, $relationship, $join, $alias); 263 } 264 265 /** 266 * Add a table to the query, without ensuring the path. 267 * 268 * This function will test to ensure that the path back to the primary 269 * table is valid and exists; if you do not wish for this testing to 270 * occur, use $query->queue_table() instead. 271 * 272 * @param $table 273 * The name of the table to add. It needs to exist in the global table 274 * array. 275 * @param $relationship 276 * The primary table alias this table is related to. If not set, the 277 * primary table will be used. 278 * @param $join 279 * In some join configurations this table may actually join back through 280 * a different method; this is most likely to be used when tracing 281 * a hierarchy path. (node->parent->parent2->parent3). This parameter 282 * will specify how this table joins if it is not the default. 283 * @param $alias 284 * A specific alias to use, rather than the default alias. 285 * 286 * @return $alias 287 * The alias of the table; this alias can be used to access information 288 * about the table and should always be used to refer to the table when 289 * adding parts to the query. Or FALSE if the table was not able to be 290 * added. 291 */ 292 function queue_table($table, $relationship = NULL, $join = NULL, $alias = NULL) { 293 // If the alias is set, make sure it doesn't already exist. 294 if (isset($this->table_queue[$alias])) { 295 return $alias; 296 } 297 298 if (empty($relationship)) { 299 $relationship = $this->base_table; 300 } 301 302 if (!array_key_exists($relationship, $this->relationships)) { 303 return FALSE; 304 } 305 306 if (!$alias && $join && $relationship && !empty($join->adjusted) && $table != $join->table) { 307 if ($relationship == $this->base_table) { 308 $alias = $table; 309 } 310 else { 311 $alias = $relationship . '_' . $table; 312 } 313 } 314 315 // Check this again to make sure we don't blow up existing aliases for already 316 // adjusted joins. 317 if (isset($this->table_queue[$alias])) { 318 return $alias; 319 } 320 321 $alias = $this->mark_table($table, $relationship, $alias); 322 323 // If no alias is specified, give it the default. 324 if (!isset($alias)) { 325 $alias = $this->tables[$relationship][$table]['alias'] . $this->tables[$relationship][$table]['count']; 326 } 327 328 // If this is a relationship based table, add a marker with 329 // the relationship as a primary table for the alias. 330 if ($table != $alias) { 331 $this->mark_table($alias, $this->base_table, $alias); 332 } 333 334 // If no join is specified, pull it from the table data. 335 if (!isset($join)) { 336 $join = $this->get_join_data($table, $this->relationships[$relationship]['base']); 337 if (empty($join)) { 338 return FALSE; 339 } 340 341 $join = $this->adjust_join($join, $relationship); 342 } 343 344 $this->table_queue[$alias] = array( 345 'table' => $table, 346 'num' => $this->tables[$relationship][$table]['count'], 347 'alias' => $alias, 348 'join' => $join, 349 'relationship' => $relationship, 350 ); 351 352 return $alias; 353 } 354 355 function mark_table($table, $relationship, $alias) { 356 // Mark that this table has been added. 357 if (empty($this->tables[$relationship][$table])) { 358 if (!isset($alias)) { 359 $alias = ''; 360 if ($relationship != $this->base_table) { 361 // double underscore will help prevent accidental name 362 // space collisions. 363 $alias = $relationship . '__'; 364 } 365 $alias .= $table; 366 } 367 $this->tables[$relationship][$table] = array( 368 'count' => 1, 369 'alias' => $alias, 370 ); 371 } 372 else { 373 $this->tables[$relationship][$table]['count']++; 374 } 375 376 return $alias; 377 } 378 379 /** 380 * Ensure a table exists in the queue; if it already exists it won't 381 * do anything, but if it doesn't it will add the table queue. It will ensure 382 * a path leads back to the relationship table. 383 * 384 * @param $table 385 * The unaliased name of the table to ensure. 386 * @param $relationship 387 * The relationship to ensure the table links to. Each relationship will 388 * get a unique instance of the table being added. If not specified, 389 * will be the primary table. 390 * @param $join 391 * A views_join object (or derived object) to join the alias in. 392 * 393 * @return 394 * The alias used to refer to this specific table, or NULL if the table 395 * cannot be ensured. 396 */ 397 function ensure_table($table, $relationship = NULL, $join = NULL) { 398 // ensure a relationship 399 if (empty($relationship)) { 400 $relationship = $this->base_table; 401 } 402 403 // If the relationship is the primary table, this actually be a relationship 404 // link back from an alias. We store all aliases along with the primary table 405 // to detect this state, because eventually it'll hit a table we already 406 // have and that's when we want to stop. 407 if ($relationship == $this->base_table && !empty($this->tables[$relationship][$table])) { 408 return $this->tables[$relationship][$table]['alias']; 409 } 410 411 if (!array_key_exists($relationship, $this->relationships)) { 412 return FALSE; 413 } 414 415 if ($table == $this->relationships[$relationship]['base']) { 416 return $relationship; 417 } 418 419 // If we do not have join info, fetch it. 420 if (!isset($join)) { 421 $join = $this->get_join_data($table, $this->relationships[$relationship]['base']); 422 } 423 424 // If it can't be fetched, this won't work. 425 if (empty($join)) { 426 return; 427 } 428 429 // Adjust this join for the relationship, which will ensure that the 'base' 430 // table it links to is correct. Tables adjoined to a relationship 431 // join to a link point, not the base table. 432 $join = $this->adjust_join($join, $relationship); 433 434 if ($this->ensure_path($table, $relationship, $join)) { 435 // Attempt to eliminate redundant joins. If this table's 436 // relationship and join exactly matches an existing table's 437 // relationship and join, we do not have to join to it again; 438 // just return the existing table's alias. See 439 // http://groups.drupal.org/node/11288 for details. 440 // 441 // This can be done safely here but not lower down in 442 // queue_table(), because queue_table() is also used by 443 // add_table() which requires the ability to intentionally add 444 // the same table with the same join multiple times. For 445 // example, a view that filters on 3 taxonomy terms using AND 446 // needs to join term_data 3 times with the same join. 447 448 // scan through the table queue to see if a matching join and 449 // relationship exists. If so, use it instead of this join. 450 451 // TODO: Scanning through $this->table_queue results in an 452 // O(N^2) algorithm, and this code runs every time the view is 453 // instantiated (Views 2 does not currently cache queries). 454 // There are a couple possible "improvements" but we should do 455 // some performance testing before picking one. 456 foreach ($this->table_queue as $queued_table) { 457 // In PHP 4 and 5, the == operation returns TRUE for two objects 458 // if they are instances of the same class and have the same 459 // attributes and values. 460 if ($queued_table['relationship'] == $relationship && $queued_table['join'] == $join) { 461 return $queued_table['alias']; 462 } 463 } 464 465 return $this->queue_table($table, $relationship, $join); 466 } 467 } 468 469 /** 470 * Make sure that the specified table can be properly linked to the primary 471 * table in the JOINs. This function uses recursion. If the tables 472 * needed to complete the path back to the primary table are not in the 473 * query they will be added, but additional copies will NOT be added 474 * if the table is already there. 475 */ 476 function ensure_path($table, $relationship = NULL, $join = NULL, $traced = array(), $add = array()) { 477 if (!isset($relationship)) { 478 $relationship = $this->base_table; 479 } 480 481 if (!array_key_exists($relationship, $this->relationships)) { 482 return FALSE; 483 } 484 485 // If we do not have join info, fetch it. 486 if (!isset($join)) { 487 $join = $this->get_join_data($table, $this->relationships[$relationship]['base']); 488 } 489 490 // If it can't be fetched, this won't work. 491 if (empty($join)) { 492 return FALSE; 493 } 494 495 // Does a table along this path exist? 496 if (isset($this->tables[$relationship][$table]) || 497 ($join && $join->left_table == $relationship) || 498 ($join && $join->left_table == $this->relationships[$relationship]['table'])) { 499 500 // Make sure that we're linking to the correct table for our relationship. 501 foreach (array_reverse($add) as $table => $path_join) { 502 $this->queue_table($table, $relationship, $this->adjust_join($path_join, $relationship)); 503 } 504 return TRUE; 505 } 506 507 // Have we been this way? 508 if (isset($traced[$join->left_table])) { 509 // we looped. Broked. 510 return FALSE; 511 } 512 513 // Do we have to add this table? 514 $left_join = $this->get_join_data($join->left_table, $this->relationships[$relationship]['base']); 515 if (!isset($this->tables[$relationship][$join->left_table])) { 516 $add[$join->left_table] = $left_join; 517 } 518 519 // Keep looking. 520 $traced[$join->left_table] = TRUE; 521 return $this->ensure_path($join->left_table, $relationship, $left_join, $traced, $add); 522 } 523 524 /** 525 * Fix a join to adhere to the proper relationship; the left table can vary 526 * based upon what relationship items are joined in on. 527 */ 528 function adjust_join($join, $relationship) { 529 if (!empty($join->adjusted)) { 530 return $join; 531 } 532 533 if (empty($relationship) || empty($this->relationships[$relationship])) { 534 return $join; 535 } 536 537 // Adjusts the left table for our relationship. 538 if ($relationship != $this->base_table) { 539 // If we're linking to the primary table, the relationship to use will 540 // be the prior relationship. Unless it's a direct link. 541 542 // Safety! Don't modify an original here. 543 $join = drupal_clone($join); 544 545 // Do we need to try to ensure a path? 546 if ($join->left_table != $this->relationships[$relationship]['table'] && 547 $join->left_table != $this->relationships[$relationship]['base'] && 548 !isset($this->tables[$relationship][$join->left_table]['alias'])) { 549 $this->ensure_table($join->left_table, $relationship); 550 } 551 552 // First, if this is our link point/anchor table, just use the relationship 553 if ($join->left_table == $this->relationships[$relationship]['table']) { 554 $join->left_table = $relationship; 555 } 556 // then, try the base alias. 557 else if (isset($this->tables[$relationship][$join->left_table]['alias'])) { 558 $join->left_table = $this->tables[$relationship][$join->left_table]['alias']; 559 } 560 // But if we're already looking at an alias, use that instead. 561 else if (isset($this->table_queue[$relationship]['alias'])) { 562 $join->left_table = $this->table_queue[$relationship]['alias']; 563 } 564 } 565 566 $join->adjusted = TRUE; 567 return $join; 568 } 569 570 /** 571 * Retrieve join data from the larger join data cache. 572 * 573 * @param $table 574 * The table to get the join information for. 575 * @param $base_table 576 * The path we're following to get this join. 577 * 578 * @return 579 * A views_join object or child object, if one exists. 580 */ 581 function get_join_data($table, $base_table) { 582 // Check to see if we're linking to a known alias. If so, get the real 583 // table's data instead. 584 if (!empty($this->table_queue[$table])) { 585 $table = $this->table_queue[$table]['table']; 586 } 587 return views_get_table_join($table, $base_table); 588 589 } 590 591 /** 592 * Get the information associated with a table. 593 * 594 * If you need the alias of a table with a particular relationship, use 595 * ensure_table(). 596 */ 597 function get_table_info($table) { 598 if (!empty($this->table_queue[$table])) { 599 return $this->table_queue[$table]; 600 } 601 602 // In rare cases we might *only* have aliased versions of the table. 603 if (!empty($this->tables[$this->base_table][$table])) { 604 $alias = $this->tables[$this->base_table][$table]['alias']; 605 if (!empty($this->table_queue[$alias])) { 606 return $this->table_queue[$alias]; 607 } 608 } 609 } 610 611 /** 612 * Add a field to the query table, possibly with an alias. This will 613 * automatically call ensure_table to make sure the required table 614 * exists, *unless* $table is unset. 615 * 616 * @param $table 617 * The table this field is attached to. If NULL, it is assumed this will 618 * be a formula; otherwise, ensure_table is used to make sure the 619 * table exists. 620 * @param $field 621 * The name of the field to add. This may be a real field or a formula. 622 * @param $alias 623 * The alias to create. If not specified, the alias will be $table_$field 624 * unless $table is NULL. When adding formulae, it is recommended that an 625 * alias be used. 626 * 627 * @return $name 628 * The name that this field can be referred to as. Usually this is the alias. 629 */ 630 function add_field($table, $field, $alias = '', $params = NULL) { 631 // We check for this specifically because it gets a special alias. 632 if ($table == $this->base_table && $field == $this->base_field && empty($alias)) { 633 $alias = $this->base_field; 634 } 635 636 if ($table && empty($this->table_queue[$table])) { 637 $this->ensure_table($table); 638 } 639 640 if (!$alias && $table) { 641 $alias = $table . '_' . $field; 642 } 643 644 $name = $alias ? $alias : $field; 645 646 // @todo FIXME -- $alias, then $name is inconsistent 647 if (empty($this->fields[$alias])) { 648 $this->fields[$name] = array( 649 'field' => $field, 650 'table' => $table, 651 'alias' => $alias, 652 ); 653 } 654 655 foreach ((array)$params as $key => $value) { 656 $this->fields[$name][$key] = $value; 657 } 658 659 return $name; 660 } 661 662 /** 663 * Remove all fields that may've been added; primarily used for summary 664 * mode where we're changing the query because we didn't get data we needed. 665 */ 666 function clear_fields() { 667 $this->fields = array(); 668 } 669 670 /** 671 * Create a new grouping for the WHERE or HAVING clause. 672 * 673 * @param $type 674 * Either 'AND' or 'OR'. All items within this group will be added 675 * to the WHERE clause with this logical operator. 676 * @param $group 677 * An ID to use for this group. If unspecified, an ID will be generated. 678 * @param $where 679 * 'where' or 'having'. 680 * 681 * @return $group 682 * The group ID generated. 683 */ 684 function set_where_group($type = 'AND', $group = NULL, $where = 'where') { 685 // Set an alias. 686 $groups = &$this->$where; 687 688 if (!isset($group)) { 689 $group = empty($groups) ? 1 : max(array_keys($groups)) + 1; 690 } 691 692 // Create an empty group 693 if (empty($groups[$group])) { 694 $groups[$group] = array('clauses' => array(), 'args' => array()); 695 } 696 697 $groups[$group]['type'] = strtoupper($type); 698 return $group; 699 } 700 701 /** 702 * Control how all WHERE and HAVING groups are put together. 703 * 704 * @param $type 705 * Either 'AND' or 'OR' 706 */ 707 function set_group_operator($type = 'AND') { 708 $this->group_operator = strtoupper($type); 709 } 710 711 /** 712 * Add a simple WHERE clause to the query. The caller is responsible for 713 * ensuring that all fields are fully qualified (TABLE.FIELD) and that 714 * the table already exists in the query. 715 * 716 * @param $group 717 * The WHERE group to add these to; groups are used to create AND/OR 718 * sections. Groups cannot be nested. Use 0 as the default group. 719 * If the group does not yet exist it will be created as an AND group. 720 * @param $clause 721 * The actual clause to add. When adding a where clause it is important 722 * that all tables are addressed by the alias provided by add_table or 723 * ensure_table and that all fields are addressed by their alias wehn 724 * possible. Please use %d and %s for arguments. 725 * @param ... 726 * A number of arguments as used in db_query(). May be many args or one 727 * array full of args. 728 */ 729 function add_where($group, $clause) { 730 $args = func_get_args(); 731 array_shift($args); // ditch $group 732 array_shift($args); // ditch $clause 733 734 // Expand an array of args if it came in. 735 if (count($args) == 1 && is_array(reset($args))) { 736 $args = current($args); 737 } 738 739 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all 740 // the default group. 741 if (empty($group)) { 742 $group = 0; 743 } 744 745 // Check for a group. 746 if (!isset($this->where[$group])) { 747 $this->set_where_group('AND', $group); 748 } 749 750 // Add the clause and the args. 751 if (is_array($args)) { 752 $this->where[$group]['clauses'][] = $clause; 753 // we use array_values() here to prevent array_merge errors as keys from multiple 754 // sources occasionally collide. 755 $this->where[$group]['args'] = array_merge($this->where[$group]['args'], array_values($args)); 756 } 757 } 758 759 /** 760 * Add a simple HAVING clause to the query. The caller is responsible for 761 * ensuring that all fields are fully qualified (TABLE.FIELD) and that 762 * the table and an appropriate GROUP BY already exist in the query. 763 * 764 * @param $group 765 * The HAVING group to add these to; groups are used to create AND/OR 766 * sections. Groups cannot be nested. Use 0 as the default group. 767 * If the group does not yet exist it will be created as an AND group. 768 * @param $clause 769 * The actual clause to add. When adding a having clause it is important 770 * that all tables are addressed by the alias provided by add_table or 771 * ensure_table and that all fields are addressed by their alias wehn 772 * possible. Please use %d and %s for arguments. 773 * @param ... 774 * A number of arguments as used in db_query(). May be many args or one 775 * array full of args. 776 */ 777 function add_having($group, $clause) { 778 $args = func_get_args(); 779 array_shift($args); // ditch $group 780 array_shift($args); // ditch $clause 781 782 // Expand an array of args if it came in. 783 if (count($args) == 1 && is_array(reset($args))) { 784 $args = current($args); 785 } 786 787 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all 788 // the default group. 789 if (empty($group)) { 790 $group = 0; 791 } 792 793 // Check for a group. 794 if (!isset($this->having[$group])) { 795 $this->set_where_group('AND', $group, 'having'); 796 } 797 798 // Add the clause and the args. 799 if (is_array($args)) { 800 $this->having[$group]['clauses'][] = $clause; 801 $this->having[$group]['args'] = array_merge($this->having[$group]['args'], array_values($args)); 802 } 803 } 804 805 /** 806 * Add an ORDER BY clause to the query. 807 * 808 * @param $table 809 * The table this field is part of. If a formula, enter NULL. 810 * @param $field 811 * The field or formula to sort on. If already a field, enter NULL 812 * and put in the alias. 813 * @param $order 814 * Either ASC or DESC. 815 * @param $alias 816 * The alias to add the field as. In SQL, all fields in the order by 817 * must also be in the SELECT portion. If an $alias isn't specified 818 * one will be generated for from the $field; however, if the 819 * $field is a formula, this alias will likely fail. 820 */ 821 function add_orderby($table, $field, $order, $alias = '') { 822 if ($table) { 823 $this->ensure_table($table); 824 } 825 826 // Only fill out this aliasing if there is a table; 827 // otherwise we assume it is a formula. 828 if (!$alias && $table) { 829 $as = $table . '_' . $field; 830 } 831 else { 832 $as = $alias; 833 } 834 835 if ($field) { 836 $this->add_field($table, $field, $as); 837 } 838 839 $this->orderby[] = "$as " . strtoupper($order); 840 841 // If grouping, all items in the order by must also be in the 842 // group by clause. Check $table to ensure that this is not a 843 // formula. 844 if ($this->groupby && $table) { 845 $this->add_groupby($as); 846 } 847 } 848 849 /** 850 * Add a simple GROUP BY clause to the query. The caller is responsible 851 * for ensuring that the fields are fully qualified and the table is properly 852 * added. 853 */ 854 function add_groupby($clause) { 855 // Only add it if it's not already in there. 856 if (!in_array($clause, $this->groupby)) { 857 $this->groupby[] = $clause; 858 } 859 } 860 861 /** 862 * Construct the "WHERE" or "HAVING" part of the query. 863 * 864 * @param $where 865 * 'where' or 'having'. 866 */ 867 function condition_sql($where = 'where') { 868 $clauses = array(); 869 foreach ($this->$where as $group => $info) { 870 $clause = implode(") " . $info['type'] . " (", $info['clauses']); 871 if (count($info['clauses']) > 1) { 872 $clause = '(' . $clause . ')'; 873 } 874 $clauses[] = $clause; 875 } 876 877 if ($clauses) { 878 $keyword = drupal_strtoupper($where); 879 if (count($clauses) > 1) { 880 return "$keyword (" . implode(")\n " . $this->group_operator . ' (', $clauses) . ")\n"; 881 } 882 else { 883 return "$keyword " . array_shift($clauses) . "\n"; 884 } 885 } 886 return ""; 887 } 888 889 /** 890 * Generate a query and a countquery from all of the information supplied 891 * to the object. 892 * 893 * @param $get_count 894 * Provide a countquery if this is true, otherwise provide a normal query. 895 */ 896 function query($get_count = FALSE) { 897 // Check query distinct value. 898 if (empty($this->no_distinct) && $this->distinct && !empty($this->fields)) { 899 if (!empty($this->fields[$this->base_field])) { 900 $this->fields[$this->base_field]['distinct'] = TRUE; 901 $this->add_groupby($this->base_field); 902 } 903 } 904 905 /** 906 * An optimized count query includes just the base field instead of all the fields. 907 * Determine of this query qualifies by checking for a groupby or distinct. 908 */ 909 $fields_array = $this->fields; 910 if ($get_count && !$this->groupby) { 911 foreach ($fields_array as $field) { 912 if (!empty($field['distinct'])) { 913 $get_count_optimized = FALSE; 914 break; 915 } 916 } 917 } 918 else { 919 $get_count_optimized = FALSE; 920 } 921 if (!isset($get_count_optimized)) { 922 $get_count_optimized = TRUE; 923 } 924 925 $joins = $fields = $where = $having = $orderby = $groupby = ''; 926 // Add all the tables to the query via joins. We assume all LEFT joins. 927 foreach ($this->table_queue as $table) { 928 if (is_object($table['join'])) { 929 $joins .= $table['join']->join($table, $this) . "\n"; 930 } 931 } 932 933 $has_aggregate = FALSE; 934 $non_aggregates = array(); 935 936 foreach ($fields_array as $field) { 937 if ($fields) { 938 $fields .= ",\n "; 939 } 940 $string = ''; 941 if (!empty($field['table'])) { 942 $string .= $field['table'] . '.'; 943 } 944 $string .= $field['field']; 945 946 // store for use with non-aggregates below 947 $fieldname = (!empty($field['alias']) ? $field['alias'] : $string); 948 949 if (!empty($field['distinct'])) { 950 $string = "DISTINCT($string)"; 951 } 952 if (!empty($field['count'])) { 953 $string = "COUNT($string)"; 954 $has_aggregate = TRUE; 955 } 956 else if (!empty($field['aggregate'])) { 957 $has_aggregate = TRUE; 958 } 959 elseif ($this->distinct && !in_array($fieldname, $this->groupby)) { 960 $string = $GLOBALS['db_type'] == 'pgsql' ? "FIRST($string)" : $string; 961 } 962 else { 963 $non_aggregates[] = $fieldname; 964 } 965 if ($field['alias']) { 966 $string .= " AS $field[alias]"; 967 } 968 $fields .= $string; 969 970 if ($get_count_optimized) { 971 // We only want the first field in this case. 972 break; 973 } 974 } 975 976 if ($has_aggregate || $this->groupby) { 977 $groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n"; 978 if ($this->having) { 979 $having = $this->condition_sql('having'); 980 } 981 } 982 983 if (!$get_count_optimized) { 984 // we only add the groupby if we're not counting. 985 if ($this->orderby) { 986 $orderby = "ORDER BY " . implode(', ', $this->orderby) . "\n"; 987 } 988 } 989 990 $where = $this->condition_sql(); 991 992 $query = "SELECT $fields\n FROM {" . $this->base_table . "} $this->base_table \n$joins $where $groupby $having $orderby"; 993 994 $replace = array('>' => '>', '<' => '<'); 995 $query = strtr($query, $replace); 996 997 return $query; 998 } 999 1000 /** 1001 * Get the arguments attached to the WHERE and HAVING clauses of this query. 1002 */ 1003 function get_where_args() { 1004 $args = array(); 1005 foreach ($this->where as $group => $where) { 1006 $args = array_merge($args, $where['args']); 1007 } 1008 foreach ($this->having as $group => $having) { 1009 $args = array_merge($args, $having['args']); 1010 } 1011 return $args; 1012 } 1013 } 1014
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Mar 24 11:18:33 2011 | Cross-referenced by PHPXref 0.7 |