Code coverage for /20081101/includes/database/select.inc

Line #Times calledCode
1
<?php
2
// $Id: select.inc,v 1.7 2008/10/29 09:40:17 dries Exp $
3
4
/**
5
 * @ingroup database
6
 * @{
7
 */
8
9
/**
10
 * Query builder for SELECT statements.
11
 */
122013
class SelectQuery extends Query implements QueryConditionInterface,
QueryAlterableInterface {
13
14
  /**
15
   * The fields to SELECT.
16
   *
17
   * @var array
18
   */
19
  protected $fields = array();
20
21
  /**
22
   * The expressions to SELECT as virtual fields.
23
   *
24
   * @var array
25
   */
26
  protected $expressions = array();
27
28
  /**
29
   * The tables against which to JOIN.
30
   *
31
   * This property is a nested array.  Each entry is an array representing
32
   * a single table against which to join.  The structure of each entry is:
33
   *
34
   * array(
35
   *   'type' => $join_type (one of INNER, LEFT OUTER, RIGHT OUTER),
36
   *   'table' => $name_of_table,
37
   *   'alias' => $alias_of_the_table,
38
   *   'condition' => $condition_clause_on_which_to_join,
39
   *   'arguments' => $array_of_arguments_for_placeholders_in_the
condition.
40
   *   'all_fields' => TRUE to SELECT $alias.*, FALSE or NULL otherwise.
41
   * )
42
   *
43
   * @var array
44
   */
45
  protected $tables = array();
46
47
  /**
48
   * The fields by which to order this query.
49
   *
50
   * This is an associative array.  The keys are the fields to order, and
the value
51
   * is the direction to order, either ASC or DESC.
52
   *
53
   * @var array
54
   */
55
  protected $order = array();
56
57
  /**
58
   * The fields by which to group.
59
   *
60
   * @var array
61
   */
62
  protected $group = array();
63
64
  /**
65
   * The conditional object for the WHERE clause.
66
   *
67
   * @var DatabaseCondition
68
   */
69
  protected $where;
70
71
  /**
72
   * The conditional object for the HAVING clause.
73
   *
74
   * @var DatabaseCondition
75
   */
76
  protected $having;
77
78
  /**
79
   * Whether or not this query should be DISTINCT
80
   *
81
   * @var boolean
82
   */
83
  protected $distinct = FALSE;
84
85
  /**
86
   * The range limiters for this query.
87
   *
88
   * @var array
89
   */
90
  protected $range;
91
92
  public function __construct($table, $alias = NULL, DatabaseConnection
$connection, $options = array()) {
932005
    $options['return'] = Database::RETURN_STATEMENT;
942005
    parent::__construct($connection, $options);
952005
    $this->where = new DatabaseCondition('AND');
962005
    $this->having = new DatabaseCondition('AND');
972005
    $this->addJoin(NULL, $table, $alias);
982005
  }
99
100
  /* Implementations of QueryAlterableInterface. */
101
102
  public function addTag($tag) {
1033
    $this->alterTags[$tag] = 1;
1043
  }
105
106
  public function hasTag($tag) {
10719
    return isset($this->alterTags[$tag]);
1080
  }
109
110
  public function hasAllTags() {
1111
    return !(boolean)array_diff(func_get_args(),
array_keys($this->alterTags));
1120
  }
113
114
  public function hasAnyTag() {
1151
    return (boolean)array_intersect(func_get_args(),
array_keys($this->alterTags));
1160
  }
117
118
  public function addMetaData($key, $object) {
1191
    $this->alterMetaData[$key] = $object;
1201
  }
121
122
  public function getMetaData($key) {
1231
    return isset($this->alterMetaData[$key]) ? $this->alterMetaData[$key] :
NULL;
1240
  }
125
126
  /* Implementations of QueryConditionInterface for the WHERE clause. */
127
128
  public function condition($field, $value = NULL, $operator = '=') {
1291979
    if (!isset($num_args)) {
1301979
      $num_args = func_num_args();
1311979
    }
1321979
    $this->where->condition($field, $value, $operator, $num_args);
1331979
    return $this;
1340
  }
135
136
  public function &conditions() {
1371
    return $this->where->conditions();
1380
  }
139
140
  public function arguments() {
1410
    return $this->where->arguments();
1420
  }
143
144
  public function where($snippet, $args = array()) {
1450
    $this->where->where($snippet, $args);
1460
    return $this;
1470
  }
148
149
  public function compile(DatabaseConnection $connection) {
1500
    return $this->where->compile($connection);
1510
  }
152
153
  /* Implmeentations of QueryConditionInterface for the HAVING clause. */
154
155
  public function havingCondition($field, $value = NULL, $operator = '=') {
1561
    if (!isset($num_args)) {
1571
      $num_args = func_num_args();
1581
    }
1591
    $this->having->condition($field, $value, $operator, $num_args);
1601
    return $this;
1610
  }
162
163
  public function &havingConditions() {
1640
    return $this->having->conditions();
1650
  }
166
167
  public function havingArguments() {
1680
    return $this->having->arguments();
1690
  }
170
171
  public function having($snippet, $args = array()) {
1720
    $this->having->where($snippet, $args);
1730
    return $this;
1740
  }
175
176
  public function havingCompile(DatabaseConnection $connection) {
1770
    return $this->having->compile($connection);
1780
  }
179
180
  /* Alter accessors to expose the query data to alter hooks. */
181
182
  /**
183
   * Returns a reference to the fields array for this query.
184
   *
185
   * Because this method returns by reference, alter hooks may edit the
fields
186
   * array directly to make their changes.  If just adding fields, however,
the
187
   * use of addField() is preferred.
188
   *
189
   * Note that this method must be called by reference as well:
190
   *
191
   * @code
192
   * $fields =& $query->getFields();
193
   * @endcode
194
   *
195
   * @return
196
   *   A reference to the fields array structure.
197
   */
198
  public function &getFields() {
1992
    return $this->fields;
2000
  }
201
202
  /**
203
   * Returns a reference to the expressions array for this query.
204
   *
205
   * Because this method returns by reference, alter hooks may edit the
expressions
206
   * array directly to make their changes.  If just adding expressions,
however, the
207
   * use of addExpression() is preferred.
208
   *
209
   * Note that this method must be called by reference as well:
210
   *
211
   * @code
212
   * $fields =& $query->getExpressions();
213
   * @endcode
214
   *
215
   * @return
216
   *   A reference to the expression array structure.
217
   */
218
  public function &getExpressions() {
2192
    return $this->expressions;
2200
  }
221
222
  /**
223
   * Returns a reference to the order by array for this query.
224
   *
225
   * Because this method returns by reference, alter hooks may edit the
order-by
226
   * array directly to make their changes.  If just adding additional
ordering
227
   * fields, however, the use of orderBy() is preferred.
228
   *
229
   * Note that this method must be called by reference as well:
230
   *
231
   * @code
232
   * $fields =& $query->getOrderBy();
233
   * @endcode
234
   *
235
   * @return
236
   *   A reference to the expression array structure.
237
   */
238
  public function &getOrderBy() {
2391
    return $this->order;
2400
  }
241
242
  /**
243
   * Returns a reference to the tables array for this query.
244
   *
245
   * Because this method returns by reference, alter hooks may edit the
tables
246
   * array directly to make their changes.  If just adding tables, however,
the
247
   * use of the join() methods is preferred.
248
   *
249
   * Note that this method must be called by reference as well:
250
   *
251
   * @code
252
   * $fields =& $query->getTables();
253
   * @endcode
254
   *
255
   * @return
256
   *   A reference to the tables array structure.
257
   */
258
  public function &getTables() {
2590
    return $this->tables;
2600
  }
261
262
  /**
263
   * Compiles and returns an associative array of the arguments for this
prepared statement.
264
   *
265
   * @return array
266
   */
267
  public function getArguments() {
2680
    $this->where->compile($this->connection);
2690
    $this->having->compile($this->connection);
2700
    $args = $this->where->arguments() + $this->having->arguments();
2710
    foreach ($this->tables as $table) {
2720
      if ($table['arguments']) {
2730
        $args += $table['arguments'];
2740
      }
2750
    }
2760
    foreach ($this->expressions as $expression) {
2770
      if ($expression['arguments']) {
2780
        $args += $expression['arguments'];
2790
      }
2800
    }
281
2820
    return $args;
2830
  }
284
285
  public function execute() {
2862005
    drupal_alter('query', $this);
287
2882005
    $this->where->compile($this->connection);
2892005
    $this->having->compile($this->connection);
2902005
    $args = $this->where->arguments() + $this->having->arguments();
2912005
    foreach ($this->tables as $table) {
2922005
      if ($table['arguments']) {
2930
        $args += $table['arguments'];
2940
      }
2952005
    }
2962005
    foreach ($this->expressions as $expression) {
2974
      if ($expression['arguments']) {
2980
        $args += $expression['arguments'];
2990
      }
3004
    }
301
3022005
    if (!empty($this->range)) {
303155
      return $this->connection->queryRange((string)$this, $args,
$this->range['start'], $this->range['length'], $this->queryOptions);
3040
    }
3051874
    return $this->connection->query((string)$this, $args,
$this->queryOptions);
3060
  }
307
308
  /**
309
   * Sets this query to be DISTINCT.
310
   *
311
   * @param $distinct
312
   *   TRUE to flag this query DISTINCT, FALSE to disable it.
313
   * @return
314
   *   The called object.
315
   */
316
  public function distinct($distinct = TRUE) {
3171757
    $this->distinct = $distinct;
3181757
    return $this;
3190
  }
320
321
  /**
322
   * Adds a field to the list to be SELECTed.
323
   *
324
   * @param $table_alias
325
   *   The name of the table from which the field comes, as an alias. 
Generally
326
   *   you will want to use the return value of join() here to ensure that
it is
327
   *   valid.
328
   * @param $field
329
   *   The name of the field.
330
   * @param $alias
331
   *   The alias for this field.  If not specified, one will be generated
332
   *   automatically based on the $table_alias and $field.  The alias will
be
333
   *   checked for uniqueness, so the requested alias may not be the alias
334
   *   that is assigned in all cases.
335
   * @return
336
   *   The unique alias that was assigned for this field.
337
   */
338
  public function addField($table_alias, $field, $alias = NULL) {
339
    // If no alias is specified, first try the field name itself.
3402005
    if (empty($alias)) {
3411906
      $alias = $field;
3421906
    }
343
344
    // If that's already in use, try the table name and field name.
3452005
    if (!empty($this->tables[$alias])) {
3460
      $alias = $table_alias . '_' . $field;
3470
    }
348
349
    // If that is already used, just add a counter until we find an unused
alias.
3502005
    $alias_candidate = $alias;
3512005
    $count = 2;
3522005
    while (!empty($this->tables[$alias_candidate])) {
3530
      $alias_candidate = $alias . '_' . $count++;
3540
    }
3552005
    $alias = $alias_candidate;
356
3572005
    $this->fields[$alias] = array(
3582005
      'field' => $field,
3592005
      'table' => $table_alias,
3602005
      'alias' => $alias,
361
    );
362
3632005
    return $alias;
3640
  }
365
366
  /**
367
   * Add multiple fields from the same table to be SELECTed.
368
   *
369
   * This method does not return the aliases set for the passed fields.  In
the
370
   * majority of cases that is not a problem, as the alias will be the
field
371
   * name.  However, if you do need to know the alias you can call
getFields()
372
   * and examine the result to determine what alias was created. 
Alternatively,
373
   * simply use addField() for the few fields you care about and this
method for
374
   * the rest.
375
   *
376
   * @param $table_alias
377
   *   The name of the table from which the field comes, as an alias. 
Generally
378
   *   you will want to use the return value of join() here to ensure that
it is
379
   *   valid.
380
   * @param $fields
381
   *   An indexed array of fields present in the specified table that
should be
382
   *   included in this query.  If not specified, $table_alias.* will be
generated
383
   *   without any aliases.
384
   * @return
385
   *   The called object.
386
   */
387
  public function fields($table_alias, Array $fields = array()) {
388
3891
    if ($fields) {
3901
      foreach ($fields as $field) {
391
        // We don't care what alias was assigned.
3921
        $this->addField($table_alias, $field);
3931
      }
3941
    }
395
    else {
396
      // We want all fields from this table.
3971
      $this->tables[$table_alias]['all_fields'] = TRUE;
398
    }
399
4001
    return $this;
4010
  }
402
403
  /**
404
   * Adds an expression to the list of "fields" to be SELECTed.
405
   *
406
   * An expression can be any arbitrary string that is valid SQL.  That
includes
407
   * various functions, which may in some cases be database-dependant. 
This
408
   * method makes no effort to correct for database-specific functions.
409
   *
410
   * @param $expression
411
   *   The expression string.  May contain placeholders.
412
   * @param $alias
413
   *   The alias for this expression.  If not specified, one will be
generated
414
   *   automatically in the form "expression_#".  The alias will be checked
for
415
   *   uniqueness, so the requested alias may not be the alias that is
asigned
416
   *   in all cases.
417
   * @param $arguments
418
   *   Any placeholder arguments needed for this expression.
419
   * @return
420
   *   The unique alias that was assigned for this expression.
421
   */
422
  public function addExpression($expression, $alias = NULL, $arguments =
array()) {
4234
    static $alaises = array();
424
4254
    if (empty($alias)) {
4262
      $alias = 'expression';
4272
    }
428
4294
    if (empty($aliases[$alias])) {
4304
      $aliases[$alias] = 1;
4314
    }
432
4334
    if (!empty($this->expressions[$alias])) {
4340
      $alias = $alias . '_' . $aliases[$alias]++;
4350
    }
436
4374
    $this->expressions[$alias] = array(
4384
      'expression' => $expression,
4394
      'alias' => $alias,
4404
      'arguments' => $arguments,
441
    );
442
4434
    return $alias;
4440
  }
445
446
  /**
447
   * Default Join against another table in the database.
448
   *
449
   * This method is a convenience method for innerJoin().
450
   *
451
   * @param $table
452
   *   The table against which to join.
453
   * @param $alias
454
   *   The alias for the table.  In most cases this should be the first
letter
455
   *   of the table, or the first letter of each "word" in the table.
456
   * @param $condition
457
   *   The condition on which to join this table.  If the join requires
values,
458
   *   this clause should use a named placeholder and the value or values
to
459
   *   insert should be passed in the 4th parameter.  For the first table
joined
460
   *   on a query, this value is ignored as the first table is taken as the
base
461
   *   table.
462
   * @param $arguments
463
   *   An array of arguments to replace into the $condition of this join.
464
   * @return
465
   *   The unique alias that was assigned for this table.
466
   */
467
  public function join($table, $alias = NULL, $condition = NULL, $arguments
= array()) {
4682
    return $this->addJoin('INNER', $table, $alias, $condition, $arguments);
4690
  }
470
471
  /**
472
   * Inner Join against another table in the database.
473
   *
474
   * @param $table
475
   *   The table against which to join.
476
   * @param $alias
477
   *   The alias for the table.  In most cases this should be the first
letter
478
   *   of the table, or the first letter of each "word" in the table.
479
   * @param $condition
480
   *   The condition on which to join this table.  If the join requires
values,
481
   *   this clause should use a named placeholder and the value or values
to
482
   *   insert should be passed in the 4th parameter.  For the first table
joined
483
   *   on a query, this value is ignored as the first table is taken as the
base
484
   *   table.
485
   * @param $arguments
486
   *   An array of arguments to replace into the $condition of this join.
487
   * @return
488
   *   The unique alias that was assigned for this table.
489
   */
490
  public function innerJoin($table, $alias = NULL, $condition = NULL,
$arguments = array()) {
4910
    return $this->addJoin('INNER', $table, $alias, $condition, $arguments);
4920
  }
493
494
  /**
495
   * Left Outer Join against another table in the database.
496
   *
497
   * @param $table
498
   *   The table against which to join.
499
   * @param $alias
500
   *   The alias for the table.  In most cases this should be the first
letter
501
   *   of the table, or the first letter of each "word" in the table.
502
   * @param $condition
503
   *   The condition on which to join this table.  If the join requires
values,
504
   *   this clause should use a named placeholder and the value or values
to
505
   *   insert should be passed in the 4th parameter.  For the first table
joined
506
   *   on a query, this value is ignored as the first table is taken as the
base
507
   *   table.
508
   * @param $arguments
509
   *   An array of arguments to replace into the $condition of this join.
510
   * @return
511
   *   The unique alias that was assigned for this table.
512
   */
513
  public function leftJoin($table, $alias = NULL, $condition = NULL,
$arguments = array()) {
5141
    return $this->addJoin('LEFT OUTER', $table, $alias, $condition,
$arguments);
5150
  }
516
517
  /**
518
   * Right Outer Join against another table in the database.
519
   *
520
   * @param $table
521
   *   The table against which to join.
522
   * @param $alias
523
   *   The alias for the table.  In most cases this should be the first
letter
524
   *   of the table, or the first letter of each "word" in the table.
525
   * @param $condition
526
   *   The condition on which to join this table.  If the join requires
values,
527
   *   this clause should use a named placeholder and the value or values
to
528
   *   insert should be passed in the 4th parameter.  For the first table
joined
529
   *   on a query, this value is ignored as the first table is taken as the
base
530
   *   table.
531
   * @param $arguments
532
   *   An array of arguments to replace into the $condition of this join.
533
   * @return
534
   *   The unique alias that was assigned for this table.
535
   */
536
  public function rightJoin($table, $alias = NULL, $condition = NULL,
$arguments = array()) {
5370
    return $this->addJoin('RIGHT OUTER', $table, $alias, $condition,
$arguments);
5380
  }
539
540
  /**
541
   * Join against another table in the database.
542
   *
543
   * This method does the "hard" work of queuing up a table to be joined
against.
544
   * In some cases, that may include dipping into the Schema API to find
the necessary
545
   * fields on which to join.
546
   *
547
   * @param $table
548
   *   The table against which to join.
549
   * @param $alias
550
   *   The alias for the table.  In most cases this should be the first
letter
551
   *   of the table, or the first letter of each "word" in the table.  If
omitted,
552
   *   one will be dynamically generated.
553
   * @param $condition
554
   *   The condition on which to join this table.  If the join requires
values,
555
   *   this clause should use a named placeholder and the value or values
to
556
   *   insert should be passed in the 4th parameter.  For the first table
joined
557
   *   on a query, this value is ignored as the first table is taken as the
base
558
   *   table.
559
   * @param $argments
560
   *   An array of arguments to replace into the $condition of this join.
561
   * @return
562
   *   The unique alias that was assigned for this table.
563
   */
564
  public function addJoin($type, $table, $alias = NULL, $condition = NULL,
$arguments = array()) {
565
5662005
    if (empty($alias)) {
5671788
      $alias = $table;
5681788
    }
569
5702005
    $alias_candidate = $alias;
5712005
    $count = 2;
5722005
    while (!empty($this->tables[$alias_candidate])) {
5730
      $alias_candidate = $alias . '_' . $count++;
5740
    }
5752005
    $alias = $alias_candidate;
576
5772005
    $this->tables[$alias] = array(
5782005
      'join type' => $type,
5792005
      'table' => $table,
5802005
      'alias' => $alias,
5812005
      'condition' => $condition,
5822005
      'arguments' => $arguments,
583
    );
584
5852005
    return $alias;
5860
  }
587
588
  /**
589
   * Orders the result set by a given field.
590
   *
591
   * If called multiple times, the query will order by each specified field
in the
592
   * order this method is called.
593
   *
594
   * @param $field
595
   *   The field on which to order.
596
   * @param $direction
597
   *   The direction to sort.  Legal values are "ASC" and "DESC".
598
   * @return
599
   *   The called object.
600
   */
601
  public function orderBy($field, $direction = 'ASC') {
602369
    $this->order[$field] = $direction;
603369
    return $this;
6040
  }
605
606
  /**
607
   * Restricts a query to a given range in the result set.
608
   *
609
   * If this method is called with no parameters, will remove any range
610
   * directives that have been set.
611
   *
612
   * @param $start
613
   *   The first record from the result set to return.  If NULL, removes
any
614
   *   range directives that are set.
615
   * @param $limit
616
   *   The number of records to return from the result set.
617
   * @return
618
   *   The called object.
619
   */
620
  public function range($start = NULL, $length = NULL) {
621155
    $this->range = func_num_args() ? array('start' => $start, 'length' =>
$length) : array();
622155
    return $this;
6230
  }
624
625
  /**
626
   * Groups the result set by the specified field.
627
   *
628
   * @param $field
629
   *   The field on which to group.  This should be the field as aliased.
630
   * @return
631
   *   The called object.
632
   */
633
  public function groupBy($field) {
6341
    $this->group[] = $field;
6351
  }
636
637
  /**
638
   * Get the equivalent COUNT query of this query as a new query object.
639
   *
640
   * @return
641
   *   A new SelectQuery object with no fields or expressions besides
COUNT(*).
642
   */
643
  public function countQuery() {
644
    // Shallow-clone this query.  We don't want to duplicate any of the
645
    // referenced objects, so a shallow query is all we need.
6461
    $count = clone($this);
647
648
    // Zero-out existing fields and expressions.
6491
    $fields =& $count->getFields();
6501
    $fields = array();
6511
    $expressions =& $count->getExpressions();
6521
    $expressions = array();
653
654
    // Ordering a count query is a waste of cycles, and breaks on some
655
    // databases anyway.
6561
    $orders = &$count->getOrderBy();
6571
    $orders = array();
658
659
    // COUNT() is an expression, so we add that back in.
6601
    $count->addExpression('COUNT(*)');
661
6621
    return $count;
6630
  }
664
665
  public function __toString() {
666
667
    // SELECT
6682005
    $query = 'SELECT ';
6692005
    if ($this->distinct) {
6701757
      $query .= 'DISTINCT ';
6711757
    }
672
673
    // FIELDS and EXPRESSIONS
6742005
    $fields = array();
6752005
    foreach ($this->fields as $alias => $field) {
676
      // Always use the AS keyword for field aliases, as some
677
      // databases require it (e.g., PostgreSQL).
6782005
      $fields[] = (isset($field['table']) ? $field['table'] . '.' : '') .
$field['field'] . ' AS ' . $field['alias'];
6792005
    }
6802005
    foreach ($this->expressions as $alias => $expression) {
6814
      $fields[] = $expression['expression'] . ' AS ' .
$expression['alias'];
6824
    }
6832005
    foreach ($this->tables as $alias => $table) {
6842005
      if (!empty($table['all_fields'])) {
6851
        $fields[] = $alias . '.*';
6861
      }
6872005
    }
6882005
    $query .= implode(', ', $fields);
689
690
691
    // FROM - We presume all queries have a FROM, as any query that doesn't
won't need the query builder anyway.
6922005
    $query .= "\nFROM ";
6932005
    foreach ($this->tables as $alias => $table) {
6942005
      $query .= "\n";
6952005
      if (isset($table['join type'])) {
6962
        $query .= $table['join type'] . ' JOIN ';
6972
      }
698
      // Don't use the AS keyword for table aliases, as some
699
      // databases don't support it (e.g., Oracle).
7002005
      $query .= '{' . $this->connection->escapeTable($table['table']) . '}
' . $table['alias'];
7012005
      if (!empty($table['condition'])) {
7022
        $query .= ' ON ' . $table['condition'];
7032
      }
7042005
    }
705
706
    // WHERE
7072005
    if (count($this->where)) {
7081979
      $this->where->compile($this->connection);
709
      // There is an implicit string cast on $this->condition.
7101979
      $query .= "\nWHERE " . $this->where;
7111979
    }
712
713
    // GROUP BY
7142005
    if ($this->group) {
7151
      $query .= "\nGROUP BY " . implode(', ', $this->group);
7161
    }
717
718
    // HAVING
7192005
    if (count($this->having)) {
7201
      $this->having->compile($this->connection);
721
      // There is an implicit string cast on $this->having.
7221
      $query .= "\nHAVING " . $this->having;
7231
    }
724
725
    // ORDER BY
7262005
    if ($this->order) {
727369
      $query .= "\nORDER BY ";
728369
      $fields = array();
729369
      foreach ($this->order as $field => $direction) {
730369
        $fields[] = $field . ' ' . $direction;
731369
      }
732369
      $query .= implode(', ', $fields);
733369
    }
734
735
    // RANGE is database specific, so we can't do it here.
736
7372005
    return $query;
7380
  }
739
740
  public function __clone() {
741
    // On cloning, also clone the conditional objects.  However, we do not
742
    // want to clone the database connection object as that would duplicate
the
743
    // connection itself.
744
7451
    $this->where = clone($this->where);
7461
    $this->having = clone($this->having);
7471
  }
748
}
749
750
/**
751
 * @} End of "ingroup database".
752
 */
7532013