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